Open Form on Correct Record 1

[EvilTwinOfAtrus]

Hi

This is similiar but not identical to a previous problem I was having, but please can any answer be starting from scratch . OK, first of all the 2 forms that I am about to refer to both get their values from a table called 'tblEmployee'.

I have one form - named 'frmEmployeeSelector' which has a list box on it with a list of employees coming from the 'EmployeeWholeName' field on the aforementioned table.

I also have another form - named 'frmEmployee' which contains a certain employees information such as date of birth, telephone number etc etc.

What I want to do is, doubleclick on one of those names in the list box on 'frmEmployeeSelector' and it bring up 'frmEmployee' on the corresponding record.

Help would be greatly appreciated

- Karl

 

[JamieDeVry]

Hello Karl. Your problem is quite common. I will assume that the value that is stored in the listbox control has a primary key value. So the value '01' would be for the employee name Jamie Bates. When you douleClick over the listbox control it returns a value. Store that value in a variable. For Example:

Dim strEmployeeID as String

Private Sub lstEmployee_DblClick()

strEmployeeID = Me.lstemployee.Value

Docmd.OpenForm FormName:="frmEmployee", WhereCondition:="EmployeeID=' " & strEmployeeID & " ' "

End Sub


the above example simply uses the OpenForm method to open a given form based on the Where clause of a SQL statement. For more advanced topics, review my textbook on line, Database Concepts at:

http://www.nj.devry.edu/~jbates/publishing/DatabaseTOC.htm

More advanced example are in my book in the Database Programming and GUI-Dialogs chapters.

God Bless!

Jamie

http://nj.devry.edu/~jbates

 

[EvilTwinOfAtrus]

Just one question, what do you mean by variable in this case?

 

[EvilTwinOfAtrus]

Ok this is what I have now which is giving me a 'Type Mismatch' error:

Private Sub lstEmployee_DblClick(Cancel As Integer)

Dim strEmployeeID As Integer

strEmployeeID = Me.lstEmployee.Value

DoCmd.OpenForm "frmEmployee", "EmployeeID=' " & strEmployeeID & " ' "

End Sub


Just thought It'd be worth noting that EmployeeID is an autonumber field. Please help. Thankyou,

- Karl

 

[JamieDeVry]

the mismatch error is becasue I failed to realize that the Employee ID is in a numerical format so your code should now look as follows:

Private Sub lstEmployee_DblClick(Cancel As Integer)

Dim intEmployeeID As Integer

intEmployeeID = Me.lstEmployee.Value
DoCmd.OpenForm "frmEmployee", "EmployeeID="&intEmployeeID

End Sub

The difference is that SQL does not require the small quotes for numeric values like it would for text strings. I hope this works for you.

God Bless!

Jamie


Prof: Jamie Bates
DeVry University
630 US Highway One
North Brunswick, NJ 08902

http://nj.devry.edu/~jbates