pass parameter docmd.openquery

S

souris

I would like to use docmd.openquery to open a query with parameter to pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris
 
F

fredg

I would like to use docmd.openquery to open a query with parameter to pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris
Click to expand...

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.
 
S

souris

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,
 
F

fredg

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,
Click to expand...

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.
 
S

souris

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,
 
F

fredg

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

fredg said:
But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.
Click to expand...
Click to expand...

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
S

souris

I do not know why I can not make it work.
I use docmd.runSqL instead.
It works for me.

I appreciated your help

Souirs,


fredg said:
SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

fredg said:
On Sat, 19 Mar 2005 18:17:28 -0500, souris wrote:

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

On Sat, 19 Mar 2005 12:47:33 -0500, souris wrote:

I would like to use docmd.openquery to open a query with parameter to
pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.



--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.
Click to expand...
Click to expand...

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Click to expand...
 
S

souris

Thanks for the information,
It works,
Thanks millions,


Souris,


fredg said:
SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

fredg said:
On Sat, 19 Mar 2005 18:17:28 -0500, souris wrote:

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

On Sat, 19 Mar 2005 12:47:33 -0500, souris wrote:

I would like to use docmd.openquery to open a query with parameter to
pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.



--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.
Click to expand...
Click to expand...

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Click to expand...
 
Top