Chris said:
Hello All,
Is it possible to set the caption of a label control from
the results of a query? Can this be done in VBA. After
an ID field is entered, I need to have the .caption value
be populated from a query that uses the Id that was
entered.
There are a couple of ways you could do that, using code in the
AfterUpdate event of the ID control. If your query is a simple lookup,
you might be able to replace it with a simple call to the DLookup()
function, like this:
Private Sub ID_AfterUpdate()
If IsNull(Me.ID) Then
Me.lblMyLabel.Caption = "(unknown)"
Else
Me.lblMyLabel.Caption = _
DLookup("NameField", "MyTable", "ID=" & Me.ID
End If
End Sub
If the query is more complex, but uses the control on the form as a
crierion, then you could run the DLookup against the query itself (if
it's a stored query):
Me.lblMyLabel.Caption = _
DLookup("NameField", "qryMyQuery")
If the query doesn't use the control as a criterion, you can specify the
ID crtiterion in the Dlookup call:
Me.lblMyLabel.Caption = _
DLookup("NameField", "qryMyQuery", "ID=" & Me.ID)
If it's not a stored query, then you can build a SQL statement on the
fly, and open a recordset on it to get the value you need:
Private Sub ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
If IsNull(Me.ID) Then
Me.lblMyLabel.Caption = "(unknown)"
Else
strSQL = _
"SELECT NameField FROM MyTable " & _
"WHERE ID=" & Me.ID
Set rs = CurrentDb.OpenRecordset( strSQL, dbOpenSnapshot)
With rs
If .EOF Then
Me.lblMyLabel.Caption = "(unknown)"
Else
Me.lblMyLabel.Caption = !NameField
End If
.Close
End With
Set rs = Nothing
End If
End Sub
And yet another alternative is to use a combo box for the ID field, and
set the rowsource to extract the field you would want to look up as an
extra column in the combo box. Then you could grab it directly from the
ComboBox.Column() property and assign it to the caption:
Private Sub ID_AfterUpdate()
Me.lblMyLabel.Caption = ID.Column(1) 'second column
End Sub
