Multiple fields using to search

[Guest]

Hi,

Something i've been batteling for weeks with and theres probably such a
simple answer to it and i just cant see it:

Riiiight, i have this form (lets call it frmClientSearch)

On this form i have a 2 list box thinghys; One called lstEmployee which is
populated with Employee names and the other lstCity which is populated with
City Names.

Then i have a command button (cmdSearch) which, once click and values
selected in both the lst boxes, should return only the values that are equal
to both the lst boxes in a new form called frmSearchResult.

(Hope i make sense)

So... I know how to do this if i have only one list box. (The wizard does
the work) but i dont know how to do it with multiple "criteria" (for lack of
a better word) boxes.

PLEASE HELP!!!

Fanks!!!

 

[John W. Vinson]

Hi,

Something i've been batteling for weeks with and theres probably such a
simple answer to it and i just cant see it:

Riiiight, i have this form (lets call it frmClientSearch)

On this form i have a 2 list box thinghys; One called lstEmployee which is
populated with Employee names and the other lstCity which is populated with
City Names.

Does your table contain the actual employee and city NAMES? or does it contain
a foreign key to the Employee and/or City table?

Then i have a command button (cmdSearch) which, once click and values
selected in both the lst boxes, should return only the values that are equal
to both the lst boxes in a new form called frmSearchResult.

Care to post the code? We can't see it from here.

(Hope i make sense)

So... I know how to do this if i have only one list box. (The wizard does
the work) but i dont know how to do it with multiple "criteria" (for lack of
a better word) boxes.

PLEASE HELP!!!

Please help us to help you. We can't see your database, don't know the
structure of your table, don't know your fieldnames, and don't know the code
you're now using.

John W. Vinson [MVP]

 

[Guest]

Hey John!

Sorry bout being so vague. Thing is i've tried to do this before and had no
luck so it drove me insane and im in the process of building another db so i
thought i should ask before attempting hence there was no code to paste.

I've recreated the problem now but before i paste the code here's a little
context:

1 - I am a HUGE beginner so please excuse my ignorance
2 - I dont really write code. i simple run the wizards and as a last resort
change code where necessary but find that i dont really have to do this much
as the wizards erally cover most of what i want...

Riiiight, now that my confessions are out of the way. Here's the deal.

My tables are simple tables:

tblCity
tblEmployee
tblJob

The tblJob is the main table and it looks up the values of tblEmployee and
tblCity.

Then i have a form called frmClientSearch and on this form i have two list
boxes lstEmployee and lstCity which fetches its contents from their
respective tables as above.

Riiiight, so the command button (cmdSearch) on frmClientSearch was created
to open frmSearchResult with the selected values in the frmClientSearch.
This command button was simply created with the 'Open Form' wizard HOWEVER in
one of the steps in the wizard it asks you if you want to open the form and
display ALL results are only one corresponding with a value on the
existing/open form. I selected the latter and linked EmployeeName to
EmployeeName in their respective forms.

The problem is you cant select multiple values and link them to more than
one value on the form to be opened so thats where the problem lies. *gulp* (I
think)

Riiight, bearing in mind the wizrd did the work, here is the code:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchResult"

stLinkCriteria = "[JobEmployee]=" & Me![lstEmployee]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

Damn! Hope i answered all of your questions (i think i did) and again sorry
for being vague!

Many thanks!!!

Wayne

 

[John W. Vinson]

Hey John!

Sorry bout being so vague. Thing is i've tried to do this before and had no
luck so it drove me insane and im in the process of building another db so i
thought i should ask before attempting hence there was no code to paste.

I've recreated the problem now but before i paste the code here's a little
context:

1 - I am a HUGE beginner so please excuse my ignorance
2 - I dont really write code. i simple run the wizards and as a last resort
change code where necessary but find that i dont really have to do this much
as the wizards erally cover most of what i want...

Riiiight, now that my confessions are out of the way. Here's the deal.

My tables are simple tables:

tblCity
tblEmployee
tblJob

The tblJob is the main table and it looks up the values of tblEmployee and
tblCity.

That's part of the problem. Microsoft did a VERY BAD JOB when they provided
the Lookup field capability. See http://www.mvps.org/access/lookupfields.htm
for a critique.

The problem is that your Table APPEARS to contain the city name and the
employee name.... but it doesn't. What it contains, and what you have to
search for, is a concealed numeric ID number.

Then i have a form called frmClientSearch and on this form i have two list
boxes lstEmployee and lstCity which fetches its contents from their
respective tables as above.

This should be OK, if the Bound Column property of these listboxes is the
numeric ID.

Riiiight, so the command button (cmdSearch) on frmClientSearch was created
to open frmSearchResult with the selected values in the frmClientSearch.
This command button was simply created with the 'Open Form' wizard HOWEVER in
one of the steps in the wizard it asks you if you want to open the form and
display ALL results are only one corresponding with a value on the
existing/open form. I selected the latter and linked EmployeeName to
EmployeeName in their respective forms.

The problem is you cant select multiple values and link them to more than
one value on the form to be opened so thats where the problem lies. *gulp* (I
think)

well, multiselect opens a whole different can of worms, and the form wizard is
NOT capable of dealing with it!

Damn! Hope i answered all of your questions (i think i did) and again sorry
for being vague!

This was much clearer, thank you. What you'll need will be some considerably
more elaborate code looping through the listbox's ItemsSelected collection to
construct a query containing all of the selected employee ID's. Air code,
untested:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim iPos As Integer

stDocName = "frmSearchResult"

stLinkCriteria = "[JobEmployee] IN ("
With Me![lstEmployee]
For iPos = 0 to .ItemsSelected.Count
stLinkCriteria = stLinkCriteria & .ItemsSelected(iPos) & ", "
Next iPos
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 2) & ")"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub


John W. Vinson [MVP]