I have a combo box called Approved. If I select YES from the ComboBox, I want a listbox named ApprovedBy further down on the form to get the name of the user logged in. I put the following code in the AfterUpdate event of the combo box, but it is not generating the value in the list box. I would have preferred the value to be put in a textbox, but a search in the forum revealed it is not possible without writing a different function and listbox would be the easier route. Both these controls are bounded to a table. The query to get the name of the user logged it works perfectly if executed outside the form. Can someone point me to the mistake I am doing?
Thanks in advance.
Private Sub Approved_AfterUpdate()
Dim stApproverName As String
stApproverName = "SELECT UIAB_STAFF.Name" & _
"FROM UIAB_STAFF " & _
"WHERE (((UIAB_STAFF.RACF)=fOSUserName()))"
If Me.Approved.Value = "YES" Then
'Refreshing old data using requery
Me.ApprovedBy.Requery
'Storing the result of the query in a listbox. Storing in textbox did not work
Me.ApprovedBy.RowSource = stApproverName
'Selecting the first option from the rowsource of the listbox into the listbox
Me.ApprovedBy = Me.ApprovedBy.ItemData(0)
End If
End Sub
Thanks in advance.
Private Sub Approved_AfterUpdate()
Dim stApproverName As String
stApproverName = "SELECT UIAB_STAFF.Name" & _
"FROM UIAB_STAFF " & _
"WHERE (((UIAB_STAFF.RACF)=fOSUserName()))"
If Me.Approved.Value = "YES" Then
'Refreshing old data using requery
Me.ApprovedBy.Requery
'Storing the result of the query in a listbox. Storing in textbox did not work
Me.ApprovedBy.RowSource = stApproverName
'Selecting the first option from the rowsource of the listbox into the listbox
Me.ApprovedBy = Me.ApprovedBy.ItemData(0)
End If
End Sub