Debug--Query does not include the specified expression as part of aggregate function

[DanicaDear]

I've tried to put together some code for a form. This code actually compiles (and I've very proud of that!) but it gives me this error when running and points to line 41 in the code below.
"Run-time error 3122--You tried to execute a query that does not include the specified expression 'ORDER_NUM' as part of an aggregate function."

What I want to do is this:
1. Enter a box number being returned. This will be entered into a dummy field [txtScanCapture] by a scanner, and if it has 3-4 digits, it transfers into txtScan_Box_Num. (For those who have been helping me, I have decided *not* to enter the customer number on box return because it's pointless as far as I can tell.) This BOX_NUM was previously assigned to a [Cust_Num] and [Order_Num] in tblBOX when the box was shipped.
2. Automatically insert date() into [DATE_BOX_RETURN] on subfrmBOX_SHIPPING which is on frmBOX_SHIPPING.
3. Look in tblORDERS for the [Order_Num] and [Cust_Num] matching the [Box_Num] just scanned. If field [Date_Ret] is empty, insert date(). If it isn't empty, then that implies it was done on a previous box return.

I don't know how mangled my code is but I gave it my absolute best effort. Can you please look at my code and tell me what all might be wrong with it? I'm not sure if I have my If statments correctly nested or if my strsql is in the right place. Note I have two uses of strsql--is that ok? These are the things I'm just not sure on. Suggestions to eliminate my run-time error will also be greatly appreciated!

PS. For those who have helped on a similiar problem, I changed the names of two fields on the main form...since I won't be scanning the cust_num I changed the names of two fields, taking out the "Scan" reference.
Also, I've got a couple of open posts that I need to kind of wrap-up/close but I want to get all similar problems solved so I can put my final solutions in. Things might still change slightly. ;-)

Option Explicit
Option Compare Database
 
Public strLastScan As String
Public db As DAO.Database
 
Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
 
Private Sub txtScanCapture_AfterUpdate()
    Dim strSQL As String
 
    Select Case Len(Me.txtScanCapture)
    Case 3, 4
        'Box
        'Is box registered in database?
        If DCount("BOX_NUM", _
                      "tblBOX", _
                      "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
                'Box does not exist in DB
                MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
        Else
                Me.txtScan_Box_Num = Me.txtScanCapture
                'Box exists.
                'Set received date=now
                strSQL = "UPDATE tblBOX " & _
                         "SET    [DATE_BOX_SHIP]=Date()" & _
                         "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings (True)
                Me.subfrmBOX_RECEIVING.Requery
               'Use the box_num to obtain the cust_num & order_num from tblBOX
               'but I don't know how to store the values to use in the .FindFirst below
                strSQL = "SELECT   [CUST_NUM]" & _
                        ", [ORDER_NUM]" & _
                        "FROM     tblBOX  " & _
                        "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _
                        "GROUP BY [CUST_NUM]"
                With db.OpenRecordset(strSQL, dbOpenSnapshot)
                    If .RecordCount = 0 Then
                        MsgBox "There is no record of this box shipping"
                        'Do whatever you want to handle this case
                    Else
                        Me.txtScan_Box_Num = !BOX_NUM
                        Me.tb_Cust_Num = !CUST_NUM
                        Me.Max_ORDER_NUM = !ORDER_NUM
                    End If
                    Call .Close
                End With
        End If
 
        'Update the DATE_RET in tblOrders where necessary
        With db.OpenRecordset("tblORDERS", dbOpenDynaset)
            Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
            If Not .NoMatch Then
                If IsNull(![DATE_RET]) Then
                    Call .Edit
                    ![DATE_RET] = Date
                    Call .Update
                End If
            End If
            Call .Close
        End With
        strLastScan = "Box"
 
    Case Else
        'Some sort of error or user error
        MsgBox "Box Numbers can only be 3 or 4 digits."
    End Select
 
    Me.txtScanCapture = ""
End Sub

Thanks to those of you who continue to help my pitiful self. hahaha.

[MikeTheBike]

@DanicaDear
Hi

Withoutlooking at all the code, the specific error you have give should we fixed if this

strSQL = "SELECT   [CUST_NUM]" & _ 
                        ", [ORDER_NUM]" & _ 
                        "FROM     tblBOX  " & _ 
                        "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
                        "GROUP BY [CUST_NUM]" 

is changed to this

strSQL = "SELECT   [CUST_NUM]" & _ 
                        ", [ORDER_NUM]" & _ 
                        "FROM     tblBOX  " & _ 
                        "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
                        "GROUP BY [CUST_NUM], [ORDER_NUM]" 

ie. add the offending field to thr GROUP BY clause,

or pehaps this is what you intended (using an aggregate function)

strSQL = "SELECT   [CUST_NUM]" & _ 
                        ", Max([ORDER_NUM]) " & _ 
                        "FROM     tblBOX  " & _ 
                        "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
                        "GROUP BY [CUST_NUM]" 

??

MTB

[NeoPa]

If you leave Skype up I will try to catch you later tonight. It will probably be easier to explain in words with the database to look at what has changed and what should match what.

I'll explain what needs to be changed, and possibly even post in here to explain what is going on.

I know what it used to do, but I'm not really following the explanation of what's changed in the underlying logic (and this is critical as its not very typical logic for a form).

[NeoPa]

BTW. Mike's suggestion is all perfectly valid. It makes sense.

However, as he doesn't understand your logic either, it's simply a fix to the specific problem, rather than a viable way forward for your project.

You need to understand why what you have written is not right. let me see if I can illustrate quickly :
Table = [tblBox]

BOX_NUM  CUST_NUM  ORDER_NUM
  001      AAA       00001
  002      AAA       00002
  003      BBB       00003
  004      BBB       00003

Where [CUST_NUM]='BBB' the obvious value to return for [ORDER_NUM] would be '00003'. For 'AAA' however, if you are grouping by [CUST_NUM], what value would be returned for [ORDER_NUM]?

If you include [ORDER_NUM] in the GROUP BY clause (as per Mike's first solution) then box '001' and '002' are no longer in the same grouping. Problem avoided.

If you use Max() around [ORDER_NUM] (as per Mike's second) then it knows to return the larger value '00002'.

Does this clarify the issue, and why a better understanding is required before we can properly direct you?

[DanicaDear]

Yes, NeoPa, I see your point clearly. The Max() function, (Mike's second proposal) is probably what I need. I haven't tried Mike's code yet but hope to have it complete by evening.

As for what has changed and the underlying logic...it's similar to the Box_Shipping except I want to *not* scan the customer number with every box number scan. I still want it to apply date() to the tblBOX and tblORDERS. Any confusion beyond that is probably just my mangling of the code. I tried to take what you gave me for Box_Shipping and make it work for Box_Receiving. I'm off work today and baby is going to Grandma's. When I get my errands complete I'll sign on Skype and maybe I'll catch you on later.

[NeoPa]

I must admit that I missed that this was about Receiving rather than Shipping.

Although we discussed this in more detail last Friday on Skype, I think it may be helpful to post the code we eventually came up with. It may help to clarify for any later readers what it was all about.

[DanicaDear]

NeoPa helped me make a few changes to my code and here is the final, working piece.

Option Explicit
Option Compare Database
 
Public db As DAO.Database
 
Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
 
Private Sub txtScanCapture_AfterUpdate()
    Dim strSQL As String
 
    Select Case Len(Me.txtScanCapture)
    Case 3, 4
        'Box
        'Is box registered in database?
        If DCount("BOX_NUM", _
                  "tblBOX", _
                  "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
            'Box does not exist in DB
            MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
        Else
            Me.txtScan_Box_Num = Me.txtScanCapture
            'Box exists.
            'Set received date=now
            strSQL = "UPDATE tblBOX " & _
                     "SET    [DATE_BOX_RETURN]=Date() " & _
                     "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
            DoCmd.SetWarnings (False)
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings (True)
            Me.subfrmBOX_RECEIVING.Requery
            'Use the box_num to obtain the cust_num & order_num from tblBOX
            'but I don't know how to store the values to use in the .FindFirst below
            strSQL = "SELECT   * " & _
                     "FROM     [tblBOX] " & _
                     "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
            With db.OpenRecordset(strSQL, dbOpenSnapshot)
                If .RecordCount = 0 Then
                    MsgBox "There is no record of this box shipping"
                    'Do whatever you want to handle this case
                Else
                    Me.txtScan_Box_Num = !BOX_NUM
                    Me.tb_Cust_Num = !CUST_NUM
                    Me.Max_ORDER_NUM = !ORDER_NUM
                End If
                Call .Close
            End With
        End If
 
        'Update the DATE_RET in tblOrders where necessary
        With db.OpenRecordset("tblORDERS", dbOpenDynaset)
            Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
            If Not .NoMatch Then
                If IsNull(![DATE_RET]) Then
                    Call .Edit
                    ![DATE_RET] = Date
                    Call .Update
                End If
            End If
            Call .Close
        End With
 
    Case Else
        'Some sort of error or user error
        MsgBox "Box Numbers can only be 3 or 4 digits."
    End Select
 
    Me.txtScanCapture = ""
End Sub
 
 

I appreciate everyone's help on these scanning issues. I'm "over the hump" as they say and the rest of the DB should be a downhill slide. Thanks thanks thanks again.

[NeoPa]

I certainly enjoyed it Danica. Good luck with the rest of it :)