Problem with combo box on form.

[Skip Bisconer]

I have a form built off a query of a table that has three comboboxes among other fields. This is the SQL from the query. First in the underlying table I made the DriverID, RouteID and VehicleID a lookup field. When I used the query as a Control source for the form I would get the lookup portion of the combobox in the drop down menu but I wasn't able to select any item off the list. So I thought I would make them unbound. I could select the item because I pulled them of the actual Drive, Route, vehicle lookup tables but the form wouldn't update the query. I made the query field as the datasource but that didn't work. So I tried to undo the lookup fields in the table and just added them as text fields and type in my values and it wouldn't let me do that. I originally built the ID fields in the OrderHeader to beable to use therelationship of the Driver, Route, and vehicle and assign the value to the query from the form. I am really missing the boat here. Can some one help?

SELECT [tblOE Invoice Header].OEOO_ORDR, [tblOE Invoice Header].OEOO_STAT, [tblOE Invoice Header].OEOO_CUST, [tblAR Customer Master].ARCM_NAME, [tblOE Invoice Header].OEOO_SHPV, [tblOE Invoice Header].OEOO_OSZP, [tblOE Invoice Header].DriverID, [tblOE Invoice Header].RouteID, [tblOE Invoice Header].VehicleID
FROM [tblAR Customer Master] INNER JOIN [tblOE Invoice Header] ON [tblAR Customer Master].ARCM_CUST = [tblOE Invoice Header].OEOO_CUST
WHERE ((([tblOE Invoice Header].OEOO_STAT)=49 Or ([tblOE Invoice Header].OEOO_STAT)=50) AND (([tblOE Invoice Header].OEOO_SHPV) Like "*OUR TRUCK"))
ORDER BY [tblOE Invoice Header].OEOO_ORDR;

 

[GolferGuy]

What is the purpose of this form you are dealing with? You have data coming to a form from the CustomerMaster and InvoiceHeader tables. Are you trying to update the CustomerMaster or the InvoiceHeader? Which ever table you are trying to update, that would be the only table you should need in the record source for the form. Generally, the CustomerMaster would be in a Parent form, and the InvoiceHeader table would be in a subform. By combining the two tables into one query, you have made a query that can not be used to update either table.

 

[Skip Bisconer]

Thanks for responding Vic, The fields I need to add data to are in the Invoice Header file. Each order that is to be delivered by our truck needs to have a Driver, Route and Vechicle assigned to them. I really wanted to do this through a query so I could have a listing of just the revelent orders but I couldn't get the form to update those fields using the query as a control source. I have the same issue when using the form on the table directly as the control source. I can open the table and enter data in those fields but I can't get there through a form.

 

[Skip Bisconer]

I have experiment a little since my last post and to add to my further quandery I created an abbreviated table from the select query on the original table and I created a form and am able to update that abbreviated table with a simple form.

What do you think the issue is with the OrderHeader table? I can make this work by deleting the abreviated table and use an append query to carry forward the new nightly down load but there must be a reason I cannot access those fields through other controls in the original query

 

[GolferGuy]

Skip, upon re-reading your posts, I still don't understand just what you are trying to do. You said "I couldn't get the form to update those fields using the query as a control source." But earlier, it sounded like you wanted the query to be an update query. An update query can not be used as the control source for a form. At least it is not logical, but I must admit I have never tried it. Your last post you said that you have used an abbreviated table with a simple form, and that works. I assume an abbreviated table and simple form means you have just a few fields that you are using on the form, and the table is the record source for the form. So, please explain how you see a form updating these fields with a query. I only know of either a query updating fields, or a form being used to update one of more fields. But not both a query and a form at the same time.

 

[Skip Bisconer]

Probably I have confused the issue because I have tried so may ways to make this happen.

The OrderHeader table has three fields among others as lookup fields for Driver, Route and Vehicle with their corresponding tables as a row source.

I made a query to view only the order numbers that pertained to delivery VIA our truck. The object being to add a Driver name, Route Letter, and Vehicle to every order in the query via a form.

I made a form with the query as a control source using all the available fields and created a unbound combo box using the order number in the query to find a record on my form, course the three fields I mentioned above are blank at this moment. The idea was to get the Driver name in the Driver name box etc; etc. using the dropdown combobox from the query. It would dropdown but I was unable to select any name, route or vehicle on the list.

So then I made the table itself the control source following all the same steps as above with the same result. Then I think....maybe it doesn't like the Lookup feature from the table fields so I made the table fields ordinary text fields, repeated the steps above and tried using unbound comboboxes and field defaut values pointed to the combo boxes. Could not get anything to happen.

The only way I could make it work is I used the query I had in the begging and made it a make table query then used that table as a control source for my form and I was able to achieve the desired results with the lookup fields.

This seem really odd to me that I cannot achieve this using the original table. I mean its no big deal to make a delet and append queries to handle it but it just seems to me that my original plan should have worked. The original table is a nightly update to Access 2003 database originating from our main sever and I am using it in a 2007 database and I am wondering if the original table is somehow protected. My software people say it is raw data comeing from the server. I had to add the three fields I wanted to populate to this table as it isn't part of the updated coming from the main server.

 

[gemma-the-husky]

the query you have designed is propbably non updateable

not sure where the link is, but there are helps here

"why is my query non updateable" and the answer is often to do with either

a) primary keys not avaialble in all tables (possibly your doiwnloaded tables)
or
b) using some sort of totals query (with a groupby clause)

--------
ignoring the form, try opening the underlying query directly and see if you can edit data in there - you probably cant becauses its not updateable

 

[Skip Bisconer]

The Orderheader table does not have a key and I cannot update from the query directly. I will research the forum some more for an answer.

The Order Number is unique in the header file. But at some time in the future they may reset Order numbers and duplicates could creep in. Some one else feeds me the updated file so I am going to see if he can assign an autonumber ID as a primary key with out messing up the updating process. I can't see where that should be a problem.