Creating a Msg Box that will pop up during certain criteria.

[Dmonkyking]

Hi, I'm trying to make a message box to appear when my my database is first open if a Table contains certain criteria. The way I've been doing it is by using Expression Builder to run a RunMacro command to open a message box macro when the condition was met. The way I was trying to code it was like this:

DCount("[Pmel ID]","Test","[In Pmel] = False") And
DCount("[Pmel ID]","Test","[Date Due Cal] < Date()-7")

Obviously I'm not doing this right and I'm quite new at setting this stuff up. By the code above, you can see that I want a message box to pop up when the "Over Due Cal" field is 7 days before todays date or earlier and when the "In Pmel" is also False. I'm trying to make the macro run when the two field's above criteria is met, but it's proving difficult. As I'm pretty new to bulding expressions and using VB I was hoping someone could help me out and explain it in idiot for me to understand Thanks alot.

 

[vbajock]

It's going to be hard to do with an expression. You need to create a module, and create a function:
Function CheckData()

If DCount("[Pmel ID]","Test","[In Pmel] = False") And
DCount("[Pmel ID]","Test","[Date Due Cal] < Date()-7") then

msgbox "Somemessage"

else
msgbox "someothermessage"
end if

end function

Then you would create an autoexec macro, select Runcode as the the macro instruction, and place the name of this function in the dialog box.

 

[Dmonkyking]

Thanks alot vbajock. I'm definently going to give that a shot. Much better then the jumbled crap I was trying to program.

 

[Dmonkyking]

The VB Code for making the message box worked perfectly so thanks vbajock. But the only problem I have now is that I have Null values in the [DATE DUE CAL] field. So when the code runs it sees the null values as below the date()-7 and will pop up that message box when I don't want it to. Anyone might know how to make the code perhaps skip the null values so it won't try to take them into account? Thanks alot.

 

[vbajock]

If your using the Dcount function, you can add a where clause
DCount("[Pmel ID]","Test","[In Pmel] = False","[somedatefield] >#1/1/1902#")

 

[Dmonkyking]

mmm...that code didn't apperently work. Doesn't seem to like the ,"[somedatefield] >#1/1/1902#")part. Says wrong number of arguments or invalid property assignment. I guess it doesn't like this extra parameter. Also, just to tell you, I did change the [somedatefield] parameter to the date field in my database just so you don't think I just left it like that Anymore help you might be able to give me? Maybe I should get a VB book or something so I'll actually know something about VB.

 

[vbajock]

Can you post the statement?

 

[RoyVidar]

Or:

[tt]DCount("[Pmel ID]","Test","[In Pmel] = False AND [somedatefield] >#1/1/1902#")[/tt]

Roy-Vidar

 

[Dmonkyking]

sure, so far this is the code I got so far:

If DCount("[PMEL ID]", "TEST", "[IN PMEL] = False", "[DATE DUE CAL] >#1/1/1902#")And
DCount("[PMEL ID]", "TEST", "[DATE DUE CAL] < Date()-7") Then

Beep
MsgBox "Warning, There is one or more Overdue Items, Please Review Them!!!", vbExclamation, "Overdue"

End If

End Function

The DCount statement is all on one line

 

[PHV]

Try this:
If DCount("[PMEL ID]", "TEST", "[IN PMEL] = False AND [DATE DUE CAL] BETWEEN #1/1/1902# AND #" & Format(Date()-7,"m/d/yyyy") & "#") Then


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

 

[Dmonkyking]

Sweet, thanks alot PHV. It works perfectly, finally. I appreciate everyones help in this thread