Problems using form variable as query criteria

[cmledbetter]

I hope I can sequence this so that you are not a as dizzy as I am attempting
to sort this out.

I have four forms built that use a unbound combo box named "EmpLookup" to
select an Emp_No to display Name and HireDate for the Emp_No selected then
display the linked fields in subform below. Linking no problem.
The combo box is based on qryEmpListActive (for all employees) or
qryEmpListActiveMgr which filters employee list to only departments owned by
login user. The main for has qryEmpListActiveOne as its record source. This
query has a function get_nmEmpNo() as criteria for records returned. (WHERE
(([Employee Data].Emp_No)=get_nmEmpLookup ())

The following code is included in Module 1

Dim nmEmpLookup As Integer
nmEmpLookup = ""


Public Function setEmpLookup(EmpLookup As Interger)
nmEmpLookup = EmpLookup
End Function

Public Function Get_nmEmpLookup() As Integer
Get_nmEmpLookup = nmEmpLookup
End Function

When the form opens I receive a error compiling message. I expected the form
to open with blank fields and once I select an Emp_No from the EmpLookup
combo box the After_Update event calls the following code.


Private Sub EmpLookup_AfterUpdate()
'function to set EmpLookup to variable nmEmpLookup
Call setEmpLookup
'Requery my form to place matching data on form
Me.Requery
End Sub

This is one of my first attempts at code to automate things in this DB that
has grown bigger than I had expected when I started this project.

Please advise

I have made this work by referencing the controls on each individual form
with [Forms]![MyForms]![EmpLookup] but this takes four differentqueries to
address the four different forms.

 

[crawford.francis]

I am not a hundred percent sure what you want to accomplish but why
dont you add your combobox to the form with the following = "SELECT
emp_no FROM EMPLOYEE".....form should be based on the table EMPLOYEE

Then in the After_Update event of the combobox open a recordset using
the following code:
Then fill the text fields:

strSQL = "SELECT name, hirdate FROM EMPLOYEE WHERE emp_no
= " & lstEmpNo
Set rstEmployee= dbsEmployee.OpenRecordset(strSQL,
dbOpenSnapshot)
txtName = rstEmployee!name
txtDatehired = rstEmployee!hiredate
rstCounter.Close

Then to add a subform just use the emp_no as master and child

 

[cmledbetter]

Since I have my db set with user-level security and access tables through
RWOP queries. I have the following as my combo box criteria

SELECT qryEmpListActive.Emp_No, qryEmpListActive.Name FROM qryEmpListActive
ORDER BY [Emp_No];

The selection is named EmpLookup

Then the After_Update Event code would be as follows:


Private Sub EmpLookup_AfterUpdate()
strSQL = "SELECT Name, HireDate FROM qryEmpListActive WHERE Emp_No = " &
EmpLookup

Set rstqryEmpListActive = dbsqryEmpListActive.OpenRecordset(strSQL,
dbOpenSnapshot)

txtName = rstqryEmpListActive!Name
txtHireDate = rstqrtEmpListActive!HireDate
rstCounter.Close
End Sub

When I run this code I get a runtime error #424