UPDATE Syntax not working

[Constantine AI]

Hi i have this code that Adds data into a Purchase Order Header, then displays the PONumber in a Message Box and finally Updates the PONo within the Order made which can be many records. The code i have is as follows;

Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM pordhdr")

    With rst
        .AddNew
        !SuppNo = Me.txtSuppNo
        !PODate = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderDate]
        !DueDate = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![DueDate]
        .Update
    End With
    
    'Find the NEW PONo just Assigned to the pordhdr
    'Display Message with PONo Created
    
    rst.MoveLast
        POMsg = MsgBox("Purchase Order " & rst!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    
    'UPDATE the PONo Field (Based on SuppNo and OrderNo with the last PONo above) within the ordlin Table therefore Assigning the Stock Details with a Purchase Order

    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
    
    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = '" & rst!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo] AND ((ordlin.PONo) Is NULL))"
    DoCmd.RunSQL strSQL
    rst2.Update
    
    rst.Close
    rst2.Close

I have tested the code line by line and its works upto the point of the Update part starting with rst2.

Can anybody tell me why it doesn't update the ordlin table. No errors occur! Thanks in advance.

[puppydogbuddy]

try this syntax. Have assumed all form controls referenced are text data types.

strSQL = "UPDATE ordlin SET [PONo] = '" & rst!PONo  & "' & " WHERE ([ordlin.OrderNo] = '" & [forms]![frmPOGenerator]![txtOrderNo] & "' & " And [ordlin.SuppNo] = '" & [forms]![frmPOGenerator]![txtSuppNo] & "' & " AND ((ordlin.PONo) Is NULL))"

[Stewart Ross]

Constantine Al, I think we have commented before that you are unnecessarily mixing recordset processing with SQL updates, making it difficult to understand what is going on. The SQL update in lines 23 and 24 has no relation whatsoever to the rst2 lines immediately before and after. It does not need them and it will work (or not) just as well without them.

As for what is wrong, it is impossible to say at this juncture without you telling us what you have tested, and the conditions immediately before and after your update that is apparently not working. Otherwise we are just guessing I'm sorry to say.

-Stewart