Dlookup and combo boxes

[Jose]

Hi,
I am trying to pass a value from a combo box CboEmployee which is in a form called LogonForm to the combo box SBUCbo which is in a form called SBUForm. The idea is when someone selects his/her name is the CboEmployee I can do a dlookup to obtain the department where the person works and then that value is passed to SBUCbo when the form is opened. I am using the below code but it is not working.



DoCmd.OpenForm "SBUForm"
Forms!SBUForm!SBUCbo.Value = DLookup("[SBU_Name]", "Tbl_Employees", "[IDEmployee]=" & Me.CboEmployee.Value)
DoCmd.Close acForm, "LogonForm"

Just to say that IDEmployee is a number.

I was wondering if anybody could help

Thanks

Jose

 

[John Big Booty]

Why not use the OpenArgs portion of DoCmd.OpenForm to pass the value of CboEmployee.

Then you can simply use;

Me.SBUCbo = DLookup("[SBU_Name]", "Tbl_Employees", "[IDEmployee]=" & OpenArgs)

In the Form's (SBUForm) On Load event

 

[Jose]

Thanks for your help

I have entered this code on my Logon Form

DoCmd.OpenForm "SBUForm", acNormal, , , acReadOnly, Me.CboEmployee.Value

and I have entered the below code in the Form's (SBUForm) On Load event

Me.SBUCbo = DLookup("[SBU_Name]", "Tbl_Employees", "[IDEmployee]=" & OpenArgs)

But it doesnt work.. do you know what I am doing wrongly?

Jose

 

[vbaInet]

Change OpenArgs to Me.OpenArgs

When you say it doesn't work what do you mean? It throws an error or SBUCo remains blank?

 

[John Big Booty]

Change OpenArgs to Me.OpenArgs

...

Just been playing in my sand box and either OpenArgs or Me.OpenArgs will work.

However that still begs the question; "In what manner is it not working?"

 

[John Big Booty]

The next logical question is what is being stored in the bound column of Combo CboEmployee?

 

[vbaInet]

Just been playing in my sand box and either OpenArgs or Me.OpenArgs will work.

Yep, true - just like any form property would. I was just getting the OP to get used to using Me. when calling form props/methods.

However that still begs the question; "In what manner is it not working?"

That's anyone's guess

 

[John Big Booty]

Yep, true - just like any form property would. I was just getting the OP to get used to using Me. when calling form props/methods.

....:

Fair point

 

[Jose]

I get this message
Runtime error '3075':
Syntax error (missing operator) in query expression [IDEmployee]='

 

[vbaInet]

Looks like John is away from his computer so I will ask you this. IDEmployee is a text field right?

 

[Jose]

nope it is a autonumber

 

[Jose]

IDEmployee comes from a table

 

[vbaInet]

In that case Me.OpenArgs isn't returning a value. Put a msgbox on it and see the value.

 

[Jose]

Basically I found the below code in the internet to create a login form. When we get to

DoCmd.OpenForm "SBUForm" I would like to pass the name of the department to the SBUcbo combo in the SBUForm based in the value entered in the combo CboEmployee. so I need to use a Dlookup. CboEmployee is SELECT [Tbl_Employees].[IDEmployee], [Tbl_Employees].[EmployeeName] FROM Tbl_Employees ORDER BY [EmployeeName];

Option Compare Database
Private intLogonAttempts As Integer


Private Sub CboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.TxtPassword.SetFocus
End Sub

Private Sub CmdLogon_Click()
'Check to see if data is entered into the UserName combo box

If IsNull(Me.CboEmployee) Or Me.CboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.CboEmployee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.TxtPassword) Or Me.TxtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.TxtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.TxtPassword.Value = DLookup("EmpPassword", "Tbl_Employees", _
"[IDEmployee]=" & Me.CboEmployee.Value) Then

IDEmployee = Me.CboEmployee.Value

'Close logon form and open splash screen

DoCmd.OpenForm "SBUForm"
DoCmd.Close acForm, "LogonForm"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.TxtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub

I hope the info helps now.

 

[Jose]

Cool, i will do it tomorrow.. I go to bed now.. thanks for your help