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

How to search for field, and update corresponding field via SQL

100+
P: 256
A field on frmBOX_SHIPPING receives a 5 digit CUST_NUM (text field), pulls up that customer's most recent order (ORDER_NUM (text field), and then accepts box numbers (BOX_NUM, text field)assigned to this order. During this process a DATE_BOX_SHIP = date() is assigned to the box number.

tblORDERS contains the ORDER_NUM and has its own place for a date: DATE_SHIP. This is not redundant information because it's possible the boxes could get seperated and ship out on different dates. So here's what I want to do:

When frmBOX_SHIPPING receives the 5 digit CUST_NUM and pulls up the ORDER_NUM, a box number will be entered and a date will be assigned. I'd like an after_update event, attached to the tblBOX.BOX_NUM to look in tblORDERS for the matching ORDER_NUM, and if the corresponding tlbORDERS.DATE_SHIP is null, set it to Date(). If it is not null, do nothing (that would imply a previous run of the code set the date).

If I'm not providing enough information, please let me know.

Thanks in advance!
Danica
May 5 '10 #1

✓ answered by DanicaDear

Two questions on Bytes were answered with this same piece of code. Initially these were two separate pieces of code. But here it is nonetheless, in case someone can gain something from it in the future.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public strLastScan As String
  5. Public db As DAO.Database
  6.  
  7. Private Sub Form_Open(Cancel As Integer)
  8.     Set db = CurrentDb
  9. End Sub
  10.  
  11. Private Sub txtScanCapture_AfterUpdate()
  12.     Dim strSQL As String
  13.  
  14.     Select Case Len(Me.txtScanCapture)
  15.     Case 3, 4
  16.         'Box
  17.         If strLastScan <> "Customer" Then
  18.             MsgBox "A customer ID must be scanned first before scanning boxes."
  19.         Else
  20.             'Is box registered in database?
  21.             If DCount("BOX_NUM", _
  22.                       "tblBOX", _
  23.                       "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  24.                 'Box does not exist in DB
  25.                 MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
  26.             Else
  27.                 Me.txtScan_Box_Num = Me.txtScanCapture
  28.                 'Box exists.
  29.                 'Assign box to current customer, set shipping date=now, and received date to null
  30.                 strSQL = "UPDATE tblBOX " & _
  31.                          "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
  32.                               ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
  33.                               ", [DATE_BOX_SHIP]=Date()" & _
  34.                               ", [DATE_BOX_RETURN]=Null " & _
  35.                          "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  36.                 DoCmd.SetWarnings (False)
  37.                 DoCmd.RunSQL strSQL
  38.                 DoCmd.SetWarnings (True)
  39.                 Me.subfrmBOX_SHIPPING.Requery
  40.                 'Update the DATE_SHIP in tblOrders where necessary
  41.                 With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  42.                     Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  43.                     If Not .NoMatch Then
  44.                         If IsNull(![DATE_SHIP]) Then
  45.                             Call .Edit
  46.                             ![DATE_SHIP] = Date
  47.                             Call .Update
  48.                          End If
  49.                     End If
  50.                     Call .Close
  51.                 End With
  52.             End If
  53.             strLastScan = "Box"
  54.             Me.tb_Scan_Cust_Num.BackStyle = 1
  55.             Me.txtScan_Box_Num.BackStyle = 0
  56.         End If
  57.  
  58.     Case 5
  59.         'Customer
  60.         'Lets find customer entered
  61.         strSQL = "SELECT   [CUST_NUM]" & _
  62.                         ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
  63.                  "FROM     tblORDERS " & _
  64.                  "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
  65.                  "GROUP BY [CUST_NUM]"
  66.         With db.OpenRecordset(strSQL, dbOpenSnapshot)
  67.             If .RecordCount = 0 Then
  68.                 MsgBox "Customer number not recognized"
  69.                 'Do whatever you want to handle this case
  70.             Else
  71.                 strLastScan = "Customer"
  72.                 Me.tb_Scan_Cust_Num.BackStyle = 1
  73.                 Me.txtScan_Box_Num.BackStyle = 1
  74.                 Me.tb_Scan_Cust_Num = !CUST_NUM
  75.                 Me.Max_ORDER_NUM = !MaxOfORDER_NUM
  76.             End If
  77.             Call .Close
  78.         End With
  79.  
  80.     Case Else
  81.         'Some sort of error or user error
  82.         MsgBox "Input error, resetting"
  83.     End Select
  84.  
  85.     Me.txtScanCapture = ""
  86. End Sub
  87.  
Thanks to everyone who helped out!

Share this Question
Share on Google+
14 Replies


Megalog
Expert 100+
P: 378
Something like this should do the trick, may want to check the field/table names to be sure they are correct.

Expand|Select|Wrap|Line Numbers
  1. Dim rs as DAO.Recordset
  2. Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)
  3.  
  4. rs.Findfirst = "[Order_Num] = '" & me.order_num & "'"
  5.  
  6. If rs.nomatch = false then
  7.  
  8.      If IsNull(rs![Date_Ship]) Then
  9.          rs.edit
  10.          rs![Date_Ship] = Date()
  11.          rs.update
  12.      End If
  13.  
  14. End If
  15.  
  16. Set rs = Nothing
  17.  
May 5 '10 #2

100+
P: 256
Wow Megalog, that was fast. All I did was go to lunch and get a sandwich. And, boom, SQL. ;-)
I'll try this and let you know if it works.
Thanks,
Danica
May 5 '10 #3

100+
P: 256
Megalog,

There is a problem with line 2. I have tried
Expand|Select|Wrap|Line Numbers
  1. Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)
  2. Set rs = OpenRecordset("tblOrders", dbOpenDynaset)
The original you provided and both of these two allow the subform itself to update, but it's not changing the SHIP_DATE in the tblORDERS.
All of the field names are correct. Any other ideas?
Thanks. :-)
Danica
May 5 '10 #4

Megalog
Expert 100+
P: 378
Good catch, I totally screwed up that line. I updated my previous post with the correct recordset statement.

As far as the update not working, my guess is it's not finding a match, therefore skipping the whole update block. Stepping through lines 4 to 12 should give you a good clue.

This is set up to match text for text.. so be sure both fields are formatted as text and not numbers (you did specify before it was a text field), and that there is a value for me.order_num being returned in line 4.
May 5 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
Watching Man City v Spurs ATM, but I noticed some points missing in your code so I grabbed the following for you to check through.
When posting any code on here please :
  1. Ensure you have Option Explicit set (See Require Variable Declaration).
  2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
  4. Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
May 5 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
I meant to look at the code for this first, but I fell over the other one and did some work on that instead - and now it's quite late.

I'll try to look at it again tomorrow for you. Megalog's idea of using VBA instead of SQL is a good one. I would probably proceed along the same lines.
May 5 '10 #7

100+
P: 256
I was studying about the Option Explicit awhile ago before some stuff at work hit the fan and I had to abandon it. I set the "require variable declaration" tool but it didn't change my code. From what I understand so far, this just helps you type things correctly. But if I post code from Bytes that doesn't have it to start with, does that mean it's too late to use it? I tried to do new code with it in there, and even though I had it selected, it didn't put that at the top of my code. Most likely I just need to play with this some more. I'll work on that. Might be a few days....
May 5 '10 #8

100+
P: 256
I meant to look at the code for this first, but I fell over the other one and did some work on that instead - and now it's quite late.
Don't rush yourself, I'm going to be out of the office for a few days! :-)
May 5 '10 #9

NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
This can be added to any module at any time. Subsequent compilations will pick up any references which are not already Dimmed.

The Require Variable Declaration option just ensures that any modules created after that point get that created with that option already in place. It won't change any existing modules.

I will not get to look at your code at work due to my not having your database here to hand (my policy decision not to work on non-work databases at work). This evening will be my next opportunity. If I forget myself as far as not doing so before you return from your beach holiday (burning with envy) then drop in a quick reminder for me at that time.
May 6 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
I've done most of this from MegaLog's starting code to be honest, but there are a few minor changes, and it does compile.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub BOX_NUM_AfterUpdate()
  5.     Dim db As DAO.Database
  6.  
  7.     Set db = CurrentDb
  8.     With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  9.         Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'")
  10.         If (Not .NoMatch) _
  11.         And (IsNull(!DATE_SHIP)) Then
  12.             Call .Edit
  13.             !DATE_SHIP = Date
  14.             Call .Update
  15.         End If
  16.     End With
  17. End Sub
See if this works for you. If not, then let's see where it fails or stops or, failing either of those we'll look at breakpointing the code to see what's happening that we don't expect (Debugging in VBA can help with this).
May 8 '10 #11

100+
P: 256
Well....this code is running (compiling?) but it's still not updating my table. :-( I have investigated some on debugging previously but I definetely will not say I know how to do it. Usually when my code is "bad" it won't run at all. So this will be a new challenge for me.

I did also try to attach it to the ORDER_NUM after update just to be sure if was seeing the Order_Num before trying to update the table. Got the same results....none.
May 20 '10 #12

Megalog
Expert 100+
P: 378
If it's running but nothing happens then it's probably failing on the match.
Expand|Select|Wrap|Line Numbers
  1.         Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'") 
Put stops in the code on this line and after to see if it's passing through
Expand|Select|Wrap|Line Numbers
  1.         If (Not .NoMatch) _ 
May 20 '10 #13

NeoPa
Expert Mod 15k+
P: 31,494
If I'm on Skype when you get to look at this then you can try giving me a call Danica. I'd be happy to take you through a debugging session. We could work on this code together, and you would :
  1. Find the problem.
  2. Get some proper debugging experience under your belt.
May 21 '10 #14

100+
P: 256
Two questions on Bytes were answered with this same piece of code. Initially these were two separate pieces of code. But here it is nonetheless, in case someone can gain something from it in the future.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public strLastScan As String
  5. Public db As DAO.Database
  6.  
  7. Private Sub Form_Open(Cancel As Integer)
  8.     Set db = CurrentDb
  9. End Sub
  10.  
  11. Private Sub txtScanCapture_AfterUpdate()
  12.     Dim strSQL As String
  13.  
  14.     Select Case Len(Me.txtScanCapture)
  15.     Case 3, 4
  16.         'Box
  17.         If strLastScan <> "Customer" Then
  18.             MsgBox "A customer ID must be scanned first before scanning boxes."
  19.         Else
  20.             'Is box registered in database?
  21.             If DCount("BOX_NUM", _
  22.                       "tblBOX", _
  23.                       "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  24.                 'Box does not exist in DB
  25.                 MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
  26.             Else
  27.                 Me.txtScan_Box_Num = Me.txtScanCapture
  28.                 'Box exists.
  29.                 'Assign box to current customer, set shipping date=now, and received date to null
  30.                 strSQL = "UPDATE tblBOX " & _
  31.                          "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
  32.                               ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
  33.                               ", [DATE_BOX_SHIP]=Date()" & _
  34.                               ", [DATE_BOX_RETURN]=Null " & _
  35.                          "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  36.                 DoCmd.SetWarnings (False)
  37.                 DoCmd.RunSQL strSQL
  38.                 DoCmd.SetWarnings (True)
  39.                 Me.subfrmBOX_SHIPPING.Requery
  40.                 'Update the DATE_SHIP in tblOrders where necessary
  41.                 With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  42.                     Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  43.                     If Not .NoMatch Then
  44.                         If IsNull(![DATE_SHIP]) Then
  45.                             Call .Edit
  46.                             ![DATE_SHIP] = Date
  47.                             Call .Update
  48.                          End If
  49.                     End If
  50.                     Call .Close
  51.                 End With
  52.             End If
  53.             strLastScan = "Box"
  54.             Me.tb_Scan_Cust_Num.BackStyle = 1
  55.             Me.txtScan_Box_Num.BackStyle = 0
  56.         End If
  57.  
  58.     Case 5
  59.         'Customer
  60.         'Lets find customer entered
  61.         strSQL = "SELECT   [CUST_NUM]" & _
  62.                         ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
  63.                  "FROM     tblORDERS " & _
  64.                  "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
  65.                  "GROUP BY [CUST_NUM]"
  66.         With db.OpenRecordset(strSQL, dbOpenSnapshot)
  67.             If .RecordCount = 0 Then
  68.                 MsgBox "Customer number not recognized"
  69.                 'Do whatever you want to handle this case
  70.             Else
  71.                 strLastScan = "Customer"
  72.                 Me.tb_Scan_Cust_Num.BackStyle = 1
  73.                 Me.txtScan_Box_Num.BackStyle = 1
  74.                 Me.tb_Scan_Cust_Num = !CUST_NUM
  75.                 Me.Max_ORDER_NUM = !MaxOfORDER_NUM
  76.             End If
  77.             Call .Close
  78.         End With
  79.  
  80.     Case Else
  81.         'Some sort of error or user error
  82.         MsgBox "Input error, resetting"
  83.     End Select
  84.  
  85.     Me.txtScanCapture = ""
  86. End Sub
  87.  
Thanks to everyone who helped out!
Jun 1 '10 #15

Post your reply

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