For starters, in design view select your textbox, go into the Format tab and set its Visible property to No. This way it's automatically invisible, until a check is made later on to make sure that a value has been chosen.
If you're only looking to return a single cell value from a table, you could use a Dlookup to find the value:
For cboLocations, create an AfterUpdate event with the following code:
- Private Sub cboLocations_AfterUpdate()
-
-
Me.txtAddress = DLookUp("YourAddressColumn", "YourTableContainingTheAddressColumn", "[YourLocationColumn]='" & Me.cboLocations & "'")
-
Me.txtAddress.Visible = True
-
-
End Sub
(Note the additional single quotes for text based fields)
One thing to note is that if you have multiple records with the same address, you'd need to make sure the Row Source of cboLocations includes the unique ID column (Change cboLocations Format so that Column count is 2, but ID column width can be 0cm if you don't want it visible in the combo box), and then change the DlookUp so that the criteria checks the ID column instead:
- Private Sub cboLocations_AfterUpdate()
-
-
Me.txtAddress = DLookUp("YourAddressColumn", "YourTableContainingTheAddressColumn", "[YourIDColumn]=" & Me.cboLocations.Column(0))
-
Me.txtAddress.Visible = True
-
-
End Sub
(Single quotes removed for integer-based fields)
If you intend on extended this so that it returns values from multiple columns (so an entire record as opposed to one column's value) you'd probably be better off making sure the form is bound to that table, and then using a Recordset to navigate the form to the designated record:
- Private Sub cboLocations_AfterUpdate()
-
Dim db as DAO.Database
-
Dim rs as DAO.Recordset
-
-
Set db = CurrentDb
-
Set rs = Me.RecordsetClone
-
rs.FindFirst "[YourLocationColumn]=" & Me.cboLocations
-
If rs.NoMatch Then
-
MsgBox "No match.", vbExclamation + vbOKOnly
-
Else
-
Me.Recordset.Bookmark = rs.Bookmark
-
End If
-
-
End Sub
The DLookUp method should suffice for now.
Hope that helps.
Adam.
1
