Multiple fields using to search

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!!!



0
Utf
7/10/2007 7:20:01 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1406 Views

Similar Articles

[PageSpeed] 22

On Tue, 10 Jul 2007 12:20:01 -0700, ant1983
<ant1983@discussions.microsoft.com> 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]
0
John
7/10/2007 8:00:27 PM
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@discussions.microsoft.com> 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]
> 
0
Utf
7/11/2007 9:04:03 AM
On Wed, 11 Jul 2007 02:04:03 -0700, ant1983
<ant1983@discussions.microsoft.com> wrote:

>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]
0
John
7/11/2007 11:33:47 PM
Reply:

Similar Artilces:

IE8 address box steals focus from search box
Hi Running latest IE8 I keep finding that when I start IE8, type an expression in the search box in the top right corner, the address box steals focus So if I type "SQL Server 2008 Client installation", the letters "SQL S" are in the search box and the rest of the letters "erver 2008 Client installation" are now in the address box. This is deeply annoying. Anyone seen this and know how it can be fixed? It only seems to do this at IE8 startup or new tab startup. After that, all words are in the search box and the address box dioes not steal focus....

Postal Bar Coding & Using Outlook Address Book in Publisher
I would like to add bar coding to my envelopes but can't seem to find any help in Publisher for this feature. I'd also like to find a way to use my Outlook Address book in Publisher - can anyone help me. Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Sharon Fermer <anonymous@discussions.microsoft.com>... > I would like to add bar coding to my envelopes but can't seem to find > any help in Publisher for this feature. Read www.mvps.org/the_nerd/Publisher/FAQs.htm > I'd also like to find a way > to use my Outlook Address bo...

How do I print multiple records to one publication page?
I want to merge records from a data source in access to publisher. I want 4 different records per page in postcard format. Each time I use the catalog merge feature it prints 1 record 4 times on the page. How can I fix this? You need to have one card on your screen for the merge to work. (Print preview will show the same entry on all four cards, its a bug). -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jagodie" <Jagodie@discussions.microsoft.com> wrote in message news:CF2C96BA-6A33-4AF0-9703-A692713A5EA5@micro...

#VALUE! when using defined name
HELP!!! Problem: receive #VALUE! Using a defined NAME range In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209 1209Sch contains all monthly entries, 1209 is the summary sheet. Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values: {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager L88 is a SUM function but I have remove L88 from range and still have problem Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error B4:B9 are empty Can copy and then paste 1209Sch L8...

Using Powerpoint 2007
PPS files on webpage open as PPT using PowerPoint 2007. Don't have this same issue when opening the same link from an Office 2003 computer, they run the show only. -- Miroshak ...

Changing a custom field length
We have created a custom section and there is a field (in that custom section) whose length has to be changed from 100 to 500 of nvarchar datatype, but the interface does not allow more than 100 for nvarchar. I couldnt modify the format of textbox to textarea as it is disabled nor i can change the datatype to another datatype. can anybody please help me with this?? Affy Unfortunately you'll have to recreate the field, as you can neither increse the length of a text field nor change it's format after it's been created. Then there's a question of what to do with any existing...

Use of Option Buttons
I have a main form where I display a record and associated fields. One of the fields is called 'source'. Currently, the source is linked to a one-to-one source table. No problem with this setup. However, I want to be able to assign and display multiple 'sources' for each main record. This ends up being a many-to-many relationship and, therefore, I have created the following tables (not all fields are listed). Program Table Prog_ID Program_Name Link Table Prog_ID Source_ID Source Table Source_ID Source_Name I want to display on the main form 'buttons' indicating ...

Multiple Series Pie Charts
Is there a way to display multiple series pie charts in excel? Was thinking the Pie Charts could be displayed next to one another. Thanks, Chad Chad - Make a pie chart, not as a separate chart sheet, but located on a worksheet (a blank one, if you like). Make another and position it on the worksheet next to the first. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Chad BATES wrote: > Is there a way to display multiple series pie charts in > excel? Was thinking the Pie Charts could be displayed > next to...

How to use OpenArgs
Hi all, a pop-up form is opened from a subform with this code: Private Sub Form_Open(Cancel As Integer) Dim strCustomerName As String Dim strBuildingNo As Long If IsNull(OpenArgs) = False Then Dim arrArgs() As String arrArgs = Split(Me.OpenArgs, "~") strCustomerName = arrArgs(0) strBuildingNo = arrArgs(1) End If End Sub It is working good and my array, arrArgs, is populating strCustomerName and strBuildingNo perfectly. If a new record is needed I use the following: Private Sub Form_BeforeUpdate(Cancel As Integer) '...

Field lookup query question
Access 2003. I have two tables. Master and IssuePart. Each table has a report number (Master.OA) that ties all this together. I wanted to make the Master table perform a lookup by matching the report number in IssuePart and bringup a listbox. But the query won't cooperate. I can manually enter a report number and get the results I want, but as soon as I try to change the criteria to the Master report number field name, it chokes with syntaxs errors and such. Here's the SQL the Builder produces. SELECT IssueData.[Part Number], IssueData.[Serial Number] FROM IssueData WHERE ((ISS...

Using (1), (2), (3), etc. in Risks and Issues Category values?
Do most of you use numbers, like (1), (2), (3), to define Category values for Risks and Issues to keep it "the same" as the out-of-the- box values for Status? Just curious. Thanks Andy Novak UNT Andy, I have used both with numbers and without for projects, based on the clients maturity. I would suggest if you want to make it as easy as possible to differentiate between the different risk and issues categories, put the number in, it makes it much easier to see. Alex. On Feb 20, 8:48=A0am, "ano...@unt.edu" <ano...@unt.edu> wrote: > Do most of y...

Multiple chioce in drop-down list
Hello, everyone I have to create a field on the form with a drop-down list where user can select more than one value. It seems like standard insruments (creating attribute with picklist type) are not working cause it's not allowed to choose more than one value. If you have ideas about that, please assist -- ---------------------- regards, Ilya Milshtein MBS Master, MBSCP, MCP Softline International www.softline.ru As you noticed, multi-value picklists are not an option in crm 3.0. What you can do is create a new custom entity instead of a picklist. So if your picklist was for "...

Using eseutil.exe
What is the best way to determine, if my store need to be defragged with eseutil. We have been running W2K3 Exch for about 7 months, we have removed a lot of information from the store (by forcing users to clean up their sent items and deleted items folders) yet the store remains so large the backup will not fit on 1 40GB tape. Is this an indication that I should use the utility? If the online defragmentation runs as part of the online maintenance that should run every evening then you may be able to tell from this, have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

How to read B-LOB data from Oracel DB using Pro*c
Hello, I'm trying to read B-LOB data in Oracle Database using Pro*c. I know how to read it with char mode. What I want to know is read it with binary mode. Are there any good person help me? Let me know if you have a good sample source. Thanks and have a nice day. ...

CHM Help using MFC and VC6
I'm not certain I'm in the right place, so please direct me if there is another forum where this question would be more appropriate. Two questions: 1) Migrating an existing application from using WinHelp to using HTML Help. 2) Building a new application to use HTML Help, using VS6 and MFC. Everything I've found in the MSDN so far is suggesting that building an application in VS6 to use HTML Help is a brutal and bloody process. The preferred approach seems to be use VS.Net. This doesn't make sense, since HTML Help existed long before VS.Net. I can't imagine ...

how to use Field codes in Excel
Hi, I am facing a problem in using custom property values in excel worksheet. Here is my requirement. I want to display the Version No in the footer. how to do the same using custom properties. I have used field codes in Word but could not find the same in excel. Can you pls help ASAP "Radhika" wrote... >I am facing a problem in using custom property values in >excel worksheet. Here is my requirement. I want to >display the Version No in the footer. how to do the same >using custom properties. I have used field codes in Word >but could not find the s...

How to install software on a terminal server/use microsoft office through RDP
My boss is asking me to figure this out, so I do not know all the configurations he is using. I can tell you its Windows server 2k3. He wants to be able to RDP to server, and work physically on the server. He also needs to have office 2003 license installed. So the greater question is "how to use ms office 2k3 through RDP on microsoft 2003 server?" -- jgosney ------------------------------------------------------------------------ jgosney's Profile: http://forums.techarena.in/members/159717.htm View this thread: http://forums.techarena.in/windows-server-help/1277...

how do i format a multiple page brochure?
i am trying to print a multiple page brochure and want to know how to format the final document so the pages come out in the correct order. critic <critic@discussions.microsoft.com> was very recently heard to utter: > i am trying to print a multiple page brochure and want to know how to > format the final document so the pages come out in the correct order. You already posted this question once. It depends entirely on how you want the finished publication to look. And on your version of Publisher Your best bet is to use File > Page Setup > Booklet. -- Ed Bennett - MV...

Fix multiple 'assign macro' references broken due to moving sheet
I'm mystified as to why this happened, but I'm hoping someone can help me fix it in an easy way... I have a workbook with multiple sheets, and many buttons with assigned macros on different sheets for navigation, filtering, etc. I created a new blank sheet within the workbook for a throwaway calculation, and then decided I didn't want it in that book so right clicked and moved to a new book. That was fine and I proceeded with my calculating for a while. Then I went back to the original book and tried to use a button, but got an error that the macro did not exist, refe...

delete multiple range names
I have a workbook with multiple sheets. The workbook was built by someone else and has multiple range names that are no longer needed. How can I delete all range names to start fresh? Hi, In excel 2007 , go to Formulas, Name manager, choose the name to delet and click delete "mnsue" wrote: > I have a workbook with multiple sheets. The workbook was built by someone > else and has multiple range names that are no longer needed. How can I > delete all range names to start fresh? The following macro will delete all range names in the active workbook S...

Sys table Query to Obtain List of Queries used in Reports
Is it possible to query the Access 2007 Sys tables to obtain a list of which queries are used in which reports? Thanks, Brad If you've named your queries according to their functionality it is easy. To get a list of queries from the system table use: SELECT Name, DateCreate, DateUpdate FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=5)); The quewry has no idea where it will be used so there isn't any way for it to give you that information. The recordsource is in code or it's name is stored with the form or repo...

How can I know the folders of searched out mails?
Hello, If I search for mails - particularly those already in Archive folders, I get a list of such mails and can of cours open them etc., but I do not see any way to establish in which folder they are actually stored. Harry120 You'll need to use google or windows desktop search (or other search tool) - outlook won't give you folder paths. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net ...

Help writing formula... multiple nested if/thens
Hi all, I am trying to create a spreadsheet that calculates allowable (pro-rated) car rental expenses that employees can claim for a conference that was held. The organization has agreed to pay employee car rental expenses that fall between the dates of the conference 7th Sept 2008 and 14th October 2008 (including both of those days). However, some car rental contracts fall partially in this range, (as some employees showed up early or stayed late for vacation), and employees are reimbursed accordingly. I am trying to create a spreadsheet that gives the number of allowable days (inside the ran...

Decrypting RSA encrypted symmetric key using crypto API
Hi, I'm trying to decrypt a 3DES symmetric key wich was encrypted by a RSA public key using the PKCS#1 padding. I'm using the CryptDecrypt function which should support (MSDN) the decryption of RSA encrypted keys. But when I call this function all I get is an unsupported function error. I'm using Windows XP with service pack 2. It would be greate if someone can give me an example for this or an example using the CryptImportKey function. Thanks in advance Martin Howdy, Forgive me being vague but it was a year ago I worked with this stuff and I don't have my references he...