Parameter query as row source to a combo box

[Stuart]

Hello, I'm new to .ADP. I am able to write a parameter query as record souce
for a form. I have not had any luck so far however making it the row source
for a combo box. The parameter is a field on the form. When I fill in
"input parameters" the query works as a record source for a form. But it
prompts me no matter what I try when the query is row source for a combo box.
Can anyone help?

Thanks, Stuart

 

[Sylvain Lafontaine]

When using parameters, the rules are a little different between a record
source and a control. You can use the InputParameters property to transmit
a parameter to a query source of the form but you can do that with a control
only if the parameter(s) is itself used for the query source of the form.
Put in another way, the list of parameters of a control must be a subset of
the list of parameters used for the query source of the form.

If you can do that, then the obvious solution is simply to build a full sql
query string to be used as the row source for the control. If you want to
use a stored procedure, use the EXEC keyword:

MyComboBox.RowSource = "Select * From MyTable Where IdTable = " & MyId

or:
MyComboBox.RowSource = "EXEC MySP " & MyFirstArgument .....

You don't have to use the refresh command after changing the rowsource of a
control and don't forget to put any alphanumeric value between single
quotes.

 

[Stuart]

Thanks, this is very helpful. Can you explain a bit more about the stored
procedure? I am pretty familiar with the logic in your first example where
you append the field to the query in code. In fact, that's how I've been
doing it to avoid this parameterization issue.

A related question, perhaps best illustrated by an example. Suppose I have
three tables:

tblProjects: ProjectID, ProjectName
tblStaff: StaffID, StaffName
tblAssignments: ProjectID, StaffID

I would like the user to enter a staffID, and then have the form display all
the projects to which this staff member is NOT assigned. I know how to write
such a query in the jet world (I would do a query of a query). But the
parameterization issue seems to muddy the whole thing up. Any suggestions?

And a final question, can you suggest an on-line resource for help with
Access/SQL? I haven't been able to find anything.

Thanks, Stuart

 

[Sylvain Lafontaine]

For the question about stored procedures (SP), I was under the impression
that you already knew about it because you were able to make a parameter
query. There are two ways of doing a parameter query - one by using a SP
and the second by using the placement holder @[Forms]![MyForms]![MyControl]
(and ? in the InputParameters) but the second one is rarely used in an ADP
project.

The preferred method is to use a SP because this is the most powerful way of
extracting a resultset (recordset) from a SQL-Server. If you want to know
about SP, you should consult a good book on SQL-Server and T-SQL (the
language used on SQL-Server).

For your second question, I don't know what you mean exactly with the
expression « a query of a query » but the answer to your question would
probably be to use the NOT EXISTS or the NOT IN statements. A Left Outer
Join could also be used but this is a little more complicated to understand.
Grosso modo, it should be the same on SQL-Server than with JET. I don't see
why the parameterization issue here would or could muddy the whole thing up.

Finally, to my knowledge, there is no on-line resource help for issues about
Access and SQL-Server other than using Google or the newsgroups.