Need help with strSQL coding w/ UPDATE and Requery. Add If/Else statement?

[DanicaDear]

I have this update strSQL that is working great, but I'd like to make it smarter in hopes of preventing input errors.
Here is my working code:

Else
                        Me.txtScan_Box_Num = Me.txtScanCapture
                        'Box exists.
                        'Assign box to current customer, and set box return date=now
                        Dim strSQL As String
                        strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
                                " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
                        DoCmd.SetWarnings (False)
                            DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings (True)
                        Me.subfrmBOX_RECEIVING.Requery
                        Me.tb_Scan_Cust_Num = ""
                        Me.Max_ORDER_NUM = ""
                    End If

(Note: These items will be scanned in, or numbers typed with an "Enter" after each number, and that's the purpose of the fields with the "Scan" references.)

What I'm trying to do below is to do a check that the CUST_NUM matches the BOX_NUM (both of which will be simultaneously scanned by a barcode) before accepting the DATE_BOX_RETURN . Here is my try. This is my first attempt to write an If/Else statement so please watch me closely. :-) What I have added is indented farthest right.

Else
                        Me.txtScan_Box_Num = Me.txtScanCapture
                        'Box exists.
                                       'If the CUST_NUM (and matching max ORDER_NUM which updates itself instantly)
                                       'just scanned has a record matching the BOX_NUM just scanned
                                       If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' & tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then
                        'Set box return date=now
                        Dim strSQL As String
                        strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
                                " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
                        DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings (True)
                        Me.subfrmBOX_RECEIVING.Requery
                        Me.tb_Scan_Cust_Num = ""
                        Me.Max_ORDER_NUM = ""
                                      'If the CUST_NUM just scanned doesn't match a BOX_NUM 
                                      'in an existing record, then give warning
                                      Else
                                      MsgBox "That box number is not assigned to that customer."    
                                      End If
                End If

Can someone please tell me what I've done wrong? Hint: Debugger takes me to Line 6.

[TheSmileyCoder]

Hi Danica

I have not read your entire code, just focused on line 6:

If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' & tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then 

Replace with:

If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND  tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then

I replaced a "&" with an "AND"

[DanicaDear]

Thanks Smiley.
I tried your suggestion and it still won't work. Debugger still takes me to line 6. When I remove lines 6, 19, 20, and 21, the code runs as expected.

Since this post, I've noticed something of interest that I don't understand that might help in the solution.
I have
Select Case Len()
Case 3,4
Code pasted above, which puts a box number into a textbox then puts a date in a subform.
Case 5
Code stores my scanned customer number into a textbox. This will always happen before Case 3 or Case 4.

When I open my form, I first scan a 5 digit number, and then I get the error/debugger. The debugger suggests code from Case 3,4 is my problem. But if I was in Case 5, what's it doing looking in the code of Case 3,4 anyway?

I'm posting all the code. Don't feel obligated to go through it, but if you need it I want the info to be here. (lines 28, 41, 42, 43 here)

Option Compare Database
Option Explicit
 
 
Private Sub txtScanCapture_AfterUpdate()
Dim rs As DAO.Recordset
 
    If Len(Me.txtScanCapture) < 3 Or Len(Me.txtScanCapture) > 5 Then
        'Some sort of error or user error
        MsgBox "Input error, resetting"
    Else
 
        Select Case Len(Me.txtScanCapture)
            Case 3, 4
                'Box
                If Nz(Me.tb_Scan_Cust_Num) = "" Then
                    MsgBox "A customer ID must be scanned first before scanning boxes."
                Else
                    '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 & " not recognized in tool"
                    Else
                        Me.txtScan_Box_Num = Me.txtScanCapture
                        'Box exists.
                                       'If the CUST_NUM (and matching max ORDER_NUM which updates itself instantly)
                                       'just scanned has a record matching the BOX_NUM just scanned
                                       If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then
                        'Set box return date=now
                        Dim strSQL As String
                        strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
                                " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
                        DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings (True)
                        Me.subfrmBOX_RECEIVING.Requery
                        Me.tb_Scan_Cust_Num = ""
                        Me.Max_ORDER_NUM = ""
                                      'If the CUST_NUM just scanned doesn't match a BOX_NUM
                                      'in an existing record, then give warning
                                      Else
                                      MsgBox "That box number is not assigned to that customer."
                                      End If
                     End If
                End If
 
            Case 5
                'Customer
                'Lets find customer entered
                Set rs = CurrentDb.OpenRecordset("SELECT tblCUSTOMERS.CUST_NUM, Max(tblORDERS.ORDER_NUM) AS MaxOfORDER_NUM FROM tblCUSTOMERS INNER JOIN tblORDERS ON tblCUSTOMERS.CUST_NUM = tblORDERS.CUST_NUM WHERE tblCUSTOMERS.CUST_NUM Like '" & Me.txtScanCapture & "*' GROUP BY tblCUSTOMERS.CUST_NUM", dbOpenSnapshot)
                If rs.RecordCount = 0 Then
                    MsgBox "Customer number not recognized"
                    'Do whatever you want to handle this case
                Else
                    Me.tb_Scan_Cust_Num = rs!CUST_NUM
                    Me.Max_ORDER_NUM = rs!MaxOfORDER_NUM
                End If
        End Select
 
    End If
 
    Me.txtScanCapture = ""
 
Exit Sub
 
End Sub

[topher23]

Gee, this code looks really familiar. ;)

Here's what I'm seeing: When you are looking to see if the box is assigned to the customer, you are referencing table fields, but you don't have a table to reference, so you're getting an error. What you need to do is the same routine as in Case 5, where a recordset is initiated that references the table, then you check to see if there is anything in that recordset.

So, replace

If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then 
 

with

Set rs = CurrentDb.OpenRecordset("SELECT tblBOX.CUST_NUM FROM tblBOX " & _
"WHERE tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "';", dbOpenSnapshot)
If rs.RecordCount > 0 Then
 

You could probably also do it with a DLookup, something like

If Nz(DLookup("tblBOX.CUST_NUM","tblBOX","tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "'"))<>"" Then
 

but I haven't tested that.

Oh, and the reason it's pulling the error in that code is because the VBA compiler has to compile the whole subroutine in order to run it before the processor can evaluate any of the conditions. When it does, it hits that error and can't compile the sub, so it's actually kicking out with the error before it evaluates anything at all.

It's sort of like... If a book is written in Russian, you can't read it until it's translated into English. What you type in as code is actually Russian to the computer's processor, so the VBA compiler translates it into computer English. The translator is hitting your code and saying "Wait a minute, that's not Russian! You gotta fix that so I can translate it for the computer!"

[NeoPa]

topher23: It's sort of like... If a book is written in Russian, you can't read it until it's translated into English. What you type in as code is actually Russian to the computer's processor, so the VBA compiler translates it into computer English. The translator is hitting your code and saying "Wait a minute, that's not Russian! You gotta fix that so I can translate it for the computer!"

This is very much along the lines we were just discussing Danica. That text I sent you (repeated below) describes an approach that should help.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[DanicaDear]

I elected to do my box returns a different way, so I never completed the VBA to make this work as I initially intended. Just FYI for later readers.

[NeoPa]

Thanks for the update Danica.

I think there's plenty in here to help any other readers though, so all good :)