Need help with MsgBox

[Loony22]

Automatic notification of expiry date

Hey guys, i really didn't know what to put in the topic but i need help .

Anyway, this is the full story :

In my DB there is Expiration date of a cretin product. Whenever the date has expired I would like that a MsgBox will appear:
"Product has expired. Would u like to update his Information? "
If the user clicked yes it will go to the form and allow the user to change the Product details. If the user pressed no, the MsgBox will be closed.

The very importent thing is that the MsgBox will appear WHENEVER the program is running. No matter where the user is, no matter in which form/Report is running.

I would be happy to get an answer as soon as possible.

 

[Fizzio]

You are a bit harsh on the product, calling it a 'cretin', after all, they are not highly intelligent mammals like some of the others in here. Anyway I digress.

The best way is to check the expiration date on opening your startup form. As you are only checking for a date, you will not need to use the timer event (Unless your Db is permanently open)
How you do the alerting depends on a couple of things. Is it possible for more than 1 product to expire on that date? The likely answer is yes so do you want the user to have the option to update many products?
A way of doing this is to open a recordset based on a query with the expiration date of the product <Date()
Then if records returned >0 put up a message box.
If yes, open the product update form (with the controlsource the same as the recordset)

eg.

Sub Startup Form_Open()
Dim MyDb as DAO.Database, MyRs as DAO.Recordset, lngRecCount as Long
set MyDb = CurrentDB
Set MyRs = MyDb.OpenRecordset("QueryExaminingExpiryDates")
MyRs.movelast
lngRecCount = MyRs.recordcount
MyRs.Close

If lngRecCount > 0 then 'If 1

If MsgBox (lngRecCount & " Products have expired. Do you wish to update them?", VbInformation & VbYesNo, "Products Expired") = VbYes then 'If 2
Forms!ProductUpdateForm.Controlsource = "QueryExaminingExpiryDates"
Docmd.openform "ProductUpdateForm"
Else Forms!ProductUpdateForm.Controlsource = "NormalControlSource"
End if 'If 2

Else Forms!ProductUpdateForm.Controlsource = "NormalControlSource"
End if ' If 1
End Sub

Should work

 

[Loony22]

Very very helpful. Thank u very much. Just one Question, what is Dao.Database ?

Because when i complie the Script there is an eror msg :
"User-defined type not defined."

Hmm?

 

[The_Doc_Man]

DAO.Database comes into play when you are using Data Access Objects. (Which is a good idea for VBA work.)

Open the module window so that VBA code is on your screen. In Access 97 or earlier, use Tools>>References from the main menu bar. In Access 2K or later, use Tools>>References from the code window's menu bar.

The references listed in the dialog box are statements of the external files Access will use. Libraries of useful routines and constants and data structrues. One of these SHOULD be the DAO library. If you look in the list, there ought to be a row for Data Access Objects. Check the box for that row.

Note: If any row in that list has the word MISSING (in all caps), you have a missing reference. I won't go into that now, but if you have it, continue this thread and one of us will advise you on how to fix that.

If you have AC97, you should have DAO35.TLB in your windows SYSTEM32 folder. If you have AC2K, it would be (I think) DAO36.TLB, but that's just a new version of DAO35...

 

[Loony22]

Thanks . However i am having troubles :-(

This is what i wrotre :

Dim MyDb As DAO.Database, MyRs As DAO.Recordset, lngRecCount As Long
Set MyDb = CurrentDb
Set MyRs = CurrentDb.OpenRecordset("ExpireDateQry")
MyRs.MoveLast
lngRecCount = MyRs.RecordCount
MyRs.Close

If lngRecCount > 0 Then 'If 1

If MsgBox(lngRecCount & " Products have expired. Do you wish to update them?", vbInformation & vbYesNo, "Products Expired") = vbYes Then 'If 2
Forms!EntitledToRentForm.ControlSource = "ExpireDateQry"
DoCmd.OpenForm "EntitledToRentForm"
Else: Forms!EntitledToRentForm.ControlSource = "NormalControlSource"
End If 'If 2

Else: Forms!EntitledToRentForm.ControlSource = "NormalControlSource"
End If ' If 1
End Sub

This is My Qry :

SELECT EntitledToRentalPaymentTbl.EntitledId, EntitledToRentalPaymentTbl.EntitledExpireDate
FROM EntitledToRentalPaymentTbl
WHERE (((EntitledToRentalPaymentTbl.EntitledExpireDate)<Date()));

If someone will help me find the answer to my problems i'll be greatfull.

 

[bradcccs]

I may be amiss here but . . .

When Fizzio used "NormalControlSource", I think it referred to your "Normal Control Source" - Table Name, Query etc. Not "NormalControlSource" verbatim. (Unless it is an operand I haven't heard of??)

If you list the actual "trouble" you are having, the real experts (not pretend ones like me) might be able to help you out.

Cheers

Brad.

 

[Loony22]

First i understood the problem with the ControlSource and i fixed it. Second, Few thing accure while i am running the moudle:

a) there are no yes or no Buttons in the msgbox.There is only Ok button.

b) the Program says it can't find the form (even tough the form exist in the DB) . Err number 2450 .

Sorry for the ignorance but i keep having problems
.

 

[bradcccs]

no yes or no buttons

Try removing the "vbinformation" setting from your message box line.

To read:

If MsgBox(lngRecCount & " Products have expired. Do you wish to update them?", vbYesNo, "Products Expired") = vbYes Then 'If 2

At which point does the "Can't find form" error occur. On the control source setting, or on the form opening.

You could try using break points (or comment out individual lines) to determine the error source.

Persistence is the key

Brad.

 

[Loony22]

The eror occur On the control source setting Not on the form open command.
If i'll remove the control source setting and Press Yes in the msg box, the form will open (of course not with the control source i want) .

 

[Fizzio]

Try changing the syntax to read

Forms!EntitledToRentForm.Form.ControlSource = "ExpireDateQry"
and likewise for the other statements
I'm not sure if you need the .Form in but I suspect you do.

 

[Loony22]

Still doesn't work, same eror number 2450 . I tried a new thing :

When the answer is yes :
Forms!EntitledToRentForm!EntitledId.ControlSource = "ExpireDateQry!EntitledId"

When the answer is no or there are no Products Expired :
Forms!EntitledToRentForm!EntitledId.ControlSource = "EntitledToRentalPaymentTbl!EntitledId"

But it doesn't work.


("EntitledId" is the Id of the table "EntitledToRentalPaymentTbl" which the form "EntitledToRentForm" is based on ).

 

[CJBIRKIN]

Hello

Just wondering what the difference between controlsource and recordsource??

Chris

 

[Loony22]

YES!!!! Finally, everything is allright i fixed the problem .
Thank you very much evreyone iam really greatfull.

About the question asked :

RecordSource = The whole record (qry or tbl).
ControlSource = Field within the tbl, qry or form .

 

[CJBIRKIN]

Doh,

It's weird how you can know stuff like setting the controlsource for a textbox in a form etc but when you see it in code you (I) have a total blank. Anyway that actually really helps me with a problem i've been having.


Cheers

Chris