Changing RowSource in Subform Control with SQL in VBA

[PaulA]

Hi, all--

I am wanting to have the RowSource property of a subform control change based on the currentuser so that the use can only select one item in a control box.

I thought I could use the following code:

If Application.CurrentUser = "JSmith" Then
Me.frmIndContactNotesub.Form.Clinician.RowSource =
"SELECT tblEmployeeList.EmployeeName," & _
"tblEmployeeList.StaffID" & _
"FROM tblEmployeeList WHERE tblEmployeeList.StaffName = 'Smith, John';"
End If

I also tried:
If Application.CurrentUser = "JSmith" Then
Me.frmIndContactNotesub.Form.Clinician.RowSource =
"SELECT tblEmployeeList.EmployeeName," & _
"tblEmployeeList.StaffID" & _
"FROM tblEmployeeList WHERE tblEmployeeList.StaffID = 3;"
End If

While I am not getting an error in either, it's also not doing what I want: to have one item available in the control box. The control box has no items at all--it is blank.

I currently have no value in the RowSource property for the control, which is bound on column 2 which is a numerical ID, the first column being the actual name (text).

Any help will be appreciated.

Thanks.

 

[vbaInet]

You need to Requery the combo box after you've set its Row Source.

 

[nanscombe]

Could you add a Username column to tblEmployeeList?

You could then create a query, to use as the rowsource of Me.frmIndContactNotesub.Form.Clinician, with the SQL

SELECT tblEmployeeList.EmployeeName, tblEmployeeList.StaffID
FROM tblEmployeeList
WHERE (tblEmployeeList.Username=[B]CurrentUser()[/B]);

CurrentUser() is a built in Access function you can use in a query.

One query which never needs changing as long as tblEmployeeList was kept up to date.

 

[PaulA]

Thank you both for your responses which were both helpful. I was able to get it to work.

 

[nanscombe]

You're welcome.