Before Record Change Event?

[aliendan]

Is there an event that is equivalent to a 'before record change' event?

I need to run check on a field to see if it's null or 0. If it is then a message box will pop up and tell them that data needs to be in this field before they can change to a different record and if this is true, I need to stop the record change. I need this to happen BEFORE the record change whether they use the navigation buttons or the keyboard to move through the records.

I hope this makes sense to anyone and all help on this is most welcome.

Thank you.


Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[Remou]

Forms have a Before Update event. Is this what you mean?

 

[aliendan]

I tried that event. I placed my procedure in that event and it did not work. It was as if no procedure existed.


Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[jkl0]

You can go to the table in Design View and make it a required field.

 

[ItIsHardToProgram]

private sub form_berfoupdtate(cancel as integer)
dim bla as integer 'or long depending on the possible values
'bla could be a string
bla = nz(ur txtbox)
'0 if numeric, "" if string
if bla = 0 then
'requirement not met, cancel.
      Msgbox "you need to fill the txtbox"
      Cancel = true
End if.

Try this.

 

[aliendan]

Yes, I can do that. That is probably what I will have to do. In doing that, would you know how I could use my own error message instead of the generic one created? A normal user wouldn't be able to make sense of what it is saying.


Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[ItIsHardToProgram]

Aliendan, try my method.... you can add anything in that code....

 

[aliendan]

I understand what you are saying. This is the code that I'm using to test it. I should see the msgbox, but I don't when using it in the Before Update Event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim txtRndmID As TextBox

Set txtRndmID = Forms("frmCstmrs")("CstmrRndmID")

If IsNull(txtRndmID) Then
MsgBox "STOP", vbCritical
End If

End Sub



Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[ItIsHardToProgram]

Why do you set txtrndmID in ur code... just set it in your form......

Also, Try it with "" instead of isnull since its a string and if it still dont work then Try it with any value, do a if "10" and place 10 in ur string txt box see if the event is triggered... if so the problem u have is with isnull(txtrndmID) it is probably never null or something


I can't really help you more with that, it should get triggered as you want it

 

[aliendan]

Ok, now really feel stupid. When I typed the code for you people for some reason I had test box on my mind. It is a numerical field. I should have copy/pasted. I just changed it to this code.

If IsNull(Me!CstmrRndmID) Then
MsgBox "STOP", vbCritical
End If

The procedure works in other events, for instance the On Click event. But, if I have it in the Before Update event it does nothing when I change records using the navigation buttons or by using the page up and page down buttons...or any other means.

I guess I will just have to make the field a required field in the table and deal with it. I think Billy G. and the folks missed and opportunity to place a cool event in the program...a "Before Record Change" event.

Thanks for all of your time,
Dan


Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[Remou]

Custom message:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Keywords: Custom error input mask violation
'goes in a form module
   ' Const INPUTMASK_VIOLATION = 2279
    Const REQUIREDFIELD_VIOLATION = 3314

    If DataErr = REQUIREDFIELD_VIOLATION Then
       MsgBox "Tut, tut"
       Screen.ActiveControl.Undo
       Response = acDataErrContinue
    End If
    Debug.Print DataErr
 End Sub

Which do you prefer Before Update or required field?

 

[PHV]

The BeforeUpdate form's event is meaningfull for BOUND forms only.

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

 

[ItIsHardToProgram]

PHV

Thank you

 

[JoeAtWork]

The BeforeUpdate event really is the "Before Record Change" event you're looking for. Perhaps your assumption that the textbox is Null is wrong - maybe it's an empty string?

If IsNull(Me!CstmrRndmID) Then
    MsgBox "STOP", vbCritical
Else
    Msgbox "Textbox wasn't Null"
End If

Or perhaps your record isn't actually dirty, so BeforeUpdate wouldn't fire. Try changing one of your other fields to make sure the record is dirty.

As PHV mentioned, the BeforeUpdate event only works for bound fields.

Also, Access has this little bug (maybe solved in 2003) where sometimes a control's event code doesn't get "wired" to the actual event. This is usually solved if you enter the VBA code through the properties page - don't ask me why.

 

[Remou]

You will find this:
[tt]If Trim(Me.[NameOfControl] & "") = "" Then[/tt]

Recommended by-too-many-people-to-credit-anyone as a test for Null, because it will work for null, for zero length strings and for space filled controls.

 

[TheAceMan1]

How are ya aliendan . . .

Perhaps your having an operational problem . . .

[blue]The forms BeforeUpdate event doesn't trigger unless [purple]you've edited a record and either change record or close the form[/purple].[/blue]

[blue]   If Nz(Me!CstmrRndmID) = 0 Then
      MsgBox "STOP", vbCritical
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

 

[aliendan]

This is rather embarrasing. Friday was one of those days I couldn't get out of a brain lock. It wasn't until the end of the day when I went outside to have cigarette (it's amazing how many problems I've solved over a cigarette) that I realized how this really should work.

I needed a field to be populated with a random 9 digit number that just HAD to be there no matter what. All I had to do was make it happen during the 'before update' event. SHEESH!! I really needn't worry about historical records not having it because this program will not have historical data in it. HEhehahahaha!

Sorry for waisting ya'll's time. But it was fun anyway and indirectly you all helped me wake up.

So, thank you to all of you.

Dan


Dan Rogotzke
Dan_Rogotzke@oxy.com