Parameters in a QueryDef

[rockman]

Question about parameters in a querydef

Pat Hartman has stated that it is more efficient to use a querydef than dynamic SQL. I've read over his post of HowJetHandlesQueriesAgainstODBCDatasources.doc, but I have questions.

I have a form "frmCustomer" and I have a querydef "qryCustomer" based on my table "tblCustomers". On my form I have a textbox (txtCurrentCustomerID) that will contain the record that I wish to view.

If I place criteria within my qryCustomer to the effect: fldCustomerID = forms!frmCustomer!txtCurrentCustomerID, I get prompted to manually enter a value for txtCurrentCustomerID (assumedly because the control doesn't exist yet in the early sequence of opening the form).

I guess it boils down to, "How do I pass a parameter to the querydef to return only the single record I wish to view while the form is opening?"

Does my question make sense?

Thanks for any help,
Jeff

 

[Kevin_S]

pass the parameter to the form before it opens. For example: If you have a form that lists customers you could select a customer from the list and then open the customer detail form to show the deatil for that single record, which is based on the query. I frequently use a listbox control to display lists and then use the "double-click" event on the listbox to open the next form...

HTH,
Kevin

 

[rockman]

Kevin-

Thanks for the reply. Its obvious that you are accomplishing exactly what I would like to achieve. I don't quite understand your statement "pass the parameter to the form before it opens".

I was able to place the CustomerID number in a textbox on my "Customer Select" form, and then reference that textbox in the criteria of the query that is used as source for the "frmCustomer". This works, but seems a bit too "round-about" than is needed.

Can you explain your method a little more in detail, or is it possible for me to designate a global variable that I can use in the querydef?

Thanks,
Jeff

 

[Kevin_S]

rockman - I wasn't able to get back with you the last few days as I have been away from the office... Are you still having problems with this? If so let me know and I'll help you set it up...

Kev

 

[rockman]

I found a round-about way to accomplish this, but it sounds like your way is more straight forward.

Please expound upon "pass the parameter to the form before it opens".

Thanks

 

[Kevin_S]

rockman - I whiped up a demo of the following and attached it to this post (Note* - its in A 2002 format - if you need A97 format see the post after this one). The demo expands on the idea of passing a parameter to a query to return a single record. Basically what happends is that the user selects a record in the listbox and then double-clicks it to open the detail form for that individual record. Behind the seens the form (frmCustomer) is based on a query (qryCustomer). The query (qryCustomer) has a statement in the criteria under the PK CustomerID that filters the return to the selected record in the listbox control. The statement is as follows:

Forms!TheNameOfTheForm!TheNameOfTheControlOnTheForm - or - in the case of the mock-up its: Forms!frmSearchCustomer!List3

Have a look at the examples and if you have any more questions let me know...

HTH,
kev

 

[Kevin_S]

Same example above only in A97 format:

 

[rockman]

Perfect Kevin!

Your way is much more elegant than what I was able to come up with.

Thanks a million,
Jeff

 

[Kevin_S]

No problem Jeff... Glad I could help!

Good Luck and post back if you need more help,
Kevin

 

[JiTS]

Well, maybe you can help me...

I have two forms (frmInput and frmControl).

Description:
frmInput = Here you enter the order information
frmControl = Here you enter more detail information about the order

In both forms is 'OrderID' the primary key.

If frmInput is filled in and you click on the button 'Next', the form frmControl will be opened.

In frmControl are a couple of fields the same as in frmInput but not everything.

Normally you create a query with a parameter (i.e. Give OrderID?). When you click on the button a dialog shows up and you enter the OrderID... then the form opens with all the data of OrderID.

Question:
If frmControl opens, how do I get the information automaticly in stead of giving manual a parameter input?

 

[JiTS]

Pat,

Thanks for your reply but I still have one question:

Where do I have to put the following code:

Where OrderID = Forms!frmControl!txtOrderID

Can you give me more specific details please?

My own solution:
I guess it has to be in the query which I use for the form frmControl. Am I right?