please help me i'm sure this is not 1

[virginie]

please help me i'm sure this is not complicate..)
i have a form that permits me to select the good option to filter the data.
i have a frame with some options
for each frame options at the end i have a query that give me the filter

when i want to enter the form with the filtered data, i click a buton :
****************************
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Select Case framFilter
Case 1
'select * from orders,order_details ;
Case 2
'select * from orders,order_details where orders.id_order = "id text from the form";
Case 3
'select * from orders,order_details where orders.supplier = "supplier text from the form";
End Select

Dim stDocName As String
Dim stLinkCriteria As String
'open this form with the data from the framFilter
'''''''''''''''''''''''''''''''''''''''''''''''

stDocName = "see_order"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.description
Resume Exit_Command17_Click

End Sub
**************************
i know what are all my queries

the single problem is how to send to the form "see_order " that i want to open, the data relevent after the filter??

please help me
i am really stuck and i'm sure this is not hard thing!!
i can send the code if someone accept to help me...

 

[ChrisCalvert]

I would write three queries as you have above.
Then set the ME.Recordsource to the appropriate query.
Make sure to use the fields from the form like this '[Forms]![see_order]![field]. I am sure there are other ways but this would be an easy one. Make sure that all the queries have the same fields.


-chris

 

[JeremyNYC]

You can do it with the method you're using, but you're code has some errors...
Select Case framFilter
Case 1
stLinkCriteria = ""
Case 2
stLinkCriteria = "where orders.id_order = '" me!txtWhatever & "'"
Case 3
stLinkCriteria = "orders.supplier = '" & me!txtWhatever & "'"
End Select

You should only be passing the WHERE clause, without the word "where". You'll also have to put in the single quotes to offset strings. If the values you are getting from the form are numeric, you can do away with the single quotes. If they're dates, use # instead.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ChrisCalvert]

The only reason I chose the other method was I didn't realize that all the selects were *.

Jeremy:
If you needed to pass SELECT information, do you think the way I suggested would be best? This is simillar to something I am doing is why I ask.

-chris

 

[JeremyNYC]

If you are going to use the whole sql statement, you can do it either way: by create three stored queries and chosing which to make the recordsource or by creating three sql statements in your code and making one of them your recordsource. Stored queries, as long as they've been opened once already, run a little bit faster than sql statements, as the Jet database engine has already made a "plan" for how to run the query most efficiently. SQL statements from code have to be evaluated every time they are run. On the other hand, you ahve more flexibility with the stroed sql statements, and you get to see what you're doing right there in the code.

Also, if you're using the whole sql statement, you can't pass it in the WHERE clause parameter of the openForm command, you have to do it after you open the form:
call DoCmd.OpenForm (stDocName)
forms(stDocName).recordsource = [either the name of your query or your sql statement]

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[KenReay]

Hi

You could use OpenArgs property to pass the SQL string to the other form, and in the on open event of teh receiving form, use the passed string as the recordsource, see example I have sent you.

All are variations on a theme, tehre are several ways to achieve same result. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK