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..

 

[Trevil]

In your code you could add 'LostFocus' events for each control and then do whatever validation you need.

To avoid seeing the records when you open the form, you either need to set the form's 'Date Entry' property to 'Yes', or remove the Record Source, or place some silly WHERE clause in your query (i.e. WHERE UserName = 'WhenMonkeeysFlyOutta')

If you remove the recordsource, then you would need the button to save the record. This could be where you perform all edits.

i.e.

Sub cmdSaveRecord_Click()
....perform all edits
Open recordset for your table
rs.addnew
rs!Cose = me.txtCost
etc.
rs.Update
rs.close
set rs = nothing
End Sub



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

 

[rushitshah]

Hi Trevil,
Thanks for this wonderful tip.
But actually I do not know VBA coding, so is there any other way out? With the help of wizards only?

Thanks,
Rushit..

 

[Trevil]

On your form, look at the properties for your control (i.e. a text box), and click on 'Validation Rule'. Then click the 'build button (...)' on the right. You can enter things like '>5 and < 10'. Then add whatever text you want displayed in the 'Validation Text' field

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

 

[GingerR]

The best would be Trevil's suggestion of using an unbound form. It seems daunting, but once you have it set up it's pretty easy to understand. Short of that, a good hack-job would be to have an identical "Temporary" table which is the recordsource for the form. The users type into it, you validate the data, and if it's "good", write that record to the "real" table and delete it from the "Temp" table. that's much more work though than just using an unbound form.

1) set the DATA ENTRY property to YES/TRUE. read in Help about what that means.

2) probably will be a mix and match of devices.
a) one is that you can use the VALIDATION property of the control. In Design view, view the VALIDATION RULE property of one of your text boxes. You can set it to something like ">0". Then in the VALIDATION TEXT you'd put the message you want to users. b) use the BEFORE UPDATE property of the control to check the data and send the user a message and set CANCEL = True to cancel the update event if it's not what you want. c) wait until the user hits the SUBMIT button to check all entries.

3) use an unbound form.



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

 

[rushitshah]

Thanks Trevil and Ginger,
Your help is really very important.

But still this does not take care of mandatory fields...like some fields I want to make sure that user has entered before the data goes into the table...

second there is not other way apart from coding for making a button on the form, when clicked only the data will go in to the tables...??

Because I am an electrical engg.. and I have not even touched VBA coding...

Thanks again...

 

[Trevil]

Mandatory may be validated by Validation Rule:
Is Not Null And <>""

Or by changing the table definition for the field to 'Required'


You could add one buttons using the Wizard that would Save
your record, and another that would 'Undo' changes

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

 

[GingerR]

For what you want to do, you will have to do a little work. There's no way around that. We can help you through it. Usually you can cut/paste then tweak what someone else has done to get what you need. you should for sure use an unbound form. Basically it will work just how you think it should:

1. Form has no recordsource (called "Unbound form")
2. user enters in info
3. user clicks a button
4. the button's OnClick code goes through each form control and validates the data. If there's a problem, the user gets some kind of pretty pop-up message like "Please enter a Part Number!" and the cursor goes into the Part Number box. When everything is ok, the same code behind the button writes the data to the real table. easy squeezy.

I never even touched VBA coding either. I avoided this for years. Wish I hadn't.

Do this to try it out.
1. Make a new table. Name it "TEST". Make it have one field (type=text), called TestData. Close the table.
2. Make a new form. Put a text box on it. Name the text box txtNewInfo. name the form Main.
3. Put a button on the form. when the Wizard starts, just cancel it.
4. In the OnCLick event of the button, put this in between the SUB and END SUB lines:

dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Test")
rs.addnew
'Put data from form control txtNewInfo into field TestData
rs!TestData = me.txtNewInfo
rs.update
set rs=nothing
msgbox "Done adding data!"

5. Make sure in the VBA Code screen, pick the menu item REFERENCES and make sure there is one for Microsoft DAO checked. Pick the highest version, probably 3.6.

6. view the form in Form View. Type something into the text box and hit the button. Does it work?

This is the basic code you need. Play around with it and let us know how it goes.


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

 

[rushitshah]

Hi Again...
I have added button on the form with the help of form wizard. When I added button, I selected record operations and in that action to add record. I did not use any VBA coding.

It seems working fine, but the problem is....

I have 3 related tables in my database. I am doing entry in those 3 tables with only one form and that has this button. The action of the button is valid for only one table, in rest of the two tables data enters directly without waiting for the action on the button, as soon as I put the data in the textbox of the form, that is surprising... And the other weird thing is that when I close the form without hitting the button, the data on the form also goes to the table. I want to stop that.

I am sure that you will help me out this time too...

Thanks in advance.

Rushit..

 

[GingerR]

Hi. That is how Access works. It's frustrating giving you a solution which you refuse to try.

From MS Access HELP File:

Microsoft Access automatically saves the record you are adding or editing as soon as you move the insertion point to a different record, or close the form or datasheet (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.) you are working on.

The data would have been saved as soon as you left the current record, even it you didn't hit the button. All the button that you made does, is SAVE explicitly.

To explicitly save the data in a record while you are editing it, click Save Record on the Records menu.

If you insist on using a bound form, try using the form's BEFORE UPDATE event or individual controls' BEFORE UPDATE events to do your data validation. Search MS Access HELP or this forum for "validate data" and "BEFORE UPDATE". You'd put in something like this for all of your controls:

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

Good luck.

g

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

 

[rushitshah]

Hi Ginger, Thanks a lot for your help. I will try that out tomorrow when get back to work.

But the thing is that the button operation works perfectly for one table, but rest of the two tables get data without the button hitting.

So it is like one table gets the data when I hit the button, but the other two gets the data as soon as the data is entered in the textbox? Am I doing some kind of mistake over here?

I can understand when the form is closed it saves data automatically.

Thanks,
Rushit..

 

[GingerR]

Do you have subforms?

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

 

[rushitshah]

yes...I have 3 related tables.
So when I used the form wizard it created subforms too.

 

[Trevil]

I agree with GingerR and think you should do the following:
1. Look at the GingerR post from 22 Jul 05 17:59.
2. Create that form but use one of your tables that would be in your subform. TEST IT!
3. Repeat the process, use the table from the OTHER subform. TEST IT!
4. Repeat the process for your MAIN table.

By now you have three forms -- each with a button to save changes.

5. Modify your MAIN form to include the two new forms from steps 2 and 3.

Since you earlier said you did not want the form to show any data when you open it, are these new forms strictly for DATA ENTRY? or will you be doing UPDATES? Depending on your answer, and if your tables have some defined relationship, you may need a way to select the record you want to update, or to link the subform to the main form.


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

 

[GingerR]

Rush--The reason that your subform data tables update as you type in to them is because that's how Access works: once you leave the record (of the subform) the data goes into the table.

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

 

[rushitshah]

Thanks both of you guys...That is a great help for a novice like me.

I tried what ginger said in 22 July, 17:59 reply.

dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Test")
rs.addnew
'Put data from form control txtNewInfo into field TestData
rs!TestData = me.txtNewInfo
rs.update
set rs=nothing
msgbox "Done adding data!"

When I added this code in the Onclick event of the button in between its sub and endsub, it gave me a compile error at me.txtNewInfo, It said that method or member not found. I am sure this will be very easy for you to decode. Let me know.

I have checked the references option and checked the microsoft access DAO 3.6. I checked for spelling mistakes too and seems everything ok...so help me out again.

Thanks,
Rushit..

 

[Trevil]

What name did you give the 'textbox' control on your form? Replace the code with whatever name you used.

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

 

[rushitshah]

Thanks trevil..
But the textbox name is the same txtNewInfo...

Rush..

 

[Trevil]

1. open the form in design view
2. go to the CODE window
3. highlight the "me.txtNewInfo" and press the delete key
4. start typing "Me." Once you press the period, you should see a drop-down of all objects / properties on your form.
5. Press the letters "txt" (should now be Me.txt)
6. Do you see "txtNewInfo"? If so, double click that name.
7. Click "Debug | Compile ..."

What happens?

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

 

[PHV]

For the Me. stuff the button must be in the same form as the textbox.

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