Use Select Case to Open specific record

[Conner]

Not sure this is correct forum, but I have a question about a bit of VBA code that opens various forms in my Database...

A form gets opened depending upon the PIN code.

Here's the code:

Private Sub PinCode_Click()
Dim strInput as string

strInput=InputBox("Enter your PIN Code&quot

'Depending on PIN Code, a different form opens.
Select Case strInput
'PIN Code "1234" opens frmX
Case 1234
DoCmd.OpenForm "frmX",acNormal

'PIN Code "5678" opens frmY
Case 5678
DoCmd.OpenForm "frmY",acNormal

....and so on. But here is my problem

'The PIN Code entered by the employee needs to open _
'frmZ to that employee's record. It's this next Case
'that is giving me trouble...

Case WHAT GOES HERE? HOW DO I TAKE THE PIN CODE
ENTERED BY THE EMPLOYEE AND "LINK" IT TO THAT
EMPLOYEE'S RECORD ON frmZ?

The Else Case is reserved for a msgbox "Invalid
Entry.Try Again"

To make matters worse, the form I want to open has a subform.

 

[jrbarnett]

Conner

If there are several Case statements that would open form Z, then that is fine. For example:

Case 9876
Case 8765
Case 7654
DoCmd.OpenForm "frmZ", WhereCondition := "ID='" & strInput & "'"

The WhereCondition argument passes across the ID and filters it to only display records that match the particular ID. The "ID" needs to match a field on the form's dataset.

John

 

[Conner]

Thanks. Will give it a try. I'm having to post another question about combo boxes in just a moment. What you gave me answers part of what I'm trying to do, but I may be trying to make a combo box jump through too many hoops...Again, thanks.