Re: Multiple fields using to search

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

On Tue, 10 Jul 2007 12:20:01 -0700, ant1983
<ant1983@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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]

.

Quantcast