Dlookup Message Box

[Ann]

Hi, I'm trying to find out how to use Dlookup to show a message box. I have
a table called tblPackagingLowCountsNotification with multiple fields, two of
which I am trying to use for the message box. One is strMediaPackagingType
and the other is RemainingPackaging. If the strMediaPackagingType is Clam
Shells and RemainingPackaging is less than 500 I want a message box to appear
that says it's time to order more Clam Shells.

I've looked through all the dlookup information on the site and tried many
of them but can't get it to work. If there is a different way to do this
other then dlookup that's fine too. I'd really appreciate it if someone can
help me out. Thanks in advance.

 

[Clifford Bass]

Hi Ann,

I presume you deal with more than Clam Shells. What you might want to
do is set up a query that lists all of the items that need reordering. Then
set up a report that uses that query. In that report, use its No Data event
to cancel the opening of the report. Then whenever you want to do the
reminder you simply open the report. If there are any things that need
reordering, they will be listed. When there are not any, the report will not
show. If that will not do what you want, please post back with more detail
as to when/how this check is being performed. While the user is doing
something in a form? Under what circumstances? Other pertinent information?

Clifford Bass

 

[Ann]

Hi Clifford,

Thanks for helping me. I do already have a form that keeps track of the
current amount of media we have (CDs, DVDs, Clam Shells, etc.). When the
number used for every job that is processed is entered it subtracts from
these totals and any new items purchased get added to these numbers so at any
given time the user can open the form and see what is available.
Unfortunately, he doesn't want to do this all the time and wants to have a
message appear at the time he opens the database that will tell him which
ones are low and need to be purchased.

My plan was to run a make-table query with the type and amounts remaining
that will run when the Switchboard form loads then a macro that checks the
totals in this table to see what is below a certain amount. That's why I
thought I could use DLookup in the macro. I've used it in the past but was
only looking up one field. Now I need to lookup two fields and am having a
hard time with the syntax.

The table is tblPackagingLowCountsNotification, the fields are
strMediaPackagingTypes and RemainingPackaging which comes from the following
calculation in the query ([lngPackagingStartingTotals]-[SumOflngCount]), the
query is qryPackagingLowCountsNotification.

 

[Clifford Bass]

Hi Ann,

The DLookup will not function for this need. It will only return one
item, unless you want to run it multiple times for each and every item that
you sell.

Instead of a make-table query and DLookup, I would suggest that my
solution is exactly what will fit the user's desire. You will simply add the
code to open the report into the On Open event of the Switchboard. Also, no
need for the make-table part. Just use your
qryPackagingLowCountsNotification as the source of the report. You could add
the condition "([lngPackagingStartingTotals]-[SumOflngCount]) < 500" to the
query, if you do not already have that condition. Give it a try and see how
it works.

Good Luck,

Clifford Bass

 

[Ann]

Thank you Clifford. I did what you said and it works great. I let the user
know this would be better since he can print the report to keep as a reminder
to make sure he orders the supplies. Thanks so much for the help.

Hi Ann,

The DLookup will not function for this need. It will only return one
item, unless you want to run it multiple times for each and every item that
you sell.

Instead of a make-table query and DLookup, I would suggest that my
solution is exactly what will fit the user's desire. You will simply add the
code to open the report into the On Open event of the Switchboard. Also, no
need for the make-table part. Just use your
qryPackagingLowCountsNotification as the source of the report. You could add
the condition "([lngPackagingStartingTotals]-[SumOflngCount]) < 500" to the
query, if you do not already have that condition. Give it a try and see how
it works.

Good Luck,

Clifford Bass

Hi Clifford,

Thanks for helping me. I do already have a form that keeps track of the
current amount of media we have (CDs, DVDs, Clam Shells, etc.). When the
number used for every job that is processed is entered it subtracts from
these totals and any new items purchased get added to these numbers so at any
given time the user can open the form and see what is available.
Unfortunately, he doesn't want to do this all the time and wants to have a
message appear at the time he opens the database that will tell him which
ones are low and need to be purchased.

My plan was to run a make-table query with the type and amounts remaining
that will run when the Switchboard form loads then a macro that checks the
totals in this table to see what is below a certain amount. That's why I
thought I could use DLookup in the macro. I've used it in the past but was
only looking up one field. Now I need to lookup two fields and am having a
hard time with the syntax.

The table is tblPackagingLowCountsNotification, the fields are
strMediaPackagingTypes and RemainingPackaging which comes from the following
calculation in the query ([lngPackagingStartingTotals]-[SumOflngCount]), the
query is qryPackagingLowCountsNotification.

 

[Clifford Bass]

Hi Ann,

Great! I did not even think about the ability to print for use as a
further reminder. Excellent thought! You are quite welcome!

Clifford Bass