Show results in a form-- from multiple combo boxes 2

[mdavis1]

i have 4 synchronized combo boxes.
one each for customer name, plant name, location, and unit
these are the four search criteria to find the information i want to find.
i have the combo boxes synchronized//////////
now i want to be able to select those four fields, then press a button to open my regular form that displays the filtered records.
any help would be GREATLY appreciated..and thanx in advance
monica
mdavis@ccidover.com X-)


[sig][/sig]

 

[jimmythegeek]

I am assuming that any of the fields may, or may not have a value. Let's say the user just wants a plant name and location. Or will all the fields be filled in?

On Click of your button put something like this (change the red accordingly):

===============
Public Sub whatever2()
Dim strSQL As String

strSQL = "SELECT * FROM YourTableName "
strSQL = strSQL & "WHERE("

If Not IsNull(Me.Customer) Then
strSQL = strSQL & "(([Customer Name]) = '" & Me.[Customer Name] & "') AND "
End If

If Not IsNull(Me.PlantName) Then
strSQL = strSQL & "(([Plant Name]) = '" & Me.[Plant Name] & "') AND "
End If

If Not IsNull(Me.Location) Then
strSQL = strSQL & "((Location) = '" & Me.Location & "') AND "
End If

If Not IsNull(Me.Unit) Then
strSQL = strSQL & "((Unit) = '" & Me.unit & "')"
End If

If Right(strSQL, 4) = "AND " Then
strSQL = Left(strSQL, Len(strSQL) - 5)
End If

strSQL = strSQL & &quot"
'strSQL = strSQL & " ORDER BY [Customer Name]" ' You can do an order by if you want
strSQL = strSQL & ";"

DoCmd.OpenForm "YourFormName"
Forms!YourFormName.RecordSource = strSQL
End Sub
=============== [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

ok...each combo box is linked to a query.
**field name**||* combo box name*** || **query name**
--------------||---------------------||------------------
Customer Name = cboSelectCustomerName = qryCustomerName
Plant Name = cboSelectPlantName = qryPlantName
Location = cboSelectLocation = qryLocation
Unit No = cboSelectUnitNo = qryUnitNo

each box must have a value selected.

these combo boxes are located on a form. (QryCustName)
which is based on a query. (reportquery)
the form i want to open (frmCustomerSurveySheet) is based on (reportquery) also.
i have a command button on there(frmCustomerSurveySheet), to print selected record to a report.

what would be a dream , is to put these combo boxes on that form..instead of using the apply filter (toolbar) method.

but i will settle for these combo boxes on a separate form, with a button that, once the values are chosen, would put the selected records in the form (frmCustomerSurveySheet)so i could print them out or whatever...have i lost you yet?
**********************************************************
***mr. lunde**
i have tried your code, and i keep getting errors on line:
///If Not IsNull(Me.Customer)Then\\keeps giving me &quot;method or data type not found&quot;
i've tried everything.
***********************************************************
i know this is going to be like a brick in the face when i realize how to do this....but i'm going bonkers...my database is all finished..except for this action/form
thank you very much for all your advice and help.
monica
mdavis@ccidover.com:-I


[sig][/sig]

 

[jimmythegeek]

If you want them on the same form, you could have one problem. When you open the form the first time, you will get the data displayed for the first record in the table. There is no easy way around this. Two options:

1) Show the combo boxes, and hide all the other fields. After the user select the values, and filters, requery the form, and show the fields.

2) Have a subform that is invisible. After the user select the values, and filters, requery the subform, and show it.

The other way is to have a seperate form for selecting criteria, that opens frmCustomerSurveySheet form to the specific record.

Which way to you want to go. We need to know before we post a big long response, and this is not what you want to do.

p.s. One reason you are getting errors in the code, is because you need to replace the red areas with the appropriate names of your combo boxes, and fields in your table,

For example:
Anywhere you see this: Me.Customer
It should be changed to this: Me.cboSelectCustomerName

And this: [Customer Name]
Should be changed to: [TheNameOfTheFieldInYourTable]

All the others should be changed appropriately. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

Great..thanx
ok..having them on a separate form that opens to frmCustomerSurveySheet is wonderful...
i want to give you a big thank you for helping me so much.
it's greatly appreciated
monica
mdavis@ccidover.com
[sig][/sig]

 

[mdavis1]

ok..i got the code to work, down to the last line
error 3131....an error in my FROM clause
****strSQL = &quot;SELECT * FROM YourTableName &quot;*****
i put reportquery as my table name......that's the table that the form is based on.....HELP%-(

[sig][/sig]

 

[jimmythegeek]

Do this:

Go to the first &quot;strSQL =&quot; line and put a break point (click on the gray vertical bar to the left of the code, it should turn the line dark red).

Close the form, open it, add the criteria, and hit the button. The code should be suspended (yellow) at the break point.

Now hit F8 to step through it. Stop after you pass the last line of the &quot;strSQL =&quot;

Now hit ctrl+G. This will bring up the debug window. Type this: ?strSQL and hit enter.

This should give the actual SQL statement. Copy and paste it here, so we can look at it. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

SELECT * FROM reportqueryWHERE((([Customer Name]) = &quot;&Me.[Customer Name]&&quotAND(([Plant Name]) = &quot; & Me.[PlantName] & &quot AND((Location)=&quot;&Me.Location&&quotAND((UnitNo)=&quot;&Me.UnitNo&&quot);

code as requested...
[sig][/sig]

 

[jimmythegeek]

This can't be the same code that is above.

There should be single quotes in 8 different places, and they are not there. Also, there needs to be spaces between some of the words.

Did you copy the code EXACTLY like it is written above, and only change the red words? [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

?strSQL
SELECT * FROM reportquery WHERE((([Customer Name]) = 'CONOCO DISOTELL') AND (([Plant Name]) = 'SOUTH CONGER') AND (([Location]) = 'STERLING CITY, TX') AND (([Unit No]) = 'UNKNOWN'));

ok..i copied straight from above and pasted. changed the info...ran the test again. above is what came back.

ok..i'm not getting an error message this time...but it's not pulling the files i've selected..it's just opening the form with all the records.
on the last few lines of code..where it says YourFormName.
i put the form i want to open in the openform line
and i put the form i was querying from (qryCustName)in the record source line.
correct? not? HELP... [sig][/sig]

 

[jimmythegeek]

The syntax looks correct. Both the &quot;YourFormName&quot; values should be the same form. For example:

DoCmd.OpenForm &quot;frmOrderEntry&quot;
Forms!frmOrderEntry.RecordSource = strSQL

Let me know if that doesn't work. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

after i choose a value from the combo boxes and click the open form button, it opens the form to a blank page.(the form shows up for just a split second..then blank...like a filter that returns nothing.)
do i need some kind of update procedure after the combo boxes and before you click the the open form button?
i'm just so bewildered by this one
thanks so much for time and patience...
icq#3219811
mdavis@ccidover.com

[sig][/sig]

 

[jimmythegeek]

Do the same thing you did to get the SQL string that you pasted here for us, but go to a new query, and paste it into the SQL view of the new query. Run it, and see if it returns any records. If it does, then there is a problem. If it doesn't, then you are not seeing anything on the form, because there are no records that meet all of the given criteria. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[mdavis1]

ok..here is my code::::::::::::
Dim strSQL As String

strSQL = &quot;SELECT * FROM reportquery &quot;
strSQL = strSQL & &quot;WHERE(&quot;

If Not IsNull(Me.cboCustomerNameSelect) Then
strSQL = strSQL & &quot;(([Customer Name]) = '&quot; & Me.[Customer Name] & &quot;') AND &quot;
End If

If Not IsNull(Me.cboPlantNameSelect) Then
strSQL = strSQL & &quot;(([Plant Name]) = '&quot; & Me.[Plant Name] & &quot;') AND &quot;
End If

If Not IsNull(Me.cboSelectLocation) Then
strSQL = strSQL & &quot;(([Location]) = '&quot; & Me.[Location] & &quot;') AND &quot;
End If

If Not IsNull(Me.cboSelectUnitNo) Then
strSQL = strSQL & &quot;(([Unit No]) = '&quot; & Me.[Unit No] & &quot;')&quot;
End If

If Right(strSQL, 4) = &quot;AND &quot; Then
strSQL = Left(strSQL, Len(strSQL) - 5)
End If

strSQL = strSQL & &quot&quot;
'strSQL = strSQL & &quot; ORDER BY [Customer Name]&quot; ' You can do an order by if you want
strSQL = strSQL & &quot;;&quot;

DoCmd.OpenForm &quot;frmCustomerSurveySheet&quot;
Forms!frmCustomerSurveySheet.RecordSource = strSQL
it is pulling up the first record in &quot;reportquery&quot; no matter what i search.
HELP [sig][/sig]

 

[mdavis1]

SELECT * FROM reportqueryWHERE((([Customer Name]) = 'ARCO PERMIAN') AND (([Plant Name]) = 'BLK 31') AND (([Location]) = 'CRANE, TX') AND (([Unit No]) = 'K-40F #2'));

This is the first record in my &quot;reportquery.&quot;
it IS NOT pulling up what i have selected in the combo boxes.......%-( [sig][/sig]

 

[jagilman]

I think this is it... your example

&quot;SELECT * FROM reportqueryWHERE&quot; notice there is no space between reportquery and WHERE ???

Just a thought, good luck.


[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[mdavis1]

<jumping up and down> I GOT IT ...I GOT IT....
***& Me.[Customer Name] &...i was not putting the name of the combo box here...(like i was told to do)
so many thanks to you MR.LUNDE[/COLOR RED]
YOU RULE DUDE.

[sig][/sig]

 

[jimmythegeek]

Don't hurt yourself jumping up and down.

Glad you got it to work. I have used that methods many times, and knew it would work if you had the syntax right. Good Job !! [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[marydn]

Jimmythegeek,

I used your code and it was what I had been looking for for weeks! You are a real life saver! One question I have, however, is how do I use the strSQL statement with a report instead of a form? I have a SQL statement, but don't know how to put the data in a report. I tried to insert the strsql data into a table that i could then utilize with the report, but it's not working. This is what I did

strsql = &quot;Select * from tbl_X&quot;
docmd.runsql strSQL & &quot;INTO tbl_Test&quot;

It's not working. Can you tell me the best way to go about this???

Thanks so much...............

 

[jimmythegeek]

One way is to create a string global variable.

In a module, put the following line:

Public gstrReportString As String

Now viewing the end of the code above, put the following:

=============================================
.......

strSQL = strSQL & &quot&quot;
strSQL = strSQL & &quot; ORDER BY [Customer Name]&quot;
strSQL = strSQL & &quot;;&quot;

gstrReportString = strSQL
DoCmd.OpenReport &quot;rptYourReportHere&quot;
=============================================

Now in the OnOpen Event of the report put the following line:

Me.RecordSource = gstrReportString

Note: To design the report it is easier to create a query that returns the exact same data as you want to show in the report. Make the recordsource of the report that query. Design the report as you want it to look, then delete the recordsource, and add the line in the OnOpen event.

I have used this before, I hope it helps. Jim Lunde
compugeeks@hotmail.com
We all agree that your theory is crazy, but is it crazy enough?