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

[DanicaDear]

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

[Megalog]

Something like this should do the trick, may want to check the field/table names to be sure they are correct.

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)

rs.Findfirst = "[Order_Num] = '" & me.order_num & "'"

If rs.nomatch = false then

     If IsNull(rs![Date_Ship]) Then
         rs.edit
         rs![Date_Ship] = Date()
         rs.update
     End If

End If

Set rs = Nothing

[DanicaDear]

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

[DanicaDear]

Megalog,

There is a problem with line 2. I have tried

Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)
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

[Megalog]

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.

[NeoPa]

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.

[NeoPa]

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.

[DanicaDear]

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....

[DanicaDear]

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! :-)

[NeoPa]

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.

[NeoPa]

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.

Option Compare Database
Option Explicit

Private Sub BOX_NUM_AfterUpdate()
    Dim db As DAO.Database

    Set db = CurrentDb
    With db.OpenRecordset("tblORDERS", dbOpenDynaset)
        Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'")
        If (Not .NoMatch) _
        And (IsNull(!DATE_SHIP)) Then
            Call .Edit
            !DATE_SHIP = Date
            Call .Update
        End If
    End With
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).

[DanicaDear]

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 .

[Megalog]

If it's running but nothing happens then it's probably failing on the match.

        Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'")

Put stops in the code on this line and after to see if it's passing through

        If (Not .NoMatch) _

[NeoPa]

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.

[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.

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
        If strLastScan <> "Customer" 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.
                'Assign box to current customer, set shipping date=now, and received date to null
                strSQL = "UPDATE tblBOX " & _
                         "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
                              ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
                              ", [DATE_BOX_SHIP]=Date()" & _
                              ", [DATE_BOX_RETURN]=Null " & _
                         "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings (True)
                Me.subfrmBOX_SHIPPING.Requery
                'Update the DATE_SHIP in tblOrders where necessary
                With db.OpenRecordset("tblORDERS", dbOpenDynaset)
                    Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
                    If Not .NoMatch Then
                        If IsNull(![DATE_SHIP]) Then
                            Call .Edit
                            ![DATE_SHIP] = Date
                            Call .Update
                         End If
                    End If
                    Call .Close
                End With
            End If
            strLastScan = "Box"
            Me.tb_Scan_Cust_Num.BackStyle = 1
            Me.txtScan_Box_Num.BackStyle = 0
        End If

    Case 5
        'Customer
        'Lets find customer entered
        strSQL = "SELECT   [CUST_NUM]" & _
                        ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
                 "FROM     tblORDERS " & _
                 "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
                 "GROUP BY [CUST_NUM]"
        With db.OpenRecordset(strSQL, dbOpenSnapshot)
            If .RecordCount = 0 Then
                MsgBox "Customer number not recognized"
                'Do whatever you want to handle this case
            Else
                strLastScan = "Customer"
                Me.tb_Scan_Cust_Num.BackStyle = 1
                Me.txtScan_Box_Num.BackStyle = 1
                Me.tb_Scan_Cust_Num = !CUST_NUM
                Me.Max_ORDER_NUM = !MaxOfORDER_NUM
            End If
            Call .Close
        End With

    Case Else
        'Some sort of error or user error
        MsgBox "Input error, resetting"
    End Select

    Me.txtScanCapture = ""
End Sub

Thanks to everyone who helped out!