How to save information from multiple combo boxes at once?

[slenish]

Hi all,

I have asked this question a couple of times but I keep getting no answer so I thought I would give it one more go.

I have 3 combo boxes on a form. Each of these combo boxes pulls information from a table on the back end, but the list is not complete. What I want to do is use the NotInList function so that I can update the list when a new value is introduced.

But here is the complicated part. I know how to use the NotInList function for each box individually but I want to expand on this where I can tell it to save all of the information at once instead of one at a time. Reason being is because on the back end when it saves to the table it will save all the information in one row and not a new row each time.

I dont know if this can be done or not but so far im thinking not.

Appreciate any help on the matter. Thanks

Slenish
Using: Access 2003

[patjones]

Hi -

Are you connecting to the back end using ADO or with linked tables? And I assume each box is pulling it's list from the same table...just different columns, correct?

Pat

[slenish]

Hi Pat

I am connecting to the back end with linked tables. Also yes each box is pulling from one table just different columns. Also I have it so a query runs in the background to help pull the information in to each box. Based off of the first box. So if you type in an ID number in the first box the rest of the boxes will pull from the list based on that ID number.

Just cant figure out how to get them to save if the information is not in the list.

Appreciate the help :)

[slenish]

Hi Pat or who ever else may be able to help. I found a short paragraph on microsofts site that describes exactly what I want to do. Its mostly the part at the end where it talks about making a dynamic combo box that will prompt users to enter in all the information before they can save. How would I do that?

"Note The above example adds an item to an unbound combo box. When you add an item to a bound combo box, you add a value to a field in the underlying data source. In most cases you can't simply add one field in a new record — depending on the structure of data in the table, you probably will need to add one or more fields to fulfill data requirements. For instance, a new record must include values for any fields comprising the primary key. If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value."

[patjones]

The problem here is that Access won't let you leave the first box unless you pick something in the list. However if you set the "Limit to List" property for the box (and actually all of the boxes) to "No", it will let you leave the box with the value you typed into it.

So I think it is possible for you to tab through several boxes, typing a new value in each one, and then saving the record after exiting the last box (prompting the user if you wish). Let me know what you think.

Pat

[patjones]

I should say that I tried this with unbound combo boxes (control source left blank, row source set to a column in the table). In my opinion, this is a flexible way of doing it.

When I tried it with bound controls, it worked fine as well, but you have to be careful about editing an existing record. If you make a change and tab out of the box, that change is immediate. I found that when I hit the "New Record" button, both of my boxes cleared and allowed entry of new values (even leaving one box blank if I wanted) resulting in a new record in the underlying table.

Pat

[slenish]

Thanks Pat,

For all the help. I will have to play around with that and see if I can get it to work right :)

The other option im thinking about is maybe just having it where if the user types in a number that is not in the list, I could have a sub form pop up that lets them just type in all of the information and then saves it on the back end up dating the table list. Then they could continue on. I'm going to test your idea first. I'll let you know what I find out

Thanks again