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

Silver_surfer

New member
Local time
Today, 16:48
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
 

Minty

AWF VIP
Local time
Today, 09:48
Joined
Jul 26, 2013
Messages
10,355
Like I suggested FIX the row source in the design of the form to :
SELECT EmployeeID, Nama FROM Employee

Set the Combo control to Locked permanently in design view

In the form load event (assuming this form only ever has a new record on it)

Me.Assigned_To = Dlookup("EmployeeID","Employee","[Nama]= '" & strUser "'")

That's it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:48
Joined
Feb 19, 2002
Messages
42,981
I don't ever dirty the form in the Load event. That code to populate the combo belongs in the BeforeInsert event so it only runs for a new record - after the user has dirtied the form - but it runs for ALL new records.
 

Minty

AWF VIP
Local time
Today, 09:48
Joined
Jul 26, 2013
Messages
10,355
I guess you could also set the default value to the DLookup() so that it avoided those whole shenanigans?
 

Users who are viewing this thread

Top Bottom