Use adp form control as criteria for sql function

  • Thread starter Thread starter Ernie
  • Start date Start date
E

Ernie

In Access.mdb, I use Forms!FormName!ControlName in the Query criteria
field to filter a query. I need to do the same thing with an
Access.adp front end and a SQL in-line Function. Currently, I am
using two LIKE @'s in the function criteria fields to produce parameter
query prompts for my user. The problem is I don't know how to populate
the parameter query boxes with a drop down list for my user to be able
to select from a list. Also, the user needs to be able to select ALL
records. With the LIKE @, he enters a % sign as a wildcard filter.

Any idea's on how to create a drop down list in the parameter query
box? Better yet, if I can create a form where I can place both of my
drop down boxes to gather all of the criteria and pass it to the sql
function criteria to filter my data.

Form control's Warehouse, PartNumber

Need to pass what is selected in the Form control's Warehouse and
PartNumber fields to the criteria section of the function fields.

Function fields: WHSE, PN

Thanks.
 
Sorry but from your post, I cannot tell what you are able to do from what
you're not.

Is your problem how to populate a combobox or how to have an Union query or
how to have more than one column by combobox or how to use the LIKE function
with SQL-Server?

What you mean by « parameter query box » ?

If you're totally new to ADP, maybe the best thing to do would be to take a
look at the previous posts in this newsgroup.
 
I'm not totally new to ADP. I have written several front-ends. In
response to your questions: The LIKE function that I am using in my
in-line function allows me to 'prompt' the user for criteria AND they
can use the % wildcard character to view ALL records. Currently, my
user must KNOW what to enter into the parameter query prompt. Since
there are up to 10 different types of criteria, it makes more sense
when the prompt appears, to allow the user to select one of th 10 items
in a drop-down list. In .mdb, this is easy: Create a user input form
and then place Form!FormName!FormField in the criteria of the .mdb
Query. Using .adp, it doesn't work this way. All that I need to do is
be able to populate the in-line function prompt box (appears when a @
or LIKE @ is used in the in-line function criteria for the field) with
a drop down list.
 
In ADP, there are two ways of doing this. The first one is simply to build
an sql string that will make a Select or an EXEC a stored procedure with the
required parameters and set the RecordSource of the form to this value;
something like:

Dim sql as string
sql = "Select * from MyTable Where IdField = " & Me.FormField
Me.RecordSource = sql

or:
Dim sql as string
sql = "EXEC MyStoredProcdure " & Me.FormField
Me.RecordSource = sql

You don't have to make a requery after setting the record source. For the
comboboxes, you do exactly the same excerpt that the name of the property is
RowSource instead of RecordSource.

The second method is to use the InputParameters property. This method is
best used when the record source is a stored procedure. It can also be used
for controls like the ComboBoxes but then, all parameters used for the
combobox must also be used in the stored procedure of the main form.

Exemple:

1- set the record source to the name of the stored procedure.
2- set the record source qualifier to the name of the owner; usually « dbo »
(Otherwise, you might have trouble).
3- set the InputParameters to the list of parameters, exemple:

@IdField int = Forms!MyForm!FormField

And presto!

It's also a good idea to set the UniqueTable and the ResyncCommand to their
proper values if you want to have an editable form (not mandatory but will
help). A frequent problem is to have an read-only form; often, this problem
will be solved by setting these two properties. See previous posts in this
newsgroup for more info on that subject.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I'm not totally new to ADP. I have written several front-ends. In
response to your questions: The LIKE function that I am using in my
in-line function allows me to 'prompt' the user for criteria AND they
can use the % wildcard character to view ALL records. Currently, my
user must KNOW what to enter into the parameter query prompt. Since
there are up to 10 different types of criteria, it makes more sense
when the prompt appears, to allow the user to select one of th 10 items
in a drop-down list. In .mdb, this is easy: Create a user input form
and then place Form!FormName!FormField in the criteria of the .mdb
Query. Using .adp, it doesn't work this way. All that I need to do is
be able to populate the in-line function prompt box (appears when a @
or LIKE @ is used in the in-line function criteria for the field) with
a drop down list.
 
I have an easier way:

1- set the record source to the name of the stored procedure.
2- set the record source qualifier to the name of the owner; usually «
dbo »
(Otherwise, you might have trouble).
3- make a field named IdField that can you set to a VBA function and
presto chango it automatically resolves the stored procedures'
parameter.

-Aaron
 
I'd say there are three ways of doing this. The third method applies when using a stored procedure as the record source:

Set the form's record source to the name of your parameter-taking stored procedure , and place controls on the form whose name is
identical to the name of the stored procedure's parameters. ADP will 'figure out' the parameter binding for you.

This ADP Magic is very simple but requires syncronizing your naming convention between the MS SQL parameter names on the back end
and your control names on the front end, which may result in your abandoning any preexisting naming conventions you had. However,
if you go this route, you might consider adopting a new naming convention just for stored procs that serve as record sources for
your forms... i.e. for starters you might prefix the name of the proc with something like "p_adpfrm_" to help you remember that 1)
it is a stored proc (the "p_") and it serves as the record source for an ADP form (the "adpfrm""). Then, just for adpfrm SPs, you
might name parameters after the frontend control (i.e. cboWareHouse).

Employing this approach is a slight nod in the direction of "convention over configuration" .

Regards,

Malcolm
 
I personally would rather get ass raped by a donkey than ever use an
underscore in any field name.

I mean... do you guys know it's a wildcard character?

ROFL
 
Back
Top