Referencing 2 Combo Boxes to open 1 form. 2

[WishIcouldprogram]

Brief Description:

I have a Switchboard, It opens a user friendly Form to search for Last Names (combo box) from a query *I used a query because I wanted it to continuously update info that was stored on a table *listing all the Last names generated by a table. Next I created a command button to open a Form, based on the last name where users edit, input data.

OOOps, I just realized 2 or more last names can be the same. Obviously, I need to be more specific. So I created 2 combo boxes, 1.) last name(looks up from query) and 2.) first name (looks up from query based on input from combo box 1 last name) *thank you forums*

Now I would like to take input from combo box 1 and combo box 2 and still open the same Form based on these inputs.
Using the command button wizard you are allowed to "open form and find specific data to display". However, you're only allowed one "Matching Field". I'd like LastName <-> LastName & FirstName<->FirstName.

I tried copying code I found from another forum, tailoring it to my database but when I ran it. It prompted me to Enter Parameter Value. with heading "frmSelectName.LastName" when i hit ok it then showed the info that was in the combo box for last name. hit ok again, and went to firstname... same heading "frmSelectName.FirstName", hit ok and shows info previously slected in FirstName. Hit ok again takes you to a blank Form.

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLastName As String
Dim stFirstName As String
Dim stFilter As String

stLastName = Me.LastName.Value
stFirstName = Me.FirstName.Value

stFilter = "[frmSelectName].[LastName] = " & stLastName & " And [frmSelectName].[FirstName] = " & stFirstName

stDocName = "Representative"

DoCmd.OpenForm stDocName, , , stFilter

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Any help for a non-programmer or an easier solution would be greatly appreciated.

 

[PHV]

stFilter = "LastName='" & Replace(stLastName, "'", "''") & "' AND FirstName='" & Replace(stFirstName, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WishIcouldprogram]

I appreciate your help PH, all it did was clean it up a bit though. It did not solve my problem. When run, it still prompts for Enter Parameter Values...when I want it to take it straight from the combo box; and even when you enter values in the parameter query it doesn't use the correct filter and still finishes with a blank Form.

To help maybe clarify the issue below is the code Access creates when I use the wizard to set the Command button to open the Form using only the last name combo box.

Private Sub cmdEnter_Click()
On Error GoTo Err_cmdEnter_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Representative"

stLinkCriteria = "[Last Name]=" & "'" & Me![cboLastName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdEnter_Click:
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_cmdEnter_Click

End Sub

This code works great when there is just one unique last name in the DB. H/O when I choose "Smith" last name from the last name combo box it chooses one of the records for "Smith". Subsequently, if I choose the next "Smith" record in the combo box, *couldn't figure out how to remove duplicates* it opens the same record. I'm not sure if it affects the code or not but I'm also running an AfterUpdate event in case someone entered or click the wrong last name when selecting. That code is :

Private Sub LastName_AfterUpdate()

Me.FirstName = Null
Me.FirstName.Requery

End Sub

Just because I really have no idea how to program, I tried a Hail Mary! Setting the link criteria to incorporate both :

stLinkCriteria = "[Last Name]=" & "'" & Me![cboLastName] & "'" And "[First Name]=" & "'" & Me![cboFirstName] & "'"

However, when I run this Access prompts a dialog box "type mismatch". Thanks to anyone who has any suggestions and I appreciate your help PHV

 

[PHV]

stLinkCriteria = "[Last Name]='" & Me![cboLastName] & "' And [First Name]='" & Me![cboFirstName] & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WishIcouldprogram]

Thanks a lot! ... you are my MVFU... Most Valuable Forum User, have a great weekend!

 

[gcDataTechnology]

It is good to learn how to use multiple criteria for filters and parameters. Another way around this specific issue would be to set up a primary key or a unique identifier for each person in the table. Then use the combo box to list the 3 fields: [ID], [LastName], [FirstName]. Set up the combo box properties for bound column to correspond to the [ID]. If the[ID] column is listed first leave the bound column set to 1. Now open your form based upon the unique identifier and you cannot have a mistake.

 

[WishIcouldprogram]

Thank you LearningfromDabest, I appreciate your input. With the table I created, I did have a primary key for each record (Last name / First Name ) entered. Actually, if you want to get technical I created a compound key (3 primary Keys) because I wasn't sure how I was going to relate the records to other tables later.

I was able to use your method to solve this problem with relative success but I did encounter one little problem.

Creating the combo box using the wizard... obviously you want the values referenced from a table, after selecting the table I selected my fields, Last Name & First Name (I did not select the key - get to this in a moment), I chose Last Name to be ordered ascending. Now, when it lets you adjust the width of the column ~ it also lets you hide the key column. I'm assuming because I didn't test it, the wizard selects the first primary key in your table and uses that as the key column. clicking next I can create a name for the combo box. Now, in my form I can easily create a command button to take you to the form to input the data based on the first primary key criteria.

Here comes the problem : Although, this solution works I was discouraged and disappointed with the display. When I click on the combo box in Form View It does display the last name and first name. However, when you select one it only displays the last name. I would have liked it to display both the last name and first name so users could verify they clicked the right name. I'm not sure if that is something easily corrected. But if you'd like to answer it I'd appreciate it for future reference.

Thanks -