Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update subform from Unbound form (Access 2003 SP2)

    I have a situation that worked perfectly until user requirements changed.

    I have an unbound form with a combo box allowing the user to select a WorkOrder number. This then displays a bound subform containing Shipment information related to the selected WorkOrder. Within this is another subform that contains the ShippedItem records (details about the shipments which relate to the OrderDetail records). I am using ShippedItem records because there may be multiple shipments per OrderDetail record. This was working well, but now the user wants to carry the invoice number at the ShippedItem level so invoices can be reprinted, if necessary. I was planning to carry only the most recent invoice number. Because they are using pre-printed invoices, I've added a new field on the unbound form to capture the invoice number. I want to be able to populate this to the appropriate ShippedItem records as they are processed. My code currently either updates ALL the ShippedItem records with the current invoice number or updates all the non-zero ShippedItem records with the current Invoice number. Is there some way I can handle this differently without forcing the user to enter the same invoice number for each item they are shipping?

    Here is the code I'm using right now, with no success.

    Dim strSQL As String
    strSQL = "UPDATE tblShippedItem SET InvoiceNumber = " & Me.txtInvoiceNbr & " WHERE tblshippeditem.workordernumber= " & Me.cboWorkOrder & " AND tblshippeditem.InvoiceNumber=0"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Me.fsubShipmentConfirmation.Requery

    This updates ALL non-zero records (which was an improvement over the last version that overwrote earlier invoice numbers). What can I do differently? This code is run after the Invoice Number is entered and the WorkOrder Number selected but before the subforms are populated.

    Thanks in advance for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update subform from Unbound form (Access 2003 SP2)

    I's not clear to me what you actually want the code to do. If you want it to update shipped items for which the invoice number is blank, you must change

    ... " AND tblshippeditem.InvoiceNumber=0"

    to

    ... " AND tblshippeditem.InvoiceNumber Is Null"

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update subform from Unbound form (Access 2003

    My apologies for not being clearer.

    For example, we may have an order for 100 of one item; 200 of a second item and 300 of a third item. When it's time to ship, perhaps only 90 of the first item are complete and nothing else. The 90 items will be invoiced, and that invoice number needs to be applied to the ShippedItem record that corresponds to the shipment. Later, when the balance of the order is shipped, I need to apply the new invoice number to the appropriate ShippedItem records. Note that I may have SEVERAL shipments (and invoices) just to complete the one OrderDetail record which held the 100 items. How do I ensure I am updating the appropriate ShippedItem record? The way it stands now, it's all or nothing. What I really need, I suppose, is a way to transfer the Invoice Number value (entered by the user) from the Unbound Form to the second bound subform.

    Does that make any sense?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update subform from Unbound form (Access 2003

    I assume that a Shipment has some kind of unique ID field, such as ShipmentID, and that this field also occurs in the ShippedItems table. You can use this in the SQL statement to limit the records being updated to those whose ShipmentID equals the current ShipmentID in the first subform.

    strSQL = "UPDATE tblShippedItem SET InvoiceNumber = " & Me.txtInvoiceNbr & _
    " WHERE WorkOrderNumber= " & Me.cboWorkOrder & _
    " AND ShipmentNumber = " & Me.sbfShipments.Form.ShipmentID

    Substitute the appropriate names. sbfShipments must be replaced with the name of the subform as a control on the main form; this is not necessarily the same as its name in the database window.

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update subform from Unbound form (Access 2003

    As always, you've figured it out.

    The real problem was that I was trying to do the update at the wrong point. When you mentioned that I should be able to identify the ShippedItem record by its key, I realized that I was trying to update the records before I actually had that information available to me. I moved the code - adding your extra qualifiers - and it works perfectly now (pending a bit more testing).

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •