By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

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

100+
P: 256
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:
Expand|Select|Wrap|Line Numbers
  1. Else
  2.                         Me.txtScan_Box_Num = Me.txtScanCapture
  3.                         'Box exists.
  4.                         'Assign box to current customer, and set box return date=now
  5.                         Dim strSQL As String
  6.                         strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
  7.                                 " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
  8.                         DoCmd.SetWarnings (False)
  9.                             DoCmd.RunSQL strSQL
  10.                         DoCmd.SetWarnings (True)
  11.                         Me.subfrmBOX_RECEIVING.Requery
  12.                         Me.tb_Scan_Cust_Num = ""
  13.                         Me.Max_ORDER_NUM = ""
  14.                     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.
Expand|Select|Wrap|Line Numbers
  1. Else
  2.                         Me.txtScan_Box_Num = Me.txtScanCapture
  3.                         'Box exists.
  4.                                        'If the CUST_NUM (and matching max ORDER_NUM which updates itself instantly)
  5.                                        'just scanned has a record matching the BOX_NUM just scanned
  6.                                        If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' & tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then
  7.                         'Set box return date=now
  8.                         Dim strSQL As String
  9.                         strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
  10.                                 " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
  11.                         DoCmd.SetWarnings (False)
  12.                         DoCmd.RunSQL strSQL
  13.                         DoCmd.SetWarnings (True)
  14.                         Me.subfrmBOX_RECEIVING.Requery
  15.                         Me.tb_Scan_Cust_Num = ""
  16.                         Me.Max_ORDER_NUM = ""
  17.                                       'If the CUST_NUM just scanned doesn't match a BOX_NUM 
  18.                                       'in an existing record, then give warning
  19.                                       Else
  20.                                       MsgBox "That box number is not assigned to that customer."    
  21.                                       End If
  22.                 End If


Can someone please tell me what I've done wrong? Hint: Debugger takes me to Line 6.
May 2 '10 #1
Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi Danica

I have not read your entire code, just focused on line 6:
Expand|Select|Wrap|Line Numbers
  1. If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' & tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then 
Replace with:
Expand|Select|Wrap|Line Numbers
  1. If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND  tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then
I replaced a "&" with an "AND"
May 3 '10 #2

100+
P: 256
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)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5. Private Sub txtScanCapture_AfterUpdate()
  6. Dim rs As DAO.Recordset
  7.  
  8.     If Len(Me.txtScanCapture) < 3 Or Len(Me.txtScanCapture) > 5 Then
  9.         'Some sort of error or user error
  10.         MsgBox "Input error, resetting"
  11.     Else
  12.  
  13.         Select Case Len(Me.txtScanCapture)
  14.             Case 3, 4
  15.                 'Box
  16.                 If Nz(Me.tb_Scan_Cust_Num) = "" Then
  17.                     MsgBox "A customer ID must be scanned first before scanning boxes."
  18.                 Else
  19.                     'Is box registered in database?
  20.                     If DCount("BOX_NUM", "tblBOX", "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  21.                         'Box does not exist in DB
  22.                         MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
  23.                     Else
  24.                         Me.txtScan_Box_Num = Me.txtScanCapture
  25.                         'Box exists.
  26.                                        'If the CUST_NUM (and matching max ORDER_NUM which updates itself instantly)
  27.                                        'just scanned has a record matching the BOX_NUM just scanned
  28.                                        If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then
  29.                         'Set box return date=now
  30.                         Dim strSQL As String
  31.                         strSQL = "UPDATE tblBOX SET tblBOX.DATE_BOX_RETURN = Date()" & _
  32.                                 " WHERE (((tblBOX.BOX_NUM)='" & Me.txtScanCapture & "'));"
  33.                         DoCmd.SetWarnings (False)
  34.                         DoCmd.RunSQL strSQL
  35.                         DoCmd.SetWarnings (True)
  36.                         Me.subfrmBOX_RECEIVING.Requery
  37.                         Me.tb_Scan_Cust_Num = ""
  38.                         Me.Max_ORDER_NUM = ""
  39.                                       'If the CUST_NUM just scanned doesn't match a BOX_NUM
  40.                                       'in an existing record, then give warning
  41.                                       Else
  42.                                       MsgBox "That box number is not assigned to that customer."
  43.                                       End If
  44.                      End If
  45.                 End If
  46.  
  47.             Case 5
  48.                 'Customer
  49.                 'Lets find customer entered
  50.                 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)
  51.                 If rs.RecordCount = 0 Then
  52.                     MsgBox "Customer number not recognized"
  53.                     'Do whatever you want to handle this case
  54.                 Else
  55.                     Me.tb_Scan_Cust_Num = rs!CUST_NUM
  56.                     Me.Max_ORDER_NUM = rs!MaxOfORDER_NUM
  57.                 End If
  58.         End Select
  59.  
  60.     End If
  61.  
  62.     Me.txtScanCapture = ""
  63.  
  64. Exit Sub
  65.  
  66. End Sub
May 3 '10 #3

topher23
Expert 100+
P: 234
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
Expand|Select|Wrap|Line Numbers
  1. If tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "' Then 
  2.  
with

Expand|Select|Wrap|Line Numbers
  1. Set rs = CurrentDb.OpenRecordset("SELECT tblBOX.CUST_NUM FROM tblBOX " & _
  2. "WHERE tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "';", dbOpenSnapshot)
  3. If rs.RecordCount > 0 Then
  4.  
You could probably also do it with a DLookup, something like
Expand|Select|Wrap|Line Numbers
  1. If Nz(DLookup("tblBOX.CUST_NUM","tblBOX","tblBOX.CUST_NUM = '" & Me.tb_Scan_Cust_Num & "' AND tblBOX.BOX_NUM = '" & Me.txtScanCapture & "'"))<>"" Then
  2.  
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!"
May 4 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 5 '10 #5

100+
P: 256
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.
Jun 1 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
Thanks for the update Danica.

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

Post your reply

Sign in to post your reply or Sign up for a free account.