Error trying to use form control value in query criteria

[JimBurke via AccessMonster.com]

I have been using form control values in my query criteria forever. All of
the sudden Access is giving me an error with some new queries I've created
and I can't for the life of me figure out why. I have one field that needs
criteria. If I hard code the actual value in the query it runs fine. If I go
into the query and execute it directly after I have the value in the form
control that works fine as well. When I run the query via VBA I get the error
'Invalid SQL Statement: Expected Delete, Insert...".

Here is my code:

dim cn As ADODB.Connection

Set cn = CurrentProject.Connection

Forms!frmPatientInfo!txtBillID = BillID
rstBillOrig.Open "qryGetOriginalBillValidation", cn, adOpenForwardOnly,
adLockOptimistic

If I step throught the code I know that the BillID value is assigned to the
form control value. In the query criteria I pasted that form control name -
Access adds the brackets, so in the criteria field in the query it has

[Forms]![frmPatientInfo]![txtBillID]

LIke I said, if I just hard code a value it works fine. When I run it with
the form control value when it has that exact same value I get the error. I
even took the SQL text (cut and pasted it from the query) and formatted it
and assigend it to a string variable, and it works fine then also. In that
case I end up with something like

SQLText = "SELECT field1 FROM table1 " & _
"WHERE field2 = " & [Forms]![frmPatientInfo]![txtBillID]
rstBillOrig.Open SQLText, cn, adOpenForwardOnly, adLockOptimistic

And that works! I've always used form controls in my query criteria and
cannot understand why this isn't working. I'm baffled. I have lots of other
existing querys that are used throughout the application that use form
control values for criteria and they are all still functioning fine.

 

[Banana]

If you happened to access a query with reference to a control on a form
while the form wasn't open, it may not work. It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

In that case, you may want to make it a parameter query:

For the query definition:

PARAMETERS <NameOfParameter> <DataType>;
INSERT INTO foo (bar) VALUES (<NameOfParameter>)


and this can be handled in VBA:

With CurrentDb.QueryDefs(<NameOfParameterQuery>)
.Parameters(<NameOfParameter>) = <Whatever you got the value from>
.Execute
End With


Of course, replace everything inside the <>s (and remove the <>s as
well) with actual names.

See how it gets you.

 

[JimBurke via AccessMonster.com]

I know the form is open and the control has a valid value in it - I step
through the code and the value is assigned right before I run the query. It's
a hidden field on my main menu, which never closes until appl is closed. When
I back out of the form where I'm running the query from, the value is in that
control - I set it to visible to make sure it was getting the value. I know I
have the correct control name - if I run the query manually using the form
control value it works fine. It's only when the query is run via VBA that it
doesn't work. Like I said, I have many functioning queries in the app that do
the same thing and they all work. I realize there are other ways to get it to
run, but I'd like to understand why these new queries suddenly aren't working
using this technique when they've always worked previously.

If you happened to access a query with reference to a control on a form
while the form wasn't open, it may not work. It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

In that case, you may want to make it a parameter query:

For the query definition:

PARAMETERS <NameOfParameter> <DataType>;
INSERT INTO foo (bar) VALUES (<NameOfParameter>)

and this can be handled in VBA:

With CurrentDb.QueryDefs(<NameOfParameterQuery>)
.Parameters(<NameOfParameter>) = <Whatever you got the value from>
.Execute
End With

Of course, replace everything inside the <>s (and remove the <>s as
well) with actual names.

See how it gets you.

I have been using form control values in my query criteria forever. All of
the sudden Access is giving me an error with some new queries I've created

[quoted text clipped - 34 lines]

existing querys that are used throughout the application that use form
control values for criteria and they are all still functioning fine.

 

[Banana]

Jim-

As I explained earlier:

It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

When you do something in the form or via Access UI, Access does lot of
things behind the stages for you such as calling Jet Expression Service
to evaluate the reference. But when you use DAO, you bypass all of that
and thus the burden of evaluating the parameter is now on your shoulder.
Hence, the need to do a parameter query as I showed earlier.

I know the form is open and the control has a valid value in it - I step
through the code and the value is assigned right before I run the query. It's
a hidden field on my main menu, which never closes until appl is closed. When
I back out of the form where I'm running the query from, the value is in that
control - I set it to visible to make sure it was getting the value. I know I
have the correct control name - if I run the query manually using the form
control value it works fine. It's only when the query is run via VBA that it
doesn't work. Like I said, I have many functioning queries in the app that do
the same thing and they all work. I realize there are other ways to get it to
run, but I'd like to understand why these new queries suddenly aren't working
using this technique when they've always worked previously.

If you happened to access a query with reference to a control on a form
while the form wasn't open, it may not work. It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

In that case, you may want to make it a parameter query:

For the query definition:

PARAMETERS <NameOfParameter> <DataType>;
INSERT INTO foo (bar) VALUES (<NameOfParameter>)

and this can be handled in VBA:

With CurrentDb.QueryDefs(<NameOfParameterQuery>)
.Parameters(<NameOfParameter>) = <Whatever you got the value from>
.Execute
End With

Of course, replace everything inside the <>s (and remove the <>s as
well) with actual names.

See how it gets you.

I have been using form control values in my query criteria forever. All of
the sudden Access is giving me an error with some new queries I've created

[quoted text clipped - 34 lines]

existing querys that are used throughout the application that use form
control values for criteria and they are all still functioning fine.

 

[JimBurke via AccessMonster.com]

I'm not using DAO and CurentDb.Execute, I'm using ADO, opening an ADO
recordset using recordset.Open:

Like I said, I've been using this technique for years, never had a problem
before. There must be some reason why it's not working for these new queries
when it's worked every time before. Like I mentioned, a fair percentage of my
queries in my appl use form control values for criteria, and they all are
still working fine.

Jim-

As I explained earlier:

It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

When you do something in the form or via Access UI, Access does lot of
things behind the stages for you such as calling Jet Expression Service
to evaluate the reference. But when you use DAO, you bypass all of that
and thus the burden of evaluating the parameter is now on your shoulder.
Hence, the need to do a parameter query as I showed earlier.

I know the form is open and the control has a valid value in it - I step
through the code and the value is assigned right before I run the query. It's

[quoted text clipped - 39 lines]

 

[Banana]

I see.

In your opening post, you said you had queries with form criteria.
Anything you create via Access is going to be a DAO object. To execute
queries or use recordset in ADO would have to be done in VBA and never
via the Access UI because it's DAO by default (as well as the layer
where Jet Expression Service and Access UI interact to do some of work
for you behind the curtains in resolving the parameters among other
things).

I just tested with an ADO recordset using a form control reference and
as expected, got an error. You certainly can use Parameters or
concatenate the variables into the Source string, but you cant refer to
the form controls directly as you could in the saved queries created via
Access UI.

If I'm missing something or did not understand you, maybe it may help us
to focus the discussion by supplying a snippet of the code that you have
used as well as the ones that has worked for you. That would help us
provide more direct response than guessing at what could be the problem.

I'm not using DAO and CurentDb.Execute, I'm using ADO, opening an ADO
recordset using recordset.Open:

Like I said, I've been using this technique for years, never had a problem
before. There must be some reason why it's not working for these new queries
when it's worked every time before. Like I mentioned, a fair percentage of my
queries in my appl use form control values for criteria, and they all are
still working fine.

Jim-

As I explained earlier:

It's also true if you try
to run the query from DAO using say, CurrentDb.Execute because parameter
hasn't been evaluated. IINM, this remains the case even if the form is
opened because it's basically a separate instance and still hasn't been
evaluated.

When you do something in the form or via Access UI, Access does lot of
things behind the stages for you such as calling Jet Expression Service
to evaluate the reference. But when you use DAO, you bypass all of that
and thus the burden of evaluating the parameter is now on your shoulder.
Hence, the need to do a parameter query as I showed earlier.

I know the form is open and the control has a valid value in it - I step
through the code and the value is assigned right before I run the query. It's

[quoted text clipped - 39 lines]

existing querys that are used throughout the application that use form
control values for criteria and they are all still functioning fine.