Validation rule pop-up

[TomBP]

Hi,

I have a table with two fields. Both fields have a validation rule and text. As a result both fields need to be filled in the form before a next record can be added.

When using a macro based button to go to the next record a pop-up appears with the validation text if a certain field is not filled. This text does however not pop-up when clicking on the code based button.

What needs to be added to the code to get a similar result?

 

[CJ_London]

the validation rule is triggered when the field loses focus - so my guess is that you clicked on the score field before clicking on the macro button and didn't for the code button - or there is additional code in your macro that enforces it

If a score is required then you should modify the underlying table and set the required property to yes for this field - then you won't need the validation rule in this case

 

[TomBP]

Hi CJ,

If I have focus on the score field or not then the validation rule is triggered with the macro button. For the code button there is no difference. Neither way the validation pop-up appears.

I've tried changing the required property to yes in the table but that does not have the desired effect.

 

[CJ_London]

The reason the msgbox pops up on the macro button is because you have additional code in the macro checking for errors

 

[TomBP]

How can I translate the additional code in the macro to vba?

 

[CJ_London]

I don't use macros, so not sure what the exact translation would be, but if you convert/copy the macro to a 'general' macro i.e. not tied to a form or control you can then convert it to VBA

 

[TomBP]

Thanks CJ. I wasn't aware that you could convert macro to code with a simple push of a button.

Unfortunately the code does not react similar to the macro.

Private Sub NextRecordMacro_Click()
On Error GoTo NextRecordMacro_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNext
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


NextRecordMacro_Click_Exit:
    Exit Sub

NextRecordMacro_Click_Err:
    MsgBox Error$
    Resume NextRecordMacro_Click_Exit

End Sub

 

[CJ_London]

clearly the converter doesn't always get it right. Try changing this line

If (MacroError <> 0) Then


to

If (err.number<> 0) Then

 

[TomBP]

One step closer. When clicking the button a pop-up appears.

Unfortunately it's empty where I would expect it to be populated with the validation text from the field causing the error.

 

[CJ_London]

you've lost me - as previously advised for what you want, you need to set in the field definition in the table the required property to yes and remove your validation text.

then you don't need any code at all for managing the error, Access will do it for you - all you need is the docmd.gotorecord code