Msg Box Help

[robmict]

Hello,
I am both an amature to Access and Forums so please don't get too agnry with me if this has already been asked.
I have been building and Access database in Access 2007 to help manage a small EMS service. I would like to be able to have a Message Box pop up when my ambulances are getting close to a service mileage. I have a table for Ambulance check sheets where current milegae is logged once a week, and I have a table for my service log which contains a next service mileage. I have ran a query and placed and IIF statement that will tell me yes or no that the ambulances need to be serviced. This is how my IIF statement looks; Service_Due: IIf([Service_Log]![Next_Schedule_Maintenence]-[Ambulance_Checksheet]![Mileage]<=300,"Yes","No") I want the Message box to come up when I enter the mileage on my Ambulance_Checksheet_frm. Any help would be greatly appreciated. I have tried making a macro for this but it either always says service due or nothing at all. I am going to stop here because I am sure there will be specific questions I have to answer to get help.

 

[Steve R.]

I want the Message box to come up when I enter the mileage on my Ambulance_Checksheet_frm.

Use the after update event to trigger your mileage computation for the text box used to enter the mileage. You may want to consider using a pop-up form rather than a message box.

 

[robmict]

I will do that and let you know how it works. Thank you

 

[robmict]

Uggg, this is going to be the death of me. I simply want it to, on a form, if the mileage is within 300 miles to do a pop up or message box and say "Service Due" I have a query that does the calculation and it will tell me yes or no that service is due, I have tried making a macro and attaching it to the mileage box in my "ambulance check sheet" form to on up date run the macro. It runs but it will always say service due, the other way I tried it never says service due. My downfall I think is in how my macro is, I can't get an IIF statement to work in the condition area for the message box. Or the macro to run past the Stop Macro part if the No condition is met. Please help!!!!

 

[PNGBill]

You have a query that will tell you service due or service not due and it works.

Why not have the query in the after update event of the form. Why have a macro for this?

Try it manually first if you are not sure. Use toolbox and create a command button to open the query and then click this button when you have updated the form with the mileage.

If it works , then get it to work in one of the forms event properties eg after update and then cancel the command button.

 

[Steve R.]

I have a query that does the calculation and it will tell me yes or no that service is due, ...

You may be attaching your macro in the "wrong" place. How is your query triggered? If your query successfully returns a "YES" or "NO" then you should be able to incorporate something like this into the action that triggers your computation. (ServiceDue would be a Boolean variable)

if ServiceDue then
          MSGBOX "Service Due"
     ELSE
          Rem nothing
     End If

Cross posted with PNGBill.

 

[Mage-7]

In Condition field you can try to enter your true statement without IFF.
It's not neccesarry.
I did like this in condition fields and it worked:

IsNull([Forms]![Įvedimas asmens]![darb_id])=True And [Forms]![Įvedimas asmens]![darb_id].[Visible]=True And IsNull([Forms]![Įvedimas asmens]![pvm_ar_asm])=True

By the way... I think ([Forms]![Įvedimas asmens]! is not neccesarry if u checking something in same form but I'm not sure. I'm very new to this myself.

 

[acarter1]

You could use a loop on the opening of the form, which checks through the logged mileages and checks them against a statement of =<300 something like:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("YourTableName")

rs1.MoveFirst
Do Until rs1.EOF
If rs1![FieldName] = <Your Criteria> Then
MsgBox "Ambulance Mileage Within Criteria"
End If
Loop
rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

Not too hot on loops so get this checked haha!