• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

How to get a message box to Count records?

I am making a WMS label tracking application where a picker scans their id badge and then the labels they have to go pick.

I need a message box to appear off of a button that says they have scanned so many labels and do they have anymore labels to scan.  If yes then the close message box.  But when no is entered I need the code to run a macro

The count would be done on a field named PickerID
0
MattDylan
Asked:
MattDylan
  • 5
  • 5
1 Solution
 
mbizupCommented:
MattDylan,

You can use the DCount function in VBA behind the command button:

msgbox "Records scanned: " & Dcount( "*", "YourTableName", {optional criteria})

You'd have to modify the exact syntax to use your actual table name, and supply criteria if any.
0
 
MattDylanAuthor Commented:
I tried using this code but I keep getting a "Invalid Character" error everywhere.  I am not very good with visual basic but I have a lot of saved code from other porjects and I just threw yours in.
Private Sub Command6_Click()
Dim LResponse As Integer

LResponse = MsgBox"Records scanned: " & Dcount( "*", "tblEnterNewLabels", {optional criteria}("Is this how many labels you have?", vbYesNo, "Continue"))

If LResponse = vbYes Then
    {AccessApp.DoCmd.RunMacro mcrMoveNewData}
Else
    {Cancel}
End If


End Sub

Open in new window

0
 
msacc97Commented:
Hi MattDylan,

The snippet below should work

Uncomment (remove ' sign) to apply code you want to be executed in "Yes" and "No" cases.
On details about DCount method syntax, refer to MS Access help (can just click cursor on DCount statement in your code and press F1)

Hope this helps
Private Sub Command6_Click()

Select Case MsgBox("Records scanned: " & DCount("PickerID", "tblEnterNewLabels"), vbYesNo Or vbInformation Or vbDefaultButton1, Application.Name)

    Case vbYes
'    place here code for YES
    Case vbNo
'    place here code for NO
End Select

End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MattDylanAuthor Commented:
The message box now works but my macro will not run for some reason.  The message box says Records Scanned and shows the count but I need to insert a question like "Have you scanned all your labels?".
Private Sub Command6_Click()
 
Select Case MsgBox("Records scanned: " & DCount("PickerID", "tblEnterNewLabels"), vbYesNo Or vbInformation Or vbDefaultButton1, Application.Name)
 
    Case vbYes
'    DoCmd.RunMacro mcrMoveNewData
    Case vbNo
'    Cancel
End Select
 
End Sub

Open in new window

0
 
msacc97Commented:
' sign at the beginning of line means "comment", i.e. that this line will not be executed.
Remove ' to execute the code.
Private Sub Command6_Click()
 
Select Case MsgBox("Records scanned: " & DCount("PickerID", "tblEnterNewLabels") & vbCrLf & "Have you scanned all your labels?", vbYesNo Or vbInformation Or vbDefaultButton1, Application.Name)
 
    Case vbYes
    DoCmd.RunMacro mcrMoveNewData
    Case vbNo

End Select
 
End Sub

Open in new window

0
 
MattDylanAuthor Commented:
It is still giving me an error on line 6: DoCmd.RunMacro mcrMoveNewData.  And what about asking the question in the message box?  
0
 
msacc97Commented:
What error do you have for line 6?
If mcrMoveNewData is a macro name, not a variable, then you must use "

DoCmd.RunMacro "mcrMoveNewData"


Actually the question is added (see line 3). Or do you mean, you want to have some another message box with this question?
0
 
MattDylanAuthor Commented:
Thank you that was the problem now the macro is running.  
I would like the message box to say how many records have been scanned like it does now.  Then underneath that in the same message box I would like it to ask the question and that is what the yes and no buttons go with.

Example:                               20 labels scanned
                                      Is this how many labels you have?
                                              Yes            No
0
 
msacc97Commented:
Did you check line 3 of my last snippet?
Well, this is exactly what you are expected to have..
0
 
MattDylanAuthor Commented:
I don't know why but it didn't show up the last time but now it is working perfectly
Thanks a bunch
0
 
msacc97Commented:
You are welcome and thank you for the points! :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now