Updating combobox based on curent record selection

[Thumbs]

Complex question incoming, thanks in advance for any help you might be able to offer.

I have a table called OperatingLog which contains various information. Some example columns are OLDescription, OLPartNumber, OLSerial Number, etc.

There is another table named AssetData that contains the data that I want to be populated in these fields. It contains the same number that will be entered in OLDescription, as well as field for the PartNumber and SerialNumber, etc.

Assume all of these fields have text values.

I am trying to set up the table (throgh a query or a form, i have tried both) so that when the user manually enters in the OLDescription, the rest of the fields either auto populate(which I havent tried and am not too worried about), or the selectable values (they are combo box fields) change to reflect the value on that column in the AssetData table that is associated with the OLDescription in AssetData.

For instance, user is viewing a search form that displays its results in a subform below. The subform is based on a query that is based on the OperatingLog table. When the user enters 123456789 in the OlDescription field to create a new record, I want access to look in the AssetData table when that field is no longer in focus, find the record associated with 123456789, then pull the data from the PartNumber and SerialNumber columns and put it in the OLPartNumber and OLSerialNumber fields in the OperatingLog table, or allow only that value to be selected from a combobox on the form. I dont mind if the user has to go through and select the values, so long as there is only one value in the list (the one that was pulled from AssetData).



The closet I have come is putting this code on the row source of the combobox in the sub form. It only returns the values of the first record though. When I move to the next record down, the combobox retains those values instead of updating them based on the newly selected record with a different value for OLDescription.

SELECT [AssetData].[SerialNumber], [AssetData].[Identifier] FROM AssetData Left JOIN OperatingLog ON [AssetData].[Identifier] = [OperatingLog].[OlDescription] WHERE (([SearchOperatingLogs].[OlDescription]) = ([AssetData].[Identifier]));

I have tried using different joins and I have tried referencing the exact field on the form in the WHERE statement, which only causes the form to prompt me for a value for OLDescription instead of pulling it from the query, the table, or the form (I have tried all 3).

Is there a way to reference the current selected record from a query like this or is there something I can do with VBA on any of the event properties that might help? I am still learning Access and the languages used in it, and my knowledge is not up to snuff yet.

And I put this inthe queries section because I think it is going to deal more with the SQL behind what I am trying to do. Again, I'm a little green, so I'm not 100% here.

[mkallover]

You're probably going to have to use VB to do this. You can use one of the events (probably After Update) to set the value of the other form fields to whatever you want based on the value in the field that was just updated.

I would take a look at the DLookup function in VBA.

[Thumbs]

Hmnm, ok. Thanks. I didnt hink about using DLookup.