prefilling items into an order form

[syscoandrew]

I have a form set up with a subform that allows the user to enter a customer number on the form and then enter the customers order on the subform for a 4 week booking form. There are 25 items the customer can order and I currently have it set up so the user has to type in each item number, which then brings up the item description and then the user can enter a quantity for each week.



I would like to have the sub form automatically show all 25 item numbers and descriptions so the user just needs to enter the customer number on the main form and then enter the weekly numbers for each item that customer wants to order. While not every customer will order all 25 items it seems much faster to have the items already on the form, but I am not sure how to do it and still have the specific items and quantities the customer ordered tied to the customer number from the main form.

[June7]

If the form is bound would have to create the 25 records in table then open form filtered to this set. This means you could end up with blank rows that would have to eventually be deleted.

Alternative is an unbound form with 25 rows of unbound text/combo boxes. Then code saves records for only the 'rows' that have values in them. No subform needed.

[syscoandrew]

I currently have it set up so the user has to type in the items number, which does give me the tracking I need but causes the user more work. (see attached image).

If I go with an unbound form with the 25 items and the 4 weeks of ordering how would I get those orders tied back to the customer number?

My thought was to have it bound and somehow create a macro that would enter the 25 item numbers in the subform when a customer number was entered in the main form, but I do not know how to do that.

[June7]

With either approach will need SQL INSERT statement in VBA, not a macro.

With unbound aproach:
For i = 1 to 25
If Not IsNull(Me.Controls("tbxQty" & i)) Then
DoCmd.SetWarnings = False
DoCmd.RunSQL "INSERT INTO OrderDetails(OrderID, ItemID, Quantity) VALUES(" & Me.tbxOrderID & ", " & Me.Controls("tbxItem" & i) & ", " & Me.Controls("tbxQty" & i) & ")"
DoCmd.SetWarnings = True
End If
Next

The trick is figuring out what event to put the code in. Probably a button Click that closes the form.

How it ties back to customer depends on your data structure. Probably should be a Customers table linking to Orders table linking to OrderDetails table. The above SQL would write to the OrderDetails table.

[syscoandrew]

Thank you for your reply. I think this is beyond my level at this point. I will try to figure out how to insert the items numbers into the subform, but it is needed next week so we may have to go with the entering each item process.

[June7]

Yes, this is getting beyond basic Access functionality. No matter which way, will need code. The unbound method prevents saving blank records. The bound form/controls means blank records that would have to be purged. The bound approach is actually a bit more complicated.