Update Records Selected in Subform with Field in Main Form

[chemguy120]

To start off, I am a novice to DB's and Access and well below that with coding so please bear that in mind in any answers, thank you!

I am setting up a simple database to keep track of equipment shipped for customers. The customers will bring the equipment in with their account numbers and we will generate a reference number for the return and associate that with the serial numbers for their units. Afterwards the units will be shipped out but many different customer returns will share the same tracking number. The form for customer and return entry is working swimmingly. Customer information is entered on the main form, the reference and date on the subform, and the model and serial number of the units on the sub-subform. The problem is when I go to enter the shipping data. I would like a form in which the shipment data is entered on a main form and a subform displays a combobox which pulls unit serial numbers from a query that returns just the numbers off returns where the tracking number field (the foreign key linked a shipment) is null. The desired result is to have that tracking number update to the return record. When I use this form it generates a new return record for each serial number instead. As the tracking number foreign key is a lookup, I can enter this in manually using the query and selecting the tracking from the combobox, but it would be much easier (and not to mention user friendly for coworkers) if the form would update this automatically.

Attached are screen grabs of my table relationships, the customer entry form (which is working as desired), the devices to be shipped query in design view (the query itself functions as expected), the shipment entry form (the subform combobox pulls from a list of serial numbers generated from the aforementioned query), and the return table with the undesired results from the shipment entry process.



I'm sure I'm missing something simple, but I'll never learn if I don't ask! Thanks in advance for any and all help. Once again, please keep in mind I am very new to all this. Also, I'm pretty busy so I may not be able to respond promptly so please have patience. Thanks!

[June7]

Options:

1. Unbound combobox on ShipmentEntry form to select SerialNumber and code then updates the existing record in Return table. This combobox RowSource would list only those serial numbers in Return table that do not have tracking number associated. The combobox would have to be requeried after each selection of serial number.

2. Form bound to Return table and select tracking number from a combobox to populate TrackingNumber field.

[chemguy120]

Options:

1. Unbound combobox on ShipmentEntry form to select SerialNumber and code then updates the existing record in Return table. This combobox RowSource would list only those serial numbers in Return table that do not have tracking number associated. The combobox would have to be requeried after each selection of serial number.

2. Form bound to Return table and select tracking number from a combobox to populate TrackingNumber field.

Thanks for the reply. I am assuming the first option allows for the shipment data to be entered once then update the selected return records? If so then that sounds like what I am looking for. The second option sounds like what I already do from the not shipped query itself, just in a form environment. If I'm wrong about that please let me know!

By "unbound" do you mean that the combobox is added manually in design view not related to any table and just draws off the not shipped query by invoking it in the row source? If so I am unsure of what code is required to have it update the existing return record and then requery the combobox. Does this "generate" another combo box underneath the previous so the user can see what serial numbers have already been selected during the current form session (similar to the appearance in the subform)? This sounds like it might be best for the flow to be as follows: User enters ship data, then selects the appropriate serial numbers, then hits a button to update the records in the return table. Again, for user simplicity, I prefer if possible to do this all from one form where the user enters shipment data once. I am new to all this so I'm unsure of the steps or the code, but I am learning! I'll look into how to do this evening but any guidance provided here is very much appreciated. Thanks again!

[June7]

Yes, unbound combobox, named like cbxSN. Code could be like:

Sub cbxSN_AfterUpdate()
CurrentDb.Execute "UPDATE Return SET TrackingNumber = '" & Me.TrackingNumber & "' WHERE SerialNumber='" & Me.cbxSN & "'"
Me.cbxSN.Requery
End Sub

[chemguy120]

Thanks, I will give a try when I have the chance and get back!

[chemguy120]

I have tried the code by entering it via the code builder in After Update on the Event properties tab. For simplicity I used cbxSN for the name of the combobox. When I attempt to use it nothing happens. I can select from the results of the query but after that nothing is updated to the Return table and the selected serial number remains in the combobox on the form.

I tried both a copy and paste of the complete code you gave me replacing everything Access automatically fills in and also pasting the middle line between the "Private Sub cbxSN_AfterUpdate() ...... End Sub" that is automatically entered when I bring up the code builder. The combobox is set up as an unbound control on the ShipmentEntry form with the Row Source as a query of serial numbers from the Return table where the tracking number is null and the Row Type is set to Table/Query. Is there something I am missing here?

[June7]

I just took another look at your data structure. You aren't actually saving the tracking number into Return, you are saving the ShipmentID. If the shipment record is just created then it must be committed to table before the ShipmentID can be saved to Return. Record is committed when: 1. table/query/form is closed or 2. move to another record or 3. run code to save. So try this:

If Me.Dirty Then Me.Dirty = False
CurrentDb.Execute "UPDATE Return SET TrackingNumber = " & Me!ShipmentID & " WHERE SerialNumber='" & Me.cbxSN & "'"
Me.cbxSN.Requery

Review http://allenbrowne.com/bug-01.html

[chemguy120]

Thanks! The code is working and saving the tracking numbers to the correct return records. The next caveat would be when I advance the form to a new record the previously selected serial number is still displayed in the combobox. For myself that isn't a problem, but for a general user that might be a bit confusing. Is there a way the combobox can be cleared upon advancing to a new shipment record?

[June7]

Yes. At the end of the code in post 4:

Me.cbxSN = Null

[chemguy120]

That definitely cleared the box for sure! I created additional comboboxes to the maximum number of units that can be returned in a single shipment to make it easier for a standard user to understand the process. I used that code, however, on the On Current form property so the boxes were only cleared when advancing to a new shipment record. I hope this solves the shipment entry process!

Another problem that has suddenly surfaced, my customer entry has hit a snag. It was working quite well with the following flow: Main form creates the customer record, first subform created the return reference record linked back to the customer, then finally the second subform created the return record that linked back to the reference. I had set this up via the form wizard and the first two are currently working as expected but suddenly the second subform began to give the error "You must enter a value in the 'Return.ReferenceNumber' field." In the return table the 'ReferenceNumber' is a foreign key and was created as a lookup back to the reference table and set as a required field. I tried changing the requirement to "no" and I no longer get the error message, however the return record no longer has a link back to the reference table. I don't remember making any changes to these tables or the original master and subforms before this issue popped up.

I was wondering if you might have any idea as to way this suddenly started or if it might be better to just do something similar to the shipment entry and enter the model and serial numbers into unbound fields within the reference subform that update the return table with the model and serial numbers linked back to the reference number under which they fall. The model number control could be a combobox pulling from a device table query and the serial number could be a text entry. If that is the recommended way to go I may be able to piece together the code from what you've provided previously, but it would help to have an explanation as to what the code is and does so I can attempt to adapt it to the new situation. As long as I can create a single form environment that would link return records back to the reference, which in turn are linked back to the customer information then the goal would be accomplished. I'm just confused as to why the second subform suddenly no longer works as expected. Thank goodness this is the most complicated part of the overall database I need to create, but then that's why I began with this!