using combo box value in Listbox Search (1 Viewer)

[Kevin_S]

Hopefully someone can help me out with this:

I searched this forum for the last 1.5 hrs. for the answer to this but I haven't been able to find it so I thought I would seek direct assistance, so here goes....

I have a combo box (cboList) that pulls a value from a table. I want to take this value and use it to produce similar records in a list box that is bound by a query. This is simple enough and there are numerous posts in the forum and others that address this, BUT!!! - Where this problem differs is that the value selected from the combo box is only a PART of the field information for which I want to select records from...

i.e. The field is a primary key that represents 4 distinct pieces of info and looks like this: XXX-XX-X-X

In the combo box the user is selecting on the first piece "the XXX piece" to filter so that all records that have the same first 3 characters will be displayed in the list box.

What I think I need to do is add a wildcard to the combo box result in the query criteria - something like this:

Like "[Forms]![frmSearch]![cboList]*"

But this doesn't seem to work and produces no results - Does anyone have a suggestion as to how I can make this work?

thanks in advance -Kevin

 

[Jack Cowley]

Add another column to yout query with code similar to this in the Field (top line):

pKey: Left(ID,3)

ID is your primary key. This column will have the first 3 characters of you primary key so now you can search that field using the combo box like this:

[Forms]![frmSearch]![cboList]

hth,
Jack

 

[Kevin_S]

Hi Jack - Thanks for the quick reply!

This is what I set up in a new field:

pKey: Left([PositionID],3)

When I try to run the query I get the following error message:

"The specified field '[PositionID]' could refer to more than one table listed in the FROM clause of your SQL statement"

Background: The two tables involved in the query are tblPosition and tblEmployee. They are joined through the PositionID primary key. The SQL statement (without the pKey field) is as follows:

SELECT tblPosition.PositionID, tblEmployee.F_Name, tblEmployee.L_Name FROM tblPosition LEFT JOIN tblEmployee ON tblPosition.PositionID = tblEmployee.PositionID

If you have any suggestions to solve this error that would be great!

Thanks again Jack -Kevin

 

[Kevin_S]

Nevermind Jack - I got your suggestion to work!

I just had to alter the code to represent the table the ID was coming from, like this:

pKey: Left([tblPosition].[PositionID],3)

Thanks for your help! I really appreciate it

take care
Kevin

 

[Jack Cowley]

Try this:

pKey: Left([tblPosition].[PositionID],3)

If that fails then use the other table name, but that should do it...

hth,
Jack