Confirm value before entering record?

[AlanCurtis]

The term "TechnicalUser" can be misleading but, since "Dummy" isn't one of the choices on the menu...

I'm a newbie.

I have a data entry form that has a command button which adds the record to the underlying table. Normally, one of the fields in the record should have a value greater than zero, but not always.

When the command button is clicked in those situations where the field's value IS zero, I would like a message box to appear, asking if zero is okay. Can this be done, and if so, how?

Thanks in advance.

Alan

 

[jebry]

Hi Alan!

Try this:

If txtBox.Value = 0 Then
If MsgBox("Are you sure you want to enter a zero for the value of yourvariable?", vbYesNo) = vbYes Then
Do you record save code here
End If
End If

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SBendBuckeye]

Alan,

We were all newbies at one point, so never be afraid to ask a question. You want to put your validation code in the Before Update event for the control in question. The reason you should use Before Update is because it passes a Cancel parameter which if set to True automatically returns focus to that control, regardless of what the user did.

Assume you have a control named txtTestIt on your form. Here is how you would do what you want to do:

Private Sub txtTestIt(Cancel As Integer)

If txtTestIt = 0 Then
If vbCancel = MsgBox("Please verify Zero entry", _
vbOKCancel + vbQuestion, "Verify Entry" Then
Cancel = True
txtTestIt.Undo
Exit Sub
End If
End If

maybe some other code

End Sub

What this code will do is pop a message box with a title of Verify Entry asking the user to choose OK or Cancel. If they select Cancel then control is returned to txtTestIt, the current value is undone and you exit the sub.

Be aware that you cannot change anything in the Before Update code, all you can do is validate it and cancel the update if validation fails.

So for instance, if you allowed lower case entry for a name and then Proper Cased it in the code, you would need Before Update code to do any validation that might result in the entry being rejected. Then you would do the Proper Case part in the After Update code so the user would My Name in the control after exiting the field having entered my name.

Good Luck!

 

[AlanCurtis]

It appears we have the old Purdue/Notre Dame rivalry going on here! Thanks to both of you Hoosiers (or one Hoosier and one transplanted Buckeye) for your help. This Buckeye appreciates it.

While I'm not very experienced at reading code, it appears that both of you came up with pretty much the same solution to my dilemma. I'm just not sure just how to put either one into play.

Jeff, I can easily adapt the code you wrote, but I don't know where to insert it.

Here is the code for the command button:

Private Sub cmdEnterRecord_Click()
On Error GoTo Err_cmdEnterRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdEnterRecord_Click:
Exit Sub

Err_cmdEnterRecord_Click:
MsgBox Err.Description
Resume Exit_cmdEnterRecord_Click

End Sub

Does your code get inserted right after the On Error line? I've tried it there, but the thing doesn't want to continue beyond giving me the message box. It won't enter the record in the table.

SBendBuckeye, I'm using Access 2000. I seem to recall that one of the property choices in Access 97 was "Before Update", which would make a whole lot of sense to me, but I don't find that choice in Access2K. Am I looking in the wrong place?

Here's another, related question: There will be a number of other similar checks I will need to associate with this same control. I presume I can just add the others as additional "If"s. Is that correct?

Thanks again to both of you for your help, and thanks for making me feel okay about asking these simple questions.

Alan

 

[SBendBuckeye]

Hey Alan,

I am indeed a transplanted Buckeye. When you are setting up your form controls, click on the properties button. One of the tabs is called Events. If you click on that you will get a list of various On This properties. If you click to the right of one of them it will prompt you to decide whether to use the Expression Builder (results in a function call), a Macro Builder or a Code Builder. If you select Code Builder it will automatically create a Procedure Header for you and transfer you to the code which is always attached to the form.

The event options will vary depending on the control you are working with. As an example, if you were working with a text box named txtCustomerID and you clicked on the After Update event and selected Code Builder it would automatically create the following:

Private Sub txtCustomerID_AferUpdate()

End Sub

Then you put whatever validation code you need in that sub.
You might want to play around a little bit and see how they work.

As an aside, don't forget your local public library as a resource. When I first started, I checked out several Access books from the library and they were a tremendous help. Try to avoid anything much before Access97 because it has changed a lot, but for general concepts its helpful.

Good Luck!

 

[AlanCurtis]

Greetings once more SBendBuckeye,

Thanks again for all of your help.

Just in case I didn't explain my original question clearly, here's what I was expecting. I had in mind a form with many fields for entry, upon completion of which the user would click an "OK" button. That button would cause the data on the form to be entered as a record on the underlying table. It was on THAT button that I expected the code to reside which, before adding the record, would first go back to the various fields and perform the tests I had in mind (I only mentioned originally that I wanted to test one field - there are several).

Now, if I understand correctly, what you are telling me is that updating in all of the fields happens simultaneously WHEN THE RECORD IS ADDED to the table. Therefore, by attaching the testing code to each of the other controls in its BeforeUpdate event, when the user clicks on the "OK" button each of those tests, in sequence, gets performed and must be resolved. Am I right so far?

Assuming that to be the case, I tried cutting and pasting the code you provided (modified with the correct control name) into the BeforeUpdate event on one of the controls on my form. Something is wrong, because the line beginning "If vbCancel = MsgBox" and the indented line following it, beginning with "vbOKCancel + vbQuestion" appear in red. I don't get an error message telling me what is wrong with the syntax, so I'm not sure where the problem is. Just looking at it, a couple of possibilities occur to me. First, should there be a closed parenthesis somewhere in those lines? Second, is the underscore correct, or did you intend it to indicate that the text on the second line should be a continuation of the first line?

I do believe we're going to get where we intend (he says as he clicks the heels of his ruby slippers together and crosses his fingers).

Again, thanks.

Alan

 

[SBendBuckeye]

It turned red because the underscore _ is a continuation character in VBA. Also, if you have multiple validations to do, you might want to consider the form's Before Update event which would allow you to put them all in once place. It is pretty much a preference issue. The one benefit to putting them at the control level is that you can use Cancel = True to force the user back to the error control.

Good Luck!