To verify data while entering into the DB from Forms 2

[rushitshah]

Hi Ppl,
I am very new to Access 2003. I am building up an application that requires data entry into the tables from forms.

I have couple of doubts,

1. After closing the form when I open it again, it shows the data of the table, I do not want that. I want that when opened the form it should be blank, not showing anything from the tables.

2. I want to verify the data before it is entered in to tables. As in some fields are mandatory and required to be in some specific format, to check that. If they are not proper then generate some warning to the person.

3. Is it possible to put a button or something on the form that when that is clicked only then the data is transferred to the table. Usually when I enter the data in the form at that time only it transfers to the table. But I do not want that.

If someone can reply to these questions that will be a great help. Thanks..

 

[rushitshah]

thanks trev..

i tried this. but actually it does not show the name of the textfield, txtNewInfo, but it was showing Text3, that access puts by itself when you import text box from the tools menu.

So i tried with that and seems working fine now.

So here what happens is when data is added in table it shows message that "done adding data". that should happen because we are asking it to do so. The text remains in the textbox of the form so if I hit the button again it goes in to the table again.

Now here what is the command if I want to make the text disappear from the textbox, once it is added in the table, so if the person hits the button again by mistake, the record will not be duplicated.

I will try the same thing on my original 3 related table DB and let you know how it goes.

Thanks..
Rush..

 

[Trevil]

In my opinion, it is a good practice to use meaningful names. It can save a ton of time later when you're trying to figure out exactly what may be in "Text3". I usually rename to "txt...." (i.e. txtCustomer, txtCost, etc.)

As for how to clear the data from your text boxes, after the line of code:
set rs=nothing

add code like:
Me.Text3 = ""
Me.Text4 = ""
etc.

"Hmmm, it worked when I tested it....

 

[rushitshah]

Hey Trev...Thanks a lot man...It worked when I tested it!!!

Actually I am also giving meaningful names to the objects on the form, but still in the code window it shows only the name of the objects what access gives by default.

Like when I add textbox from the tools menu, access gives it a name like Text3 for example. Then I change that name to be something meaningful. But still in the codes window after Me. popup window it appears only Text3, not the name that I have given to the text box.

The other doubt is for mandatory fields, Ginger gave me this code,

Private Sub Text5_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Text5) Then
MsgBox "Please enter a Date!", vbOKOnly, "Missing Data"
Me.Text5.SetFocus
Cancel = True
Exit Sub
End If
End Sub

I am adding that in individual textbox's before update event. Where Text5 is my mandatory field's textbox name, that again I have not given, but seems that access does not take my given names!!! But it does not seem to work. Can u suggest something regarding this code?

Rush..

 

[Trevil]

I'm *concerned* that you can't see the name changes for your text boxes.

1. Close Access
2. Open Access and open the database you are using.
3. Open the form in design view.
4. Click on the control and see if the 'correct name' appears in the properties window.
5. Click on menu 'Tools | View Code'
6. Click the dropdown list for 'object' (it's the left dropdown just below the toolbars).
7. Do you see your 'correct name'? If not $%#@!



"Hmmm, it worked when I tested it....

 

[GingerR]

Sounds like you didn't do exactly what I suggested, which was to build an unbound form to try this out on.

You should first go through your entire form and re-name every text box to something other than what Access names them. Is your form still bound to a table?

As for your problem where the user hits the button again and the data goes in again: do you have any Primary Keys set on your tables? For example, if someone adds a record for Product ID 15, do you have the ProductID field set as a primary key so that no other information can be added for it?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Thanks Ginger and Trev,

Trev.. I tried as per your suggestions..but still the same probs..will repeat those again and see what happens..

Ginger..I am using unbound form, that is not bounded with any table.. I have primary keys in my tables..As I have 3 tables. Table1, Table2 and Table3. Table1 has a primary key that is autonumber that relates with Table2. Table2 has a primary key that is also an auto number that relates it with Table3. But actually as Trev suggested I added lines like Me.Text5 = "" in the code and it is working fine.

Rush..

 

[GingerR]

oh i missed that. something easier might be to just make your last line of code be to open the form again, in ADD mode. then each time you add another control (text box, etc) you don't have to remember to clear it out as well.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Thanks Ginger..
But still facing problems with mandatory field...

I am checking if the field is empty so adding the following code in the beforeupdate event of textbox Text0.

Private Sub Text0_BeforeUpdate(Cancel As Integer)
if isnull(me.txtDate) = True then
msgbox "Please enter a Date!",vbokonly,"Missing Data"
me.txtDate.setfocus
cancel = TRUE
exit sub
end if
End Sub

But it does not generate any message box and adds up a blank line in the table.

My form is unbounded. Please help again.
Rush..

 

[PHV]

As your forms are unbound you should have some code doing the insertion in the tables.
Enforce ALL your validation rules just before this code executing it only when all is OK.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GingerR]

If your text box is named "text0" then you must replace every place it says "txtDate" with "text0". I named my text box "txtDate" as an example, but you have to change that bit if your text box has a diff name.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Hey Ginger...
I tried it with Text0 only...
But it is not working...

This is just when asking question I pasted it from the top of this page...so sorry for confusion...

Rush..

 

[GingerR]

Can't tell from your last post if you are having trouble or not. If you are still having trouble, please post your code and the names of your form(s) and control(s).

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Hi Ginger..Still having problem.

I have two tables.
Table1: Name Test -- Two fields, TestData(Text)
Number(AutoNum,Primarykey)
Table2: Name Test1 -- Two fields, Number(Number, for relation) and Text (Text)

and one button command4.

When the button is hit, data goes in to the table. If I want to make TestData mendatory, I am using the following code. Please go through it.


CODE____________________________________________________
Private Sub Text0_BeforeUpdate(Cancel As Integer)

if isnull(me.Text0)= True then
msgbox "Please enter a Date!",vbokonly,"Missing Data"
me.Text0.setfocus
cancel = TRUE
exit sub
end if
end sub

Private Sub Command4_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from Test")
rs.AddNew
'Put data from form control Text0 into field TestData
rs!TestData = Me.Text0
rs.Update
Set rs = Nothing
Me.Text0 = ""

Set rs = CurrentDb.OpenRecordset("Select * from Test1")
rs.AddNew
'Put data from form control Text2 into field TestData
rs!Text = Me.Text2
rs.Update
Set rs = Nothing
Me.Text2 = ""

End Sub
__________________________________________________
Thanks,
Rush..

 

[GingerR]

what's the problem?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

The problem is even if the Text0 textbox is null, it does not generate the error message box, and it enters the blank record in the test table.

So it is not checking for the mendatory field condition.

Rushit..

 

[GingerR]

try

if isnull(me.Text0) or me.Text0 = "" then

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

tried with it..
But not working..still the message box is not popping up.

I am leaving text0 field empty and in text2 textbox keeping some data and then hitting the button.

But it adds blank record in test table and adds the text from text2 box to test1 table.

Rushit..

 

[GingerR]

oh..duh...should have seen this before:

your validation code is in the BEFORE UPDATE event of the control. So, unless you type something into the control and move to another control, the BEFORE UPDATE event won't fire. So this doesn't make sense. Well, it sort of does, but only in combination with some other things. I'd leave it there, but that's for the case that a user deletes whatever is in the text box, which is fine.

But copy the same code to the button OnClick event for it to check before saving the data.

It all depends on how you want stuff to operate: Typically you'd put all the validation code into the Button's OnClick event, so it gets checked all at once. If that's what you want to do, I'd just remove it from the BeforeUpdate event.

Private Sub Command4_Click()
'Validate data
if isnull(me.Text0) then
     msgbox "Please enter a Date!",vbokonly,"Missing Data"
     me.Text0.setfocus
     exit sub
end if

'All is well; proceed to add data to tables
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select * from Test")
  rs.AddNew
  
  'Put data from form control Text0 into field TestData
  rs!TestData = Me.Text0
  rs.Update
  Set rs = Nothing
  Me.Text0 = ""

  Set rs = CurrentDb.OpenRecordset("Select * from Test1")
  rs.AddNew
  'Put data from form control Text2 into field TestData
  rs!Text = Me.Text2
  rs.Update
  Set rs = Nothing
  Me.Text2 = ""

End Sub

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rushitshah]

Hi Ging..
Thanks a lot...it is working..
even I should have thought of that...!!!

But you are great...I think I will allow you to take rest for today...Thanks for your help.
Will post the thread if some help is needed again.

Rush..

 

[rushitshah]

Hi Ginger..
I think there is still some minor glitch.

For the first time it works ok. Initailly if I leave the textbox text0 empty, it generates the error box.

But if I add something from that textbox first into the table, and then without closing the form, if I leave it empty it does not generate the error message.

Rush..