I’ve built an Access 97 data entry form and want the user to manually save the entries by clicking a “Save Form” button. Before the record is saved I need to test to make sure all of the fields have entries, and to see if the absolute value of the change amount field is < .005.
If any of the fields are blank, I don’t want it to save the record.
If the absolute value of the change amount is < .005 I want the user to either verify that it’s correct (which will save the record) or not (which will not save the record). That part is working.
Otherwise (when all fields have been entered & the change amount is > .005) it should save the record. That’s where the problem is. It’s not saving the record in this case.
My code:
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
If IsNull(Me![txtUserID]) Then
MsgBox “Please enter your User ID.”, vbOKOnly
Else
’ seven more of the same type of If IsNull statements removed to save space in this post
If Abs([txtChangeAmount]) < 0.005 Then
Dim LResponse As Integer
LResponse = MsgBox(“The Change Amount value is less than .005. Is this the correct change amount?”, vbYesNo, “Change Amount”)
If LResponse = vbNo Then
Exit Sub
Else
Me.EntryDate = Date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Hmmmm… I’m not sure if this will exactly solve your problem but you might try forgoing the coding and use field properties instead.
In the table that your form is based on, you can set the Required property to Yes for all the fields you don’t want blank. Then you’ll get an error message if you try to save a record with that field blank. That’ll at least replace all your IsNull stuff.
For the <.005 I’m not sure, but I’ll look into it.
AllShookDown, you maybe making this more difficult than you need. Access begins “saving” the record the moment you begin entering data on the form. A note of caution, Save Form and Save Record are two different things. As Myron pointed out, you could use the Field properties to do your data validation. For instance, when a user tries to exit a field without entering data in it, Access will display an error message and not let them exit that field. In this way, your forcing data validation on exit of each field, vice forcing the user to go back and correct one or more fields after the fact. As for your .005 code, that seems like it will work ok.
Allow me to recommend this site. The message boards there are very active and the people are very helpful. I had an Excel question that didn’t even get a sniff here and within 1 hour on ozgrid, somebody had written me a small bit of VB code to help solve my problem.
Is it possible that it’s never getting to the Else statement below Exit Sub? You might try rewriting that part of it something like this:
If Abs([txtChangeAmount]) < 0.005 Then
Dim LResponse As Integer
LResponse = MsgBox("The Change Amount value is less than .005. " & _
"Is this the correct change amount?", vbYesNo, "Change Amount")
If LResponse = vbNo Then
Exit Sub
End If
End If
Me.EntryDate = Date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
(Note that the last two lines don’t have to be within either If statement, because they’ll anyway never execute if the user answers No in the dialog box; the function will have already exited.)
Even better might be to replace the Exit Sub with a call to the Exit_cmdSaveRecord_Click: label, because that way you have only one exit point for the function.
If you are using Access97, it looks like you may need to use the RunCommand method rather than DoMenuItem.
From the help file for Access2000:
DoMenuItem Method
Note In Microsoft Access 97, the DoMenuItem method was replaced by the RunCommand method. The DoMenuItem method is included in this version of Microsoft Access only for compatibility with previous versions. When you run existing Visual Basic code containing a DoMenuItem method, Microsoft Access will display the appropriate menu or toolbar command for Microsoft Access 2000. However, unlike the DoMenuItem action in a macro, a DoMenuItem method in Visual Basic code isn’t converted to a RunCommand method when you convert a database created in a previous version of Microsoft Access.
Myron Van Horowitzski and BF - Thanks for the advice, but I have to set this form up for the lowest common denominator and I want the message boxes to specify what needs to be entered. And I specifically don’t want the record saved until the user says to save it.
adam yax - I usually go to MrExcel.com, which has an Access forum too (which isn’t nearly as helpful as their Excel forum), but I’ll give OzGrid a try next time. I didn’t realize OzGrid had forums. Thanks.
RedNaxela - Ding! Ding! Ding! We have a winner. That took care of the whole problem. Thank you so much.
pipper - I checked into that but something in the help file must be wrong. I created my “Save Record” button with the wizard and that’s the VBA code it put in there. I just pasted in the rest of that code. I tried finding examples of Run Command in the help files but wasn’t able to find any without spending a ton of time on it so, as long as it works, I’m not going to dig any further on that one. I’m getting down to the wire on my deadline for this.