Full Version: Using one form user selection to search mulitple criteria in a r
UtterAccess Forums > Microsoft® Access > Access Forms
Imalozer
I have a form that has one drop down box that the user can select one item. I need to make a button that can open up the Report based on this selection. The catch is that when searching for the users input it needs to search through two criteria in the report and return any matching items. I have used this basic code so far for just one criteria, but I'm unsure what to do next. Thank you very much for any help
CODE
  
Dim strReportName As String
Dim strCriteria As String
strReportName = "rptPrintRecord"
strCriteria = "[YourFieldName]='" & Me![YourComboName] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
Larry Larsen
Hi
Where would the second criteria come from..?
thumbup.gif
Imalozer
From the same user input...i.e their choice from the drop down box. O by the way thank you very much from your help on my last problem frown.gif
Edited by: Imalozer on Thu Sep 30 14:40:40 EDT 2004.
Larry Larsen
Hi
No problem.
Is it a situation were you have (2) combo's(drop down's) and need to search using both sets of criteria..?
thumbup.gif
Imalozer
One combo box. That needs to search through two sets of criteria.
Larry Larsen
Hi
Sorry but I'm still not understanding, one combo will give/return (1) value so you want to use this single value as criteria against (2) fields within a record or records..
thumbup.gif
Imalozer
Yup thats exactly what i want to do. But just one record/report needs to be searched.
Larry Larsen
Hi
OK..
Ostill have an issue regarding the use of a single value as criteria within a record, are you able to post up your db..?
thumbup.gif
Jack Cowley
Larry -
Is this be what he is after?
strCriteria = "[YourFieldName]='" & Me![YourComboName] & "' And [YourOtherFieldName] = '" & Me.[YourComboBoxName] & "'"
My two cents worth... Also it could be an OR rather than an AND...
Jack
Larry Larsen
Hi Jack
Thanks I was just trolling through some old code that used something very similar, still be interested in seeing the db..
I must say it's not often you have the same value in (2) different fields within a record..
thumbup.gif
Jack Cowley
Larry -
I was thinking the same thought about the same data in two fields... Could normalization be the next step?
Have fun!
Jack
Imalozer
That worked, Thank you VERY VERY VERY much.
Larry Larsen
It's only a matter of time till Imalozer enlightens us and proves us wrong..
thumbup.gif
Jack Cowley
Larry -
just got a response. My solution was what he was after... Please don't hate me... notworthy.gif
Jack
Jack Cowley
You are welcome, but a serious question is why do you have the same data in two fields in a record?
ack
Imalozer
well it involves a Electrical Power systems database. There are lines coming in and going out of a stations. The user input is the station. The code needed to go through and sort liines coming in and going out of the station. Thus the need for two criteria to search for.
Jack Cowley
If you have fields similar to this in a single record: Line1in, Line1out, Line2in, Line2out then you have repeating groups and your data is not normalized. In other words if you have a field in each record for each line coming in and each line going out then your table is set up incorrectly. What do you do if you need to add another line? You should have the lines in a related table, not in each record....
Just my two cents worth, but something you should give serious consideration to before you get too far along in your database design if indeed you have multiple line fields in your table...
Jack
Imalozer
duely noted thank you I'll look into it. (i'm not the one who created the tables, i'm just tweaking the DB)
Jack Cowley
You are welcome and good luck with the project!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.