Access combo box "first choice"

[Bob Paul]

For starters, I am using Access 2002. I am not smart enough to even ask this question clearly, so I will make an example and then ask the question:

tblWorkOrder includes maintenance work requests for an apartment building. The primary key is WorkOrderNumber. The field tblWorkOrder.LocationID defines the apartment number where the maintenance problem exists.

tblMaintenance records include one or more maintenance visits to complete each work order. The records are related through WorkOrderNumber. There is also a field called LocationID in tblMaintenance. Usually, the LocationID in each table is identical. But sometimes completing the maintenance request may mean doing some work in a neighboring apartment location (water on the ceiling in apartment 202 means a water leak in 302), so the LocationID in tblMaintenance is occasionally a different location.

In frmMaintenance, I use a combo box to choose the tblMaintenance.LocationID.

Here is the problem: I would like the combo box on frmMaintenance to initially display the location found at the related tblWorkOrder.LocationID because this will be the correct location most of the time. If work has to be performed in a different location, the user can choose from the pull-down combo box to make the change.

I am thinking that the right code following an "After Update" event on the WorkOrderNumber is what I need. You can probably tell by my problem description that I am pretty much a novice at using VBA on forms, but I would appreciate an example of how this should be coded.

Regards,
BP

 

[wazz]

hi bob. i would say your description is very clear. just wondering first, is frmMaintenance a subform of frmWorkOrder or is it a separate form. and how do you get to frmMaintenance (from where?) (this might not matter in the end but it could help simplify the answer).

p.s. i see this is actually your first post so welcome to awf.
(no need to post the same question more than once.)

 

[Bob Paul]

Thank you very much for responding. Yes, I am just learning how to use this wonderful forum. To answer your question - they are separate forms (not subforms). I didn't understand subforms or sub tables when I wrote developed the application (this is a "learn as you go" project that has grown quite large).

frmMaintenance has another combo box (the contents of which is built from a query). The query selects the open work orders and lists those work orders in the combobox cmbWO on the maintenance form. The user chooses the appropriate work order and then fills in the remainder of the fields for the maintenance activity. If this does not sufficiently answer your question, please advise.

Regards,
BP

 

[wazz]

sounds well organized.

frmMaintenance has another combo box (the contents of which is built from a query). The query selects the open work orders and lists those work orders in the combobox cmbWO on the maintenance form.

add the location (tblWorkOrder.LocationID) to cmbWO; you can hide this column (columnwidth=0).

i think the AfterUpdate event will work (or possibly the OnCurrent event of the form). Use an event to set the value of tblMaintenance.LocationID.

Me.LocationID = Me.cmbWO.Column(n)

where n = the column that holds tblWorkOrder.LocationID. (columns start at zero).

Note: when you set the value, you have to make sure you don't re-set existing values. so if you're going to an existing record you might not want to do this; if you're starting a new record you could automatically set it.

so you might want to add:

If Me.NewRecord Then
    [COLOR=green]'set the value[/COLOR]
Else
    [COLOR=green]'fget abat it.[/COLOR]
[COLOR=green]    'or add special logic here.[/COLOR]
End If

 

[Bob Paul]

Thank you very much for your clear explanation. I am excited about trying this out!

BP

 

[Bob Paul]

I got it functioning and it looks great. Thank you very kindly. BP

 

[wazz]

you're welcome. thanks for posting back.