Combo box lookup confusion

[redpenner]

I have a form containing fields from two tables (#1 is my main table, #2 is a one-to-many linked on Cust_ID). The form populates properly when I run through the records using the "next record" selector at the bottom.



I added a Combo Box at the top to select the record to display. It searches on the [Customer] field , and when I scroll down through the list and select a customer it populates the form correctly. I've also added a toggle button in the Form Header that moves the cursor back to the Combo Box control so the I can select another record. It works fine.

However, when I first open the form, it is populated with the data for the first record in Table #1 even though the combo box has no selection showing. I'd like it to pull up a blank form instead when opening the form. Also, I know the tables are updated instananeously when making changes via the form but am wondering if there is a way to hold the data and update all at once, e.g. on clicking a "save" button.

I'd like the form to be used for bringing up existing records and making changes to their data, and ideally also for entering new records. Right now I have two copies of the form, one with Data Entry=Yes and the other with DE=No.

I've looked all over the internet, tried various bits of code for the combo box, changed things from bound to unbound, and called various types of Event macros and got errors or the wrong results, and I'm so confused now that I need to just start from scratch with some clear instructions. Can anyone help me out?

[jzwp11]

Welcome to the forum!

However, when I first open the form, it is populated with the data for the first record in Table #1 even though the combo box has no selection showing.

Since the form's record source is set to the table (or query), it will always open to the first record.

Out of curiosity, do you have the many side of the relationship shown via a subform on your mainform? If not, you may have trouble entering new records in the table on the many side of the relationship. I would base the main form on the customer table. Then use a subform embedded in the main for to populate the many side of the relationship.

With respect to your issue about showing the first record on form open, you could have a form ahead of your customer form where you select either to edit existing data or add new data. If someone choses to edit existing data, you can show a combo box with a list of customers. From there you would have a button that looks at the user's choices and either opens the form in data entry mode or opens the form to a specific customer to edit.

[redpenner]

Thanks jzwp11 for all your answers -

Is there some other way I could set the record source to avoid the "first record" problem?

My one-to-many table currently only has one record per Cust_ID, so effectively it's a one-to-one, but I see your point and had tried it as a subform at one point but rejected it for format reasons. I will definitely have to rethink that one...

Love your idea about the "edit or add" preform, but I would have no idea how to set that up. I don't suppose there's a wizard to help me through it in 2010??

[jzwp11]

Is there some other way I could set the record source to avoid the "first record" problem?

One option might be to hide (visible property=false) all of the form controls (with the exception of the combo box) in the on load event of the form and then in the after update event of the combo box change the visible property=true.

Love your idea about the "edit or add" preform, but I would have no idea how to set that up. I don't suppose there's a wizard to help me through it in 2010??

I don't know of a wizard, but it is fairly simple. Start with an unbound form (not bound to a table), add an option group with a couple buttons (one to edit the other to add new), a combo box based on the customer table. There will be some code. In the form load event, hide the combo box (until an option is selected). In the after update event of the option group either open the customer form for data entry or make the combo box visible. In the after update event of the combo box, open the customer form in edit mode to the record selected via the combo box.

I've attached a sample database that illustrates this. You should be able to import it into your 2010 DB and adjust names for your database.

[redpenner]

Thank you so much!! I will try this as time permits.

[jzwp11]

Let us know how it works out for you.