Subform record selector not working

[PrimaryKey]

Hello All,



I have an Orders form with a subform for customer selection called OrderCustomers. The subform is linked to the mainform on CustomerID:ID. At the top of the subform is combo box record selector that pulls all the customers from the Customers table, and uses the default Access macro to update the form's record based on the selection.

When I open the subform itself and use the dropdown - the feature works. But when I open the Orders form, the combo box no longer updates the subform record. Does anyone know why this is? Can I fix it somehow?

Also, I have two buttons on the OrderCustomers subform - one to create a New Customer and one to Edit the existing customer. Same thing there. The buttons work when opening the subform by itself, but not when I click on them from the main form.

Any help would be greatly appreciated - I'm really confused as to why this is.

[John_G]

The subform is linked to the mainform on CustomerID:ID.

That's your issue right there. The subform will only show records for the customer selected on the main form, so it won't let you select another customer.

However, you have your form/subform set up wrong. In a one-to-many relationship like you have, the "one" side is usually on the main form, and the "many" side on the subform.

In your case, one customer can have many orders, but not the reverse, so your main form should be for Customer, and the subform for orders. You can still link them on CustomerID.

[PrimaryKey]

That's your issue right there. The subform will only show records for the customer selected on the main form, so it won't let you select another customer.

However, you have your form/subform set up wrong. In a one-to-many relationship like you have, the "one" side is usually on the main form, and the "many" side on the subform.

In your case, one customer can have many orders, but not the reverse, so your main form should be for Customer, and the subform for orders. You can still link them on CustomerID.

John G - Thank you. Wow. I was ready to pull my hair out. I immediately tested your solution upon reading your reply, by un-linking the IDs, and sure enough, it worked.

I do have a question though. My Order form uses tabs, so the order form has 4 tab pages - 1 - Customer, 2-Products, 3-Delivery Details, 4-Notes. If the main form is linked to the Customer table, would I build out the entire tab control portion as a subform that's then dropped onto the main form? I'm guessing this would be a case where I'm nesting subforms, such that the main form (Customer) would have the Orders subform, which would then have subforms itself for Products, OrderItems, etc. Is that correct?

Would it make sense to just break the link (from Order.CustomerID to Customers.ID) and find a different way of programatically passing that Id to the main form?

[John_G]

this would be a case where I'm nesting subforms, such that the main form (Customer) would have the Orders subform, which would then have subforms itself for Products, OrderItems, etc. Is that correct?

Exactly. There would be no problem putting a separate sub-sub-form on each of the tabs, because tabs are essentially a display feature - all the controls on them are considered to part of the same form, so in your case, all the sub-sub-forms (on the tabs) are on the same sub-form.

Would it make sense to just break the link (from Order.CustomerID to Customers.ID) and find a different way of programatically passing that Id to the main form?

I don't see why you would need to. Remember, though, that the links are one-way - they pass the CustomerID TO the sub-form, not from the subform back to the main form. Once you select a customer on the main form, don't change it on any of the subforms.

How are you linking the subform to the sub-sub-forms? You will need more than the CustomerID - you will need OrderID as well (or instead, if OrderID is unique).

[PrimaryKey]

How are you linking the subform to the sub-sub-forms? You will need more than the CustomerID - you will need OrderID as well (or instead, if OrderID is unique).

That's a really good question. Do you have any suggestions there?

Perhaps I wouldn't need to. So right now, in my incorrectly designed OrderForm, I have the main form linked to the Orders table. The first tab is a subform that's linked to the Customers table with the customer's name, and business name.

Then the second tab has a "Add Item" button that when clicked launches a modal form where you select the productID of the product you want to add. The button passes the OrderID value to the modal subform, which is linked to the OrderItems table. After selecting the product and quantity you click save and are returned to the Orders form where the datasheet that's built from a query updates to show the product you've just added.

Then the third tab has subforms for the customer's billing address and shipping address (sub subform), each populated by a Select statement that pulls the CustomerId from the first tab and then uses a WHERE condition to select records only of the specified type (Billing/Shipping, etc). Somehow I need to link the address selection field to the Orders table's ShippingAddID and BillingAddID as each customer can have multiple addresses for each (which I know is strange, but was specifically requested).

Most of that stuff is working, at least somewhat, but of course I'll need to redo it such that Customers form is the main form.

I've been working on this database since 7am, so I admit I haven't fully wrapped my head around the changes I need to make yet. I'll be back at my desk at 8am tomorrow. Will you get a chance to check this thread tomorrow anytime? I'd really, really, really, really, really, really, really, really, really appreciate it if you can, just in case I run into any problems I can't figure out, which is likely. I'm still really new to Access.

[PrimaryKey]

@John_G: What if instead of changing the main form/subform source, I made it so that the Customer Selector combobox was on the main form, and used VBA to obtain a record source for the customer's billing/shipping address, name, etc. Would that make more sense then redoing the form such that the mainform is the customer form? I was just thinking about some of the example databases I had looked at. All of them have a customer selector on the form, but strangely enough that's it. No addresses, no name, just the dropdown. So I'm just wondering if it's necessary for the form to be redesigned, or if would make more sense to place the CustomerID dropdown on the main form (OrderForm) and the the subform would contain that customer's information.

Would that work? Or is that considered a no-no in Access form design?

[ArviLaanemets]

The setup main form > linked subform is not only for displaying linked records - it also links all new records entered into subform with main form automatically. Different design means, that you have to write a code which does it.

Based on your description, I'll recommend design variations:

1.

A single main form based on customers table. Displays essential info about selected customer, and has an unbound combo box for navigation.

Below customer data on main form, a single form based on Orders table as subform, linked with main form. Displays general order information, like order number, order date, etc, and has an unbound combo box for selecting an order for customer from main form.

Below order data on orders subform, a tab control, with continuous forms as subforms on separate tabs, linked with orders form (Depending your primary key selection for Orders form, the link field(s) may be OrderID, or OrderID and CustomerID)

2.

Main form like variant 1. Orders form is continuous. OnCurrent event of Orders form writes current OrderID into invisible unbound text box on main form.

On main form below orders subform, you have a tab control, with continuous forms as subforms on separate tabs, linked with unbound control on main form. On tabs are displayed full details for certain order, selected in Orders subform.

3. (Assumes orders are registered elsewhere.)

Main form like variant 1, but additionally you have there an unbound combobox, where you can select an Order linked with active customer.

Below customer data on main form, you have a tab control with continuous forms as subforms on tabs, linked with unbound combo box on main form.

4.

A single main form based on orders table. Displays essential info about selected order (included a combo box to select customer), and has an unbound combo box for navigation (with second/displayed column being a calculated one, combining customer and order info).

Below order data on main form, you have a tab control with continuous forms as subforms on tabs, linked with main form. Also, you can have on this tab control a tab with subform, linked with customer combo box on main form, to display information about this customer.

Btw, when using variant 4, you can consider having another unbound control to filter the form by customer. The combo lists all customers having orders, + "ALL CUSTOMERS" at top. AfterUpdate event of combo sets the filter for main form depending on selection, and search combos have to take this selection into account.

[PrimaryKey]

@ArvilLaanemets: Wow! Thank you! I've been wondering lately if there's just one right way to build a form, so I really appreciate you taking the time to give me some ideas of other ways of doing it.

Last night I had an idea. I originally built the form such that the customer selector combo box was inside of the Customers subform on the Orders main form. This morning I thought I'd try moving the combo box to the main form, and setting its control source as CustomerID, then linking the Customer subform on CustomerID. So now when I select a customer from the dropdown box, the subform updates with their information. John_G's explanation that a link is only one-way made me realize that if the customer selector was on the main form, it may solve my problem. The one reason I wanted to do it this way was so that a user could browse through other orders if they wanted, rather than through customers, using the main form record navigation.

One question though. I've used a couple of continuous forms for things like phone numbers and email addresses, since a single customer may have 0, 1, or 10 of each. Could you elaborate a bit on how I'd use them as part of the order process? I really appreciate you taking the time to explain this to me, and just wanted to be sure I understoof how they could be used in this instance.

Thank you again!

[ArviLaanemets]

This morning I thought I'd try moving the combo box to the main form, and setting its control source as CustomerID, then linking the Customer subform on CustomerID

Looks like variant 4! For me it looks as best choice. Unlike registering orders, registering customers is an activity you aren't doing every day. So yo can have a separate form for this, and use customers table as some registry you read data from whenever you need.

I've used a couple of continuous forms for things like phone numbers and email addresses, ... Could you elaborate a bit on how I'd use them as part of the order process?

In orders table, you have all info about an order, what you can keep in a single table row, and you can't calculate, like order number, order date, customer, delivery terms, payment terms and conditions etc. Let's name this dataset Order Header Data. You start registering the order creating a new record in orders form, and entering all this data (or at least the minimum set of it needed to register the order). NB! Until you haven't left the orders form, or select another row on orders form, you always can interrupt the order registering at this stage - simply press Esc a couple times! You also can use BeforeUpdate event of main form for checking, is this minimum set of data entered, and abort order registering or leaving the orders form or the new order, when not.

After Order Header Data (or minimum set of it) is entered, you can register order details - i.e. info you enter in subforms. A classic example is named exactly this - Order Details. Into order details table, you enter the info which in printed order is usually in rows: article number (article name is read from another registry table, tblArticles), measuring unit, article quantity, [Unit price], serial number of part, when this is needed, etc. NB. Depending how the unit price is determined, you can have it in order details table, read it from tblArticles, or read it from separate article prices log table. You don't save monetary sum for row here, as you can always calculate it from quantity and price. Where you store VAT percent, in order header or order detail table, depends on your business model - when same VAT percent is applied to all sales, then you keep it in order header, otherwise obviously in order details.

You can have all those calculated values in your forms displayed too - using unbound controls for this.

NB! Activating subform means leaving main form. So it starts update process for main form immediately (running BeforeUpdate event, and when update was not canceled, saving the new/edited order.

After you have successfully activated subform (for my example order details form), you can register those detail rows. Create new row, enter nessecary data, create next row etc.
You must have a text box linked with OrderID in order details table on subform. When you create a new record in subform, OrderID is read from main form and written into control automatically. NB! Into control! Into table it is written when the record is saved. It means, that like in main form, you can always abort record creation when you haven't saved it yet!
And usual practice is, that the control linked with OrderID field is set invisible. User doesn't need it!

Unless user has to manually enter UserID, you set OrderID control in main form invisible better too. And in case some script is calculating OrderID, it writes it into this control, not into table.