Referencing forms in the Criteria box of a query on .adp

[Guest]

What is the syntax for referencing a control on a form in
the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for adp. I
really need to get it to work.

 

[Tom Ellison]

Dear anonymous:

There is no direct equivalent method. Use dynamic queries, where you
build the SQL text in code, or use Stored Procedure or User Defined
Function parameters. Again you must code to pass the parameters.

This is a significant part of the task of moving to ADPs. It will be
slightly painful at first, much easier after doing it a few times.
Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thanks Tom
The reason I was trying to do it is that I am using the
rst.Find method in code and it is very slow. Any ideas? I
have indexed the field that I am searching.

-----Original Message-----
Dear anonymous:

There is no direct equivalent method. Use dynamic queries, where you
build the SQL text in code, or use Stored Procedure or User Defined
Function parameters. Again you must code to pass the parameters.

This is a significant part of the task of moving to ADPs. It will be
slightly painful at first, much easier after doing it a few times.
Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

What is the syntax for referencing a control on a form in
the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for adp. I
really need to get it to work.

.

 

[Tom Ellison]

Dear Anon:

Same answer. Use dynamic SQL or SP/UDF with parameters. We've
experienced that recordsets are slow, as you have. They are also
limited to just one criteria. You can add AND or OR and another
criterion like you could in an MDB. Just too restrictive as well.
So, we just don't use them. There are alternatives that don't have
these drawbacks.

Usually, its dyanamic SQL we prefer. It gets pretty fast to code
after a bit of practice.

Writing ADPs required us to experiment with various techniques. We
have found some that work well. Using those, we have been able to
build whatever we wish. This is a very empirical process. We're very
happy with ADPs the way we use them. I'm trying to pass on the
results of some of that experience.

Thanks Tom
The reason I was trying to do it is that I am using the
rst.Find method in code and it is very slow. Any ideas? I
have indexed the field that I am searching.

-----Original Message-----
Dear anonymous:

There is no direct equivalent method. Use dynamic queries, where you
build the SQL text in code, or use Stored Procedure or User Defined
Function parameters. Again you must code to pass the parameters.

This is a significant part of the task of moving to ADPs. It will be
slightly painful at first, much easier after doing it a few times.
Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

What is the syntax for referencing a control on a form in
the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for adp. I
really need to get it to work.

.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Can you post me an example of either the dynamic SQL or
the SP. I am new to these.
Thanks

-----Original Message-----
Dear Anon:

Same answer. Use dynamic SQL or SP/UDF with parameters. We've
experienced that recordsets are slow, as you have. They are also
limited to just one criteria. You can add AND or OR and another
criterion like you could in an MDB. Just too restrictive as well.
So, we just don't use them. There are alternatives that don't have
these drawbacks.

Usually, its dyanamic SQL we prefer. It gets pretty fast to code
after a bit of practice.

Writing ADPs required us to experiment with various techniques. We
have found some that work well. Using those, we have been able to
build whatever we wish. This is a very empirical process. We're very
happy with ADPs the way we use them. I'm trying to pass on the
results of some of that experience.

Thanks Tom
The reason I was trying to do it is that I am using the
rst.Find method in code and it is very slow. Any ideas? I
have indexed the field that I am searching.

-----Original Message-----
Dear anonymous:

There is no direct equivalent method. Use dynamic queries, where you
build the SQL text in code, or use Stored Procedure or User Defined
Function parameters. Again you must code to pass the parameters.

This is a significant part of the task of moving to ADPs. It will be
slightly painful at first, much easier after doing it

a
few times.

Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Fri, 17 Sep 2004 01:42:52 -0700,

What is the syntax for referencing a control on a

form
in

the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for

adp.
I

really need to get it to work.

.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.

 

[Guest]

Tom
This is the code I have been using.

Set rstOps = New ADODB.Recordset
strsql1 = "SQLACCESS.tblctopl"

'strsql1 = "Select * from SQLACCESS.tblctopl "
'& " WHERE est_id = " & Me![txtEstid]
rstOps.Open strsql1, cnn, adOpenStatic, adLockReadOnly,
adCmdTable


rstOps.Find "est_id=" & myestid
Me.Bookmark = rstOps.Bookmark
rstOps.Filter = "est_id=" & myestid

It is the Find that is slow.

-----Original Message-----
Dear Anon:

Same answer. Use dynamic SQL or SP/UDF with parameters. We've
experienced that recordsets are slow, as you have. They are also
limited to just one criteria. You can add AND or OR and another
criterion like you could in an MDB. Just too restrictive as well.
So, we just don't use them. There are alternatives that don't have
these drawbacks.

Usually, its dyanamic SQL we prefer. It gets pretty fast to code
after a bit of practice.

Writing ADPs required us to experiment with various techniques. We
have found some that work well. Using those, we have been able to
build whatever we wish. This is a very empirical process. We're very
happy with ADPs the way we use them. I'm trying to pass on the
results of some of that experience.

Thanks Tom
The reason I was trying to do it is that I am using the
rst.Find method in code and it is very slow. Any ideas? I
have indexed the field that I am searching.

-----Original Message-----
Dear anonymous:

There is no direct equivalent method. Use dynamic queries, where you
build the SQL text in code, or use Stored Procedure or User Defined
Function parameters. Again you must code to pass the parameters.

This is a significant part of the task of moving to ADPs. It will be
slightly painful at first, much easier after doing it

a
few times.

Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Fri, 17 Sep 2004 01:42:52 -0700,

What is the syntax for referencing a control on a

form
in

the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for

adp.
I

really need to get it to work.

.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.

 

[Tom Ellison]

This is fairly short on details. Write a query that works for a
specific value of "estid" and make sure it works. Copy the SQL of
that into your code, replacing the value to which you want to filter.
Put a breakpoint in the code right after you have built that string
and put it in strsql1, and display what is in strsql1 in the immediate
pane. Paste that into a new query design and see that it works
properly. You can eliminate the Find and just use this new recordset.
I think you should get rid of the last line with the Filter as well.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom
This is the code I have been using.

Set rstOps = New ADODB.Recordset
strsql1 = "SQLACCESS.tblctopl"

'strsql1 = "Select * from SQLACCESS.tblctopl "
'& " WHERE est_id = " & Me![txtEstid]
rstOps.Open strsql1, cnn, adOpenStatic, adLockReadOnly,
adCmdTable


rstOps.Find "est_id=" & myestid
Me.Bookmark = rstOps.Bookmark
rstOps.Filter = "est_id=" & myestid

It is the Find that is slow.

-----Original Message-----
Dear Anon:

Same answer. Use dynamic SQL or SP/UDF with parameters. We've
experienced that recordsets are slow, as you have. They are also
limited to just one criteria. You can add AND or OR and another
criterion like you could in an MDB. Just too restrictive as well.
So, we just don't use them. There are alternatives that don't have
these drawbacks.

Usually, its dyanamic SQL we prefer. It gets pretty fast to code
after a bit of practice.

Writing ADPs required us to experiment with various techniques. We
have found some that work well. Using those, we have been able to
build whatever we wish. This is a very empirical process. We're very
happy with ADPs the way we use them. I'm trying to pass on the
results of some of that experience.

Thanks Tom
The reason I was trying to do it is that I am using the
rst.Find method in code and it is very slow. Any ideas? I
have indexed the field that I am searching.
-----Original Message-----
Dear anonymous:

There is no direct equivalent method. Use dynamic
queries, where you
build the SQL text in code, or use Stored Procedure or
User Defined
Function parameters. Again you must code to pass the
parameters.

This is a significant part of the task of moving to
ADPs. It will be
slightly painful at first, much easier after doing it a
few times.
Overall, it's not a big deal. It may seem so at first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Fri, 17 Sep 2004 01:42:52 -0700,

What is the syntax for referencing a control on a form
in
the criteria box of a query when using Access 2003
Projects(Adp). The syntax for MDB is forms![Mainform]
[subform]![controlname] but this doesn't work for adp.
I
really need to get it to work.

.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.