Select bound Combobox value using VBA based on variable?

[Silver_surfer]

So I have a bound combobox, lets call this Assigned_to
It has 2 Column, EmployeeID and Nama, with first column as bound column, and hidden
What I want is, to autofill this combobox with Nama, which defined in strUser and then lock it when user open the form
I have tried to use .RowSource property to no avail

Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= Dlookup("EmployeeID","Employee",[Nama]= & strUser)"
Me.Assigned_To.Locked = True

strUser is a Global Variable declared in Gvar Module

Option Compare Database

    Global strUser As String
    Global strRole As String

Which in turn get the value when a user is login to the database

strUser = DLookup("Nama", "Employees", "[UserName]='" & Me.cboUser.Value & "'")

I'm not familiar with VB, so every help is appreciated

 

[arnelgp]

strUser is a String so you need to Delimit your SQL:

Dim lngID As Long
lngID = Dlookup("EmployeeID","Employee", "[Nama]= '" & strUser & "'")
Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= " & lngID
Me.Assigned_To.Locked = True

 

[CJ_London]

so you'll be getting syntax errors with the code as provided - so what does 'no avail' mean? If you mean syntax errors, say so

You need to provide the code you are actually using and which event you are using it together with a description of any errors you are getting - otherwise we will be creeping to

 

[Eugene-LS]

I have tried to use .RowSource property to no avail

Try that way:

Me.Assigned_To.RowSource = "SELECT Nama FROM Employees " & _
     "WHERE EmployeeID= " & Dlookup("EmployeeID","Employee","[Nama]= " & strUser)

 

[CJ_London]

@Silver_surfer just to clarify - you are selecting Nama, in your rowsource - but providing it in your criteria - so why do you need it?

And @Eugene-LS your code also will not work as you have not used delimiters

Hence my question - what is really the problem. If it is a syntax issue then Arnel's solution is probably the answer, but the purpose of the code does not make sense.

 

[arnelgp]

you can just include EmployeeID to a Global variable, since always, you will need it.

Option Compare Database

    Global strUser As String
    Global strRole As String
    Global lngEmp As Long
ID

strUser = DLookup("Nama", "Employees", "[UserName]='" & Me.cboUser.Value & "'")
lngEmp = DLookup("EmployeeID", "Employees", "[UserName]='" & Me.cboUser.Value & "'")

Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= " & lngEmp
Me.Assigned_To.Locked = True

 

[Silver_surfer]

@CJ_London in my understanding, the bound column (EmployeeID) in the combobox is an autonumber and saved to table as number too, so I need to provide the EmpoyeeID instead of just Nama, is my understanding correct?? Thats why the need for DLookup, because strUser value is the username (John Doe) , not the EmployeeID.

@arnelgp I tried your first code in Form Load event, but it gave me error "invalid use of Null" in second line

Dim lngID As Long
lngID = Dlookup("EmployeeID","Employee", "[Nama]= '" & strUser & "'")
Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= " & lngID
Me.Assigned_To.Locked = True

Any help?

 

[Minty]

It makes no real sense to set a combo row source to a single record.
Simply set the value to the desired value and keep it locked.

If you display other peoples records they will be blank which will be very confusing.

Dim lngID As Long
lngID = Dlookup("EmployeeID","Employee", "[Nama]= '" & strUser & "'")
Me.Assigned_To = lngID
Me.Assigned_To.Locked = True

 

[arnelgp]

the difference between Minty's code and the OP, is that the OP got
only 1 list item, while on Minty, you will see other Employees.

 

[Eugene-LS]

your code also will not work as you have not used delimiters

may be ...
but I tried to show one of the solutions

 

[Silver_surfer]

@Minty That's another way that I haven't think of, thankyou

But same with @arnelgp code, this line

lngID = Dlookup("EmployeeID","Employee", "[Nama]= '" & strUser & "'")

gave me error "Invalid use of Null"

Where's the problem?

 

[arnelgp]

I tried your first code in Form Load event, but it gave me error "invalid use of Null" in second line

you need to open the Login form first, that is the starting point of saving your Global variable.
if you can't get it to work with Global variable, try using Tempvars collection.

 

[Silver_surfer]

Okay, I have reopen the accdb file
No error, but still the combobox is blank with no list values

Any help?

 

[arnelgp]

without knowing your "form" it can be anything.
from which Form event are you setting the Rowsource of the combo?

 

[Silver_surfer]

It's in the Form Load event

Private Sub Form_Load()
    Dim lngID As Long
    lngID = DLookup("EmployeeID", "Employees", "[Nama]= '" & strUser & "'")
    Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= " & lngID
    Me.Assigned_To.Locked = True
End Sub

 

[arnelgp]

Private Sub Form_Load()
    Dim lngID As Long
    lngID = DLookup("EmployeeID", "Employees", "[Nama]= '" & strUser & "'")
    Me.Assigned_To.RowSource = "SELECT Nama FROM Employees WHERE EmployeeID= " & lngID
    Me.Assigned_To = Me.Assigned_To.ItemData(0)
    Me.Assigned_To.Locked = True
End Sub

 

[Silver_surfer]

Okay, this is so obvious I want to kick myself
The combobox is expect 2 column, where I only select 1
So it becomes

Private Sub Form_Load()
    Dim lngID As Long
    lngID = DLookup("EmployeeID", "Employees", "[Nama]= '" & strUser & "'")
    Me.Assigned_To.RowSource = "SELECT EmployeeID,Nama FROM Employees WHERE EmployeeID= " & lngID
    Me.Assigned_To = Me.Assigned_To.ItemData(0)
    Me.Assigned_To.Locked = True
End Sub

Thanks everyone for the help, especially @arnelgp

My next question
Is .ItemData Property behaved as if the user select it themselves (meaning it can be saved), or it only display value?
I hope my question is clear, sorry for my english

 

[arnelgp]

see your form after the Load event.
there is a "pencil" meaning you are in edit mode (it can be saved).

 

[Pat Hartman]

I don't know if anyone mentioned this but as long as you are setting the value of the combo using VBA, then you can set the locked property of the control to Yes to prevent the user from changing it.

 

[Minty]

I suggested that - I don't see any point in using setting the record source, just set the value and have it locked permanently if you don't want it adjusted.