Select bound Combobox value using VBA based on variable? (1 Viewer)

Silver_surfer

New member
Local time
Today, 08:58
Joined
Jan 29, 2020
Messages
14
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
Code:
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
Code:
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
Code:
strUser = DLookup("Nama", "Employees", "[UserName]='" & Me.cboUser.Value & "'")

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
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

Super Moderator
Staff member
Local time
Today, 01:58
Joined
Feb 19, 2013
Messages
16,553
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:58
Joined
Feb 19, 2013
Messages
16,553
@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

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
you can just include EmployeeID to a Global variable, since always, you will need it.

Code:
Option Compare Database

    Global strUser As String
    Global strRole As String
    Global lngEmp As Long
ID
Code:
strUser = DLookup("Nama", "Employees", "[UserName]='" & Me.cboUser.Value & "'")
lngEmp = DLookup("EmployeeID", "Employees", "[UserName]='" & Me.cboUser.Value & "'")

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

Silver_surfer

New member
Local time
Today, 08:58
Joined
Jan 29, 2020
Messages
14
@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
Code:
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

AWF VIP
Local time
Today, 01:58
Joined
Jul 26, 2013
Messages
10,355
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.

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
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.
 

Silver_surfer

New member
Local time
Today, 08:58
Joined
Jan 29, 2020
Messages
14
@Minty That's another way that I haven't think of, thankyou ;)

But same with @arnelgp code, this line
Code:
lngID = Dlookup("EmployeeID","Employee", "[Nama]= '" & strUser & "'")
gave me error "Invalid use of Null"

Where's the problem?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
without knowing your "form" it can be anything.
from which Form event are you setting the Rowsource of the combo?
 

Silver_surfer

New member
Local time
Today, 08:58
Joined
Jan 29, 2020
Messages
14
It's in the Form Load event
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
Code:
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

New member
Local time
Today, 08:58
Joined
Jan 29, 2020
Messages
14
Okay, this is so obvious I want to kick myself
The combobox is expect 2 column, where I only select 1 🤦‍♂️
So it becomes
Code:
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 :(
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:58
Joined
May 7, 2009
Messages
19,169
see your form after the Load event.
there is a "pencil" meaning you are in edit mode (it can be saved).
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:58
Joined
Feb 19, 2002
Messages
42,981
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

AWF VIP
Local time
Today, 01:58
Joined
Jul 26, 2013
Messages
10,355
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.
 

Users who are viewing this thread

Top Bottom