Combo Box (lookup to Query) Values not ebing saved in a table

[StephHansen]

I have a form with several fields including a combo box. This combo box is a lookup to a query based on the statement:
SELECT DISTINCT [qry_items].[ItemDesc] FROM qry_items WHERE itemid=[itemnum];

The lookup occurs fine, but the value it finds is not being stored in the table and when you go back to that record, the data for that combo box is lost and must once again be refreshed.

Any advice on how to make the data stored in the column, "ItemDesc" would be great. I do have it bound to that column. Should I do an Update Statement and if so, how and where?

Thanks! Stephanie Nicholas
Software Engineer
i-Net Strategy

 

[scriverb]

There are two pieces to the problem. The combo box properties Control Source and Bound column. Whatever is identified as the Bound Column will be stored in the Main Forms underlying Table/Query field found in the Control Source. Make sure that the Control Source is the field that you want updated with the results of the pick in the ComboBox. Then make sure that that Bound Column is the column from the query that you want to store in it.

It looks like there is only going to be one column returned from the query so the bound column property should be 1. So, when you make your pick that value should be stored in the field identified in the Control Source. You will be storing the value of the ComboBox which from your query will be Item Description. If that is not what you want then you must include another column in your query to be stored.

Let me know if I am on the right track for you and I can help you further.

Bob Scriver

 

[StephHansen]

Thank you for your quick response.

Actually, all those attributes are set, which is why I thought this so odd.

What I ended up doing was a SetValues macro. That works fine except the form must be refreshed before the values display. Do you know of a way to autorefresh this when each record is displayed?

Thanks again! Stephanie Nicholas
Software Engineer
i-Net Strategy

 

[scriverb]

If the ComboBox is bound to the correct field in your table and you want the values immediately stored after refresh of the form put the following code in the AfterUpdate event procedure of the ComboBox:

docmd.RunCommand acCMDSaveRecord

This will immediately save the value of the ComboBox and other form text boxes that might have been changed to the underlying table record.

Bob Scriver