DCount with Msgbox

I am trying to have a form prompt the operator that they have completed the order.  I did search and I did find the code I sort of need, but I cannot alter it so that it works.

They currently scan the item numbers to an order with a barcode scanner.  Once the total # of items is scanned, I want a message box to pop up to alert them that they are done.

"load_complete" is a query that checks the total scanned items vs. the total qty ordered.  Once the scan_qty and order_qty are = to each other, that query gets populated.  My assumption was that as soon as that occurs, after the last item is scanned, the after update would see that and would pop up the message box.  Or is this something that should be done

Private Sub item_no_AfterUpdate()
If DCount("*", "load_complete") > 0 Then
    MsgBox "Order Complete"
Else
    Exit Sub
End If
End Sub
Russian_LmgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what control does the "total # of items is scanned" goes to?
0
Jeffrey CoachmanMIS LiasonCommented:
Your code only checks when the "Field" is updated, you need to check this after the "Record" is updated.

But your UI is unclear:
< Once the scan_qty and order_qty are = to each other, that query gets populated. >
How is this happening?


So try code like this first, and let us know exactly what does, or does not happen:
Private Sub item_no_AfterUpdate()
me.dirty=false
If DCount("*", "load_complete") > 0 Then
    MsgBox "Order Complete"
Else
    Exit Sub
End If
End Sub


JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russian_LmgAuthor Commented:
That code resulted in this error "Run-time error '2101' The setting you entered isn't valid for this property."

The debug highlighted the Me.Dirty=False

To answer this question:

"But your UI is unclear:
< Once the scan_qty and order_qty are = to each other, that query gets populated. >
How is this happening"

The query "load_complete" looks at the order being worked on.   It compares the total qty of items ordered [order_qty] and the total # of scanned items [scan_qty] for that same order.  It has two criteria

Criteria 1.  The query filters using [Forms]![scans]![cust_order] (that is the active form, so it only checks the form being worked on.
Criteria 2.  That [scan_qty] = [order_qty]

So the result is that the query only shows a record once the order is complete.  Which is why I figured using the DCount as a check would work, since it should count the 1 record once the order is done, any other time "load_complete" would show empty.

It has occurred to me that perhaps there is another place to put this check, which might be more logical, perhaps the form AFTER INSERT?

And now after some sleep and trying that (AFTER INSERT instead of AFTER UPDATE).  It works.  Thank you for your help.
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, that is what I mentioned first.

I just wanted you to try the code first, ...just to see if that would work...

Glad I could help.

;-)
JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.