Run Macro if =2

[Groundrush]

I have a Subform called "SubformCount" which keeps a count on certain Jobs

What I need is for a macro to activate when the count reaches 2 and notify the user that a letter needs to be sent.

I have been trying to use a message box action in a macro with the condition set to "[SubformCount]=2"

but I can't get it to work.

I also need one to activate when the count reaches 4 and more.



thanks in advance.

 

[jtvcs]

Have you tied the macro to the after update event of the specific field on your sibform ? Also I would use a conditional macro which would check all variations you need. = 2 then Msgbox 'about two', >3 then Msgbox '4 or more'. HTH JT

 

[Groundrush]

Yes I have tried that but no joy.

Is it possible that the problem is because the subform is based on a qry, or is it because of an error in the condition that I am using?.

the condition is:
[Forms]![frmAlarms]![SubfrmCount]![CountOfFalseAlarm]=2

FrmAlarms is the main form
SubfromCount is the subform in question
and the countOfFalseAlarm] is the field name in the subform


thanks

 

[IMO]

Try this in the OnCurrent Event of the form...

Private Sub Form_Current()

If Me.[SubformCount]![countOfFalseAlarm] = "2" Then
    DoCmd.RunMacro "YourMacro", , ""
End If

If Me.[SubformCount]![countOfFalseAlarm] > "3"  Then
    DoCmd.RunMacro "YourOtherMacro", , ""
End If

End Sub

IMO

 

[IMO]

Sorry, missed out your message box bit!!

Private Sub Form_Current()

If Me.[SubformCount]![countOfFalseAlarm] = "2" Then
    DoCmd.RunMacro "YourMacro", , ""
    MsgBox "YourMessageHere"
End If

If Me.[SubformCount]![countOfFalseAlarm] > "3"  Then
    DoCmd.RunMacro "YourOtherMacro", , ""
    MsgBox "YourMessageHere"
End If

End Sub

IMO

 

[Groundrush]

Thanks for your time IMO

I have tried your code and I am getting error "2465"
cant find the field "I"
see attached

Maybe I have the incorrect form/field names

should it be:
Private Sub Form_Current()
If Me.[name of subform]![name of field in subform] = "2" Then
DoCmd.RunMacro "YourMacro", , ""
MsgBox "YourMessageHere"
End If
End Sub

ty

 

[IMO]

Error "2465" means Access can't find a field name. What is the Name of your Subform and the Name of the field with the value in it?

IMO

 

[Groundrush]

Subform Name is "SubfrmCount"

Field Name is "CountOfFalseAlarm"

I have just checked my spelling and made a change.

The message I now get is "You entered an expression that has no value"






the main form that all this takes place in is called "frmAlarms"

 

[IMO]

Can you post a stripped down version of your DB and I'll take a look? It sounds like there is no value in the field.

IMO

 

[IMO]

Or you could try adding this to the begining of the code...

Private Sub Form_Current()

If Me.[SubfrmCount]![countOfFalseAlarm] = "" Then
End If

IMO

 

[Groundrush]

Access 2000

 

[IMO]

I can't seem to work out what you exactly want your DB to do (Then again it is Friday and all I can think of at the moment is the pub!), add a record every time a ComboBox is selected? I've posted back a version using 'DLookUp' rather than a Subform which seems to work if a record reaches 2 or is >3. Hope it's helped a little.

IMO

 

[Groundrush]

Thanks IMO


I happen to have another thread that has started to pick up on the same thing and I have been getting some help from Robert Dunstan.

see http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=51906

He has modified your code and put it in the Got Focus of the Operative No

at the moment it almost works but I am getting a runtime message 2427 when ever I try and enter a new record that has a new property.

see my latest version

Thanks

 

[IMO]

Rather than use a Query, why not use DCount to count the number of Alarms for each Job Number. Create a text box on the form and enter...

=DCount("[FalseAlarm]","Alarms","[JobNo] = '" & [Forms]![frmAlarms]![JobNo] & "'AND [FalseAlarm] = 'YES'")

as the Control Source. You can then have the OnCurrent event of the form check the TextBox for the value.

Private Sub Form_Current()

If Me.YourTextBox = "2" Then
    MsgBox "YourMessageHere"
End If

If Me.YourTextBox > "3"  Then
    MsgBox "YourMessageHere"
End If

End Sub

IMO

 

[IMO]

Oops! Forgot the attachment!

IMO

 

[Groundrush]

Wow that's what I need

Although I cant have it on the Job No, it needs to be on the property as we may get 3 alarm calls at the same property with 3 different Job No's

Is there a way to make it work by using the property in the qry that is linked to the job No instead?

I have just tried to include the property in the table but I can't get the combo box to save the details of the Job No and Property at the same time.

Also What would you suggest doing to cancell the Code, say when activating a command button?.

i.e when a property first reaches 2 false alarms the first code will activate instructing the user that a letter needs to be sent.
When the user clicks on send first action letter, it will de-activate the code until it reaches 4 or more causing the second part of the code to activate and so on..

maybe thats another ball game on another thread..

I am very grateful for your help, thanks

 

[IMO]

Here's a quick way to disable the code using toggle buttons, I'll have to look at the other problems tomorrow

IMO

 

[IMO]

Change the DCount to...

=DCount("[FalseAlarm]","qryAlarm","[BG_SITE] = '" & [Forms]![frmAlarms]![JobNo].[Column](1) & "'AND [FalseAlarm] = 'YES'")

Hope this is what you needed

IMO

 

[Groundrush]

IMO

Thank you very much, you have helped me a great deal.

I tried changing the code last night at home to pick up the Property in the qry, but I did not change [JobNo] to [JobNo].Column](1) as you have this morning

It works Perfectly now.


I have also looked at your quick way to disable the code using toggle buttons and will work on that now

Thanks again

 

[IMO]

Glad it works, we got there in the end!

IMO