Need a form control to not retrieve data from a table when not needed

[WCStarks]

I have a table for Sites, which includes name and type, among other fields.
I have a Asset transaction table which links Assets to Members (Custodians) and Sites, to provide historical status information about assets.
The Asset Transaction form includes a combo box to select a member as Custodian, a combo box to select a Site and control to display the Site Type from the Sites table, among other controls.

As it is now, the sub-form, under Assets, requires me to select a site, or it reports Site_ID cannot be found (when none has been selected). It is the Site_Type control, which is causing this error.

I need the Site_Type display control to retrieve the Site_Type data from the Site table, only when the Site Combo box has selected a Site, in which case, both the [Site] Combo box and the [Site_ID] will have a value.



After several different attempts to tell the Site_Type control not to retrieve data when a site has not been selected, I cannot figure out how to do this.
The Sites.Site_Type happens to be combo box to select Types for Sites. Since the control in this context is for display only, I have it disabled. I also tried using a simple text field to display the site type, but, of course it still attempts to retrieve data when no site has been selected.

The two controls in the sub-form are highlighted.

[WCStarks]

After I posted this query, I kept trolling the Internet for a solution, and came across this one:
In the sub-form, I replaced the Site_Type field from the Sites table, with an unbound text box.
Then in the After update property for the Sites combo-box (which already included the Site_Type column), which I was already using to assign the Site_ID, I added a second command to save Site.Column(2) into the unbound control.
It does just what I need it to do, so I'll mark this post as Solved.

Private Sub Site_AfterUpdate()
    Site_ID = Site.Column(0)
    SiteType = Site.Column(2)
End Sub

Since the unbound control is for display only, I disabled it, to avoid confusion.

[ssanfu]

1) You could also concatenate the SiteName and the SiteType in the Site combo box. Wouldn't need the unbound Text box.

The combo box Row Source would look something like

SELECT Site_ID, SiteName & " - " & SiteType As SiteNameType, AnotherField FROM tblSites;

Then set the combo box properties:
Bound Column = 1
Column Count = 2
Column Widths = 0



Or
2) You could add a field to the Sites table for the concatenated SiteName/SiteType, then modify the SQL for the combo box Row Source.

[WCStarks]

Thanks. I like the first option better. I was trying to have the combo box show two fields after selection, but I guess that can't be done. So, your first option will fit the bill.

[WCStarks]

I implemented your option 1 and it works fine. Thanks again.

[ssanfu]

You're welcome....

[WCStarks]

I posted another problem in the Forms forum. Can you help with it?

[WCStarks]

Synchronize a main form from its subform