Run Macro on Dirty

[AccessHelp]

Hello all,

I have 3 combo boxes on a form. The users make changes to those combo boxes
sometimes.

What I like to do is I would like to write a code to run my macro on form
closes if the users make changes to any or all of those combo boxes. Below
is the code that I wrote and it's not working. Please help. Thanks.

Private Sub Form_Close()
On Error GoTo Err_Form_Close

Dim stDocName As String
If Me.A.ondirty _
or me.B.ondirty _
or me.C.ondirty then
stDocName = "mcrRun_Queries"
DoCmd.RunMacro stDocName
End If
Exit_Form_Close:
Exit Sub

Err_Form_Close:
MsgBox Err.Description
Resume Exit_Form_Close
End Sub

 

[Ofer Cohen]

You can try

Dim stDocName As String

If Me.A<> Me.A.OldValue or me.B <> Me.B.OldValue Me.C <> Me.C.OldValue
then
stDocName = "mcrRun_Queries"
DoCmd.RunMacro stDocName
End If

 

[AccessHelp]

Thanks Ofer. I tried it, and it didn't work.

In addition to OnDirty, I also tried OnChange. When I tried both of them, I
got an error message "Type Mismatch".

Thanks.

 

[Ofer Cohen]

Try it on the Before Upate event of the form.

 

[AccessHelp]

Thanks again for your help. I tried it, and it didn't work.

 

[Ofer Cohen]

It seem that the combo's are unbound, and this is why the code doesn't work.
If that the case then most chances that the combo's are empty when the form
is loaded, so you can check if one of the combo's is not empty and then fire
the macro.

On the close event

If Len(Me.[A] & "") >0 Or Len(Me. & "") >0 Or Len(Me.[C] & "") >0Then
code here
End If
==================
If the combo's are not empty, then you can save there values in variants
that you declare in the form decleration

Dim FieldA
Dim FieldB
Dim FieldC

On the Load event of the form you can write

FieldA = Me.[A]
FieldB = Me.
FieldC = Me.[C]

On the Close event compare the two values
If FieldA <> Me.[A] Or FieldB <> Me. Or FieldC <> Me.[C] Then
Run code
End if

 

[AccessHelp]

Hi Ofer Cohen,

I want to thank you very much for your helps and your patience. Yes, all
the fields are bound. Therefore, I used the one for bound, and it worked.

Again, thank you very much.

It seem that the combo's are unbound, and this is why the code doesn't work.
If that the case then most chances that the combo's are empty when the form
is loaded, so you can check if one of the combo's is not empty and then fire
the macro.

On the close event

If Len(Me.[A] & "") >0 Or Len(Me. & "") >0 Or Len(Me.[C] & "") >0Then
code here
End If
==================
If the combo's are not empty, then you can save there values in variants
that you declare in the form decleration

Dim FieldA
Dim FieldB
Dim FieldC

On the Load event of the form you can write

FieldA = Me.[A]
FieldB = Me.
FieldC = Me.[C]

On the Close event compare the two values
If FieldA <> Me.[A] Or FieldB <> Me. Or FieldC <> Me.[C] Then
Run code
End if


--
HTH, Good Luck
BS"D

Thanks again for your help. I tried it, and it didn't work.