Updating the listbox based on a value selected in a combo box

[Hrithika]

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

 

[boblarson]

Just curious as to why you would use a listbox for this and not just a text box (you have in your code that it didn't work in a text box but that's how I would be doing it if it is a single person that goes in that field when approved is selected).

 

[Hrithika]

I did try using text box first. At that time my code looked like this. During the runtime, the text box was showing the select statement instead of showing the result of the select statement.

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
Me.ApprovedBy = stApproverName
End If
End Sub

 

[boblarson]

You would use this:

Private Sub Approved_AfterUpdate()
Dim stApproverName As String

stApproverName = [B][COLOR=red]Nz(DLookukp("[Name]","UIAB_STAFF","[RACF]=" & Chr(34) & fOSUserName() & Chr(34)),vbNullString)[/COLOR][/B]


If Me.Approved.Value = "YES" Then
   Me.ApprovedBy = stApproverName
End If
End Sub

 

[Hrithika]

Perfect solution. Worked like a charm. Thank you, Thank you, Thank you... Can't thank enough.

 

[boblarson]

Perfect solution. Worked like a charm. Thank you, Thank you, Thank you... Can't thank enough.

No problem, glad we were able to help.