form-based parameters in a query (access 2003 - ADP)

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I have done some Access work in the past, but keeping an Access
Back-end. I recently had to up-size to SQL for an ADP file without much
research or preparation. I've been able to pass parameters to queries
and reports from a form by using =[forms]![frm_Name]![region] and the
like. But since converting to ADP format, I've not been able to figure
out the new thought process.

I've spent an hour in Google and in help and have dound some cool
things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan
 
Unfortunately, there isn't really an equivalent. The reason, of course, is
that all queries are run on the server side, which is unaware of any "forms".

Probably the three most common solutions are:

1. Re-write your query/view as a stored procedure with parameters.
Personally, I ran into some problems with this method early on and have
avoided ever since, so I can only tell you that the method exists, but
little else.

2. Use dynamically generated SQL. Create the SQL text and plop in the
value of Region just before you execute it.

3. Transfer the value of Region to a table in the database so that the
necessary value is now server-side. With that there, you can now run the
entire query on the server without a hitch.



Rob
 
So running with the #3 option, How would I insert the value if queries
are unaware of forms?

I may have described my current scenario incorrectly. I ran the
upsizeing wizard and all the tables now live in Access, but queries
forms & reports live in the Access Project

Thank you



Robert said:
Unfortunately, there isn't really an equivalent. The reason, of course,
is that all queries are run on the server side, which is unaware of any
"forms".

Probably the three most common solutions are:

1. Re-write your query/view as a stored procedure with parameters.
Personally, I ran into some problems with this method early on and have
avoided ever since, so I can only tell you that the method exists, but
little else.

2. Use dynamically generated SQL. Create the SQL text and plop in the
value of Region just before you execute it.

3. Transfer the value of Region to a table in the database so that the
necessary value is now server-side. With that there, you can now run
the entire query on the server without a hitch.



Rob
I have done some Access work in the past, but keeping an Access
Back-end. I recently had to up-size to SQL for an ADP file without
much research or preparation. I've been able to pass parameters to
queries and reports from a form by using =[forms]![frm_Name]![region]
and the like. But since converting to ADP format, I've not been able
to figure out the new thought process.

I've spent an hour in Google and in help and have dound some cool
things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan
Click to expand...
Click to expand...
 
What are you going to do with the query?

Use it as the Record Source for a form or report? Then, investigate the InputParameters Property for forms/reports - "You can use
the InputParameters property to specify or determine the input parameters that are passed to a SQL statement in the RecordSource
property of a form or report or a stored procedure when used as the record source within a Microsoft Access project (.adp).
Read/write String."

Also, you should know that if you use a parameterized stored procedure as the record source for a subform of form myMasterForm, that
if any of the form controls "happen" to be named identically to any of the SPs parameters, the value of the form variable will be
used "magically". This might take care of some of your use cases...???

--Malcolm Cook
 
If you go with #3, then whenever you change the value of the combo box in
the form (or at least whenever you close the form...whichever makes sense in
your scenario), you would need to change the value in a table as well. So,
assuming it's not already bound to a field in your database, you'd have code
like the following behind the combo box or form:

CurrentProject.Execute "UPDATE MyTable SET MyField = " & Me!region.Value

Of course, if Region is a string, you'll have to enclose it in single quotes
first.

Oh and if you're using an ADP, your saved queries are on the server as well
(as either Views or Stored Procedures), not just the tables.

Malcolm has a point as well...I'd forgotten about that functionality as I'm
suspicious by nature and don't trust the magic. ;)


Rob
So running with the #3 option, How would I insert the value if queries
are unaware of forms?

I may have described my current scenario incorrectly. I ran the
upsizeing wizard and all the tables now live in Access, but queries
forms & reports live in the Access Project

Thank you



Robert said:
Unfortunately, there isn't really an equivalent. The reason, of
course, is that all queries are run on the server side, which is
unaware of any "forms".

Probably the three most common solutions are:

1. Re-write your query/view as a stored procedure with parameters.
Personally, I ran into some problems with this method early on and
have avoided ever since, so I can only tell you that the method
exists, but little else.

2. Use dynamically generated SQL. Create the SQL text and plop in
the value of Region just before you execute it.

3. Transfer the value of Region to a table in the database so that
the necessary value is now server-side. With that there, you can now
run the entire query on the server without a hitch.



Rob
I have done some Access work in the past, but keeping an Access
Back-end. I recently had to up-size to SQL for an ADP file without
much research or preparation. I've been able to pass parameters to
queries and reports from a form by using =[forms]![frm_Name]![region]
and the like. But since converting to ADP format, I've not been able
to figure out the new thought process.

I've spent an hour in Google and in help and have dound some cool
things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan
Click to expand...
Click to expand...
Click to expand...
 
I am going through a migration from Access mdb to SQL also, and a lot of my
queries had the type of form-based criteria that you mention. One solution I
have used is to remove all references to form-based criteria in the SQL Views
(you may have to re-write all the Access queries as Views), and specify the
criteria in the VBA statement that is executed when the On Click event of a
control button is initiated by the user. The event runs the Open Form
instruction, and the form is based on the view-- apparently, you can't
specify criteria using Open View-- correct me if I'm wrong, everyone-- but
with Open Form you can, so I just make a simple datasheet form based on the
view.

SO... if you have an Access query that used to refer to a field on a form
(which SQL doesn't know exists) such as your [frms]![frm_Name]![region], in
the VBA procedure that calls the new form you would say something like:

stLinkCriteria = "[Region]=" & "'" & Me![region] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

On a RELATED note, I am having trouble with one of my queries, though. In
Access it reads a name on a form, and when the user hits a control button,
the query runs and shows all names that begin with the same 3 letters as the
name on the form. IN Access,, the Last Name field has this criteria:

Like Left([Forms]![csrF]![IntReqLN],3) & "*"

I can get the corresponding SQL view to open with an exact match of the last
names like this:

stLinkCriteria = "[Lname]=" & "'" & Me![Last Name] & "'"

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

But I haven't been able to figure out how to get the match of just the first
three letters to work.. the "Like Left" part. Can anyone help? Thanks!



What are you going to do with the query?

Use it as the Record Source for a form or report? Then, investigate the InputParameters Property for forms/reports - "You can use
the InputParameters property to specify or determine the input parameters that are passed to a SQL statement in the RecordSource
property of a form or report or a stored procedure when used as the record source within a Microsoft Access project (.adp).
Read/write String."

Also, you should know that if you use a parameterized stored procedure as the record source for a subform of form myMasterForm, that
if any of the form controls "happen" to be named identically to any of the SPs parameters, the value of the form variable will be
used "magically". This might take care of some of your use cases...???

--Malcolm Cook

Alan said:
I have done some Access work in the past, but keeping an Access Back-end. I recently had to up-size to SQL for an ADP file without
much research or preparation. I've been able to pass parameters to queries and reports from a form by using
=[forms]![frm_Name]![region] and the like. But since converting to ADP format, I've not been able to figure out the new thought
process.

I've spent an hour in Google and in help and have dound some cool things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan
Click to expand...
Click to expand...
 
el said:
I am going through a migration from Access mdb to SQL also, and a lot of my
queries had the type of form-based criteria that you mention. One solution I
have used is to remove all references to form-based criteria in the SQL Views
(you may have to re-write all the Access queries as Views), and specify the
criteria in the VBA statement that is executed when the On Click event of a
control button is initiated by the user. The event runs the Open Form
instruction, and the form is based on the view-- apparently, you can't
specify criteria using Open View-- correct me if I'm wrong, everyone-- but
with Open Form you can, so I just make a simple datasheet form based on the
view.

SO... if you have an Access query that used to refer to a field on a form
(which SQL doesn't know exists) such as your [frms]![frm_Name]![region], in
the VBA procedure that calls the new form you would say something like:

stLinkCriteria = "[Region]=" & "'" & Me![region] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

On a RELATED note, I am having trouble with one of my queries, though. In
Access it reads a name on a form, and when the user hits a control button,
the query runs and shows all names that begin with the same 3 letters as the
name on the form. IN Access,, the Last Name field has this criteria:

Like Left([Forms]![csrF]![IntReqLN],3) & "*"

I can get the corresponding SQL view to open with an exact match of the last
names like this:

stLinkCriteria = "[Lname]=" & "'" & Me![Last Name] & "'"

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

But I haven't been able to figure out how to get the match of just the first
three letters to work.. the "Like Left" part. Can anyone help? Thanks!



What are you going to do with the query?

Use it as the Record Source for a form or report? Then, investigate the InputParameters Property for forms/reports - "You can use
the InputParameters property to specify or determine the input parameters that are passed to a SQL statement in the RecordSource
property of a form or report or a stored procedure when used as the record source within a Microsoft Access project (.adp).
Read/write String."

Also, you should know that if you use a parameterized stored procedure as the record source for a subform of form myMasterForm, that
if any of the form controls "happen" to be named identically to any of the SPs parameters, the value of the form variable will be
used "magically". This might take care of some of your use cases...???

--Malcolm Cook

Alan said:
I have done some Access work in the past, but keeping an Access Back-end. I recently had to up-size to SQL for an ADP file without
much research or preparation. I've been able to pass parameters to queries and reports from a form by using
=[forms]![frm_Name]![region] and the like. But since converting to ADP format, I've not been able to figure out the new thought
process.

I've spent an hour in Google and in help and have dound some cool things, but none relevant to this situation.

Thoughts or links?

Many thanks
~alan
Click to expand...
Click to expand...
Click to expand...
ok - I got what you're saying. I'll give this a go over the next few
days and report back. Sadly this is for work, but don't have time AT
work to work on it...
 
Say you have a start date and end date for your report. On report property sheet 'input parameters' you would put:

@Enter_beginning_date = Forms![ReportDateSelection]![txtStartDate],@Enter_ending_date = Forms![ReportDateSelection]![txtEndDate]

On query attached to report you would put:

SELECT (some table felds), @Enter_beginning_date AS startdate, @Enter_ending_date AS enddate
FROM (some tables)
WHERE (some table fields) BETWEEN
@Enter_beginning_date AND @Enter_ending_date)
 
Problems with Access 2003 ADP

hello everyone,

I have completed my vacation database with an Access 2003 front-end and SQL 2000 back-end. I now have to set up the Access front-end on multiple users desktops. I have connected ODBC and SQL is connecting without a hitch. The problem is on my forms that run stored procedures. Anyone heard of this...the stored procedure works fine in the queries tab, however when being run from a form it does not find the stored procedure.

Any help would be greatly appreciated.

Lucinda
 
Back
Top