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]

 

[marydn]

Thanks Jimmy! It worked beautifully! You are the Best!!!!!

 

[marydn]

Back to this code. I ran into some problems with my report. My user has two fields they can select from. One field gives them 2 choices and the other field gives them 4 choices. For example:

Select Fruit:
Apple
Oranges

Select Fruit Type:
With seeds
seedless
Large
Small


What I did was cretae a table based on the fruit selection then a second table based on the fruit type selection (quereid off the fruit selection table). Once the selections are made the user can then select the individual fruit he wants to view from a second form based on the last table created. The only problem is that I have many people using the same dataabse and the table gets locked so only one person can use the table at one time. Is there any easy way out of this? I tried querying the form based directly on the values selected i.e.) forms!frm_GetFruit!txtFruit. However, I can only query for one fruit, not multiple.

This is making me wacko! Any help is appreciated.

 

[marydn]

I get an error when I use

DoCmd.OpenForm "YourFormName"
Forms!YourFormName.RecordSource = strSQL

It says it cannot be used as a recordsource

 

[jimmythegeek]

I assume you are changing the names where necessary. For example if the name of your form is frmOrders, then your code would look like this:

DoCmd.OpenForm "frmOrders"
Forms!frmOrders.RecordSource = strSQL

If you have set the strSQL string = to a valid string, then the recordsource of the form can be set to it.



Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[marydn]

Okay - I got rid of the error. The form actually opens now, but there is no data in the combo box.

Thanks.

 

[marydn]

Okay This is what I did:

DoCmd.OpenForm "frm_DetailedSiteInfo2"
Forms!frm_DetailedSiteInfo2.RecordSource = strSQL3
Forms!frm_DetailedSiteInfo2!cboSite.rowsource = strSQL3

I notice that the form must open first before being able to assign the recordsource. My combo box even got populated. However, when I tried it again my form opens up, but the combo Box disappears. Can anyone tell me why?
Thanks!

 

[jimmythegeek]

Why would you be setting the rowsource of a combo box to the same as the recordsource of the form (strSQL3)?

One alternative is to create a global string variable. In a module put the following in the General Declarations section:

Public gstrSQL As String

Now in the OnOpen event of your form, put the following:

Me.cboSite.rowsource = gstrSQL

So long as you have set gstrSQL = to a valid rowsource for a combo box, it will set it for you when you open the form. This is just one other way to skin a cat.



Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[marydn]

Jimmy,

I don't know why I did that, other than I am getting desperate for a solution. I tried your code, but it doesn't work for me. What happens is that I set the sql statement in the main form, based on user selections, then I set the command to open the second form. The form opens and when on open it goes to to cboSite.rowsource = gstrSQL. However, the string is null. I didn't forget to put the global in a module. What do you suppose I am doing wrong? It's driving me nuts!!!!

Thanks,

M.

 

[jimmythegeek]

Post the code that sets the strSQL varable. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[marydn]

Here is a sample of the SQL string:

SELECT Sitename,[SBA Site Code],address1, PropertyId, sitestanding, propstatus, sitetype FROM dbo_DS_DetailedSiteInfo WHERE SiteStanding = ((SiteStanding) ='Owned')AND SiteType = (SiteType) ='Acquired' ORDER BY SiteName;

 

[marydn]

Okay - I fixed the problem with the SQL string, and tested it in a query. However, the cboSite still doesn't populate.

Thanks