Input Parameters

[Robert Morley]

Hi all,

I'm just trying to work with Input Parameters for the first time (I normally
just construct a recordsource & requery, but thought I'd try it this way for
a change), and I'm having a bit of trouble figuring out why the following
doesn't work:

Record Source: SELECT RespondentName FROM tblRespondents WHERE RespondentID
= ?
Input Parameters: ? smallint = [Form]![cmbRespondent]

I'm using an Access XP ADP on the front end and SQL Server 2000 on the back
end...where am I going wrong?

I know I could change it to an actual Stored Procedure, or simply reset the
recordsource programmatically whenever cmbRespondent changes, but as far as
I know, the above should work as is, shouldn't it?



Thanks,
Rob

 

[Robert Morley]

I should have mentioned, the problem with this is that it's always prompting
me to input [Form]![cmbRespondent] instead of taking it from the combo box
on the form.


Thanks again,
Rob

 

[J. Clay]

Set up your record source as a stored procedure with a parameter. This is
where the input parameter will be used.

 

[Sylvain Lafontaine]

Personally, I use the syntax « Forms!Name_of_your_Form!cmbRespondent », with
or without the [], instead of « [Form]![cmbRespondent] ».

I have great trouble with the latest form in VBA code (but no with macros
and strings directly assigned to an event).

Also, I don't use the syntax « ? » for the record source of a form, only
named parameters for stored procedures.

Finally, when you assign a new record source to an object, you don't have to
make an explicit call to the Requery function.

 

[Robert Morley]

I'd forgotten whether or not you needed to requery, but beyond that, most of
what you've said is the way I normally do things...other than the fact that
I'm not fond of using Stored Procedures to return recordsets, since that's
nominally what views are supposed to do...though of course, there are times
when an SP is the way to go.

But this time around, I decided to try Input Parameters in a
SELECT-statement recordset, and apparently nothing worked as expected.
Eventually I went back to the way I'm used to. I see other responses to my
post, though, so hopefully one of them will address my problem so I can
learn how to use that method properly.



Thanks,
Rob

Personally, I use the syntax « Forms!Name_of_your_Form!cmbRespondent »,
with or without the [], instead of « [Form]![cmbRespondent] ».

I have great trouble with the latest form in VBA code (but no with macros
and strings directly assigned to an event).

Also, I don't use the syntax « ? » for the record source of a form, only
named parameters for stored procedures.

Finally, when you assign a new record source to an object, you don't have
to make an explicit call to the Requery function.

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

I should have mentioned, the problem with this is that it's always
prompting me to input [Form]![cmbRespondent] instead of taking it from the
combo box on the form.


Thanks again,
Rob

 

[Robert Morley]

I'm aware that I can do it that way. The Access documentation, however,
says that I can do it using a SELECT statement as the recordsource, with ?'s
for parameters. Is it wrong?


Rob

 

[J. Clay]

I don't know. I almost always used stored procedures for my record source
of forms.

Jim

 

[Sylvain Lafontaine]

Does the Access documentation says that you can do this without being
prompted?

 

[aaron.kempf]

yeah i dont use inputParameters; i just bind a form to a sproc

and then when the sproc is looking for a parameter named @txtPLU just
have a textbox named txtPLU with that value.

If you need; you can set the controlsource for the textbox equal to a
vb function.

I beleive that this is a lot easier than any of this other BS. Of
course; it's basically undocumented... from what i've seen.

-Aaron

 

[Robert Morley]

Does the Access documentation says that you can do this without being

prompted?

Yes.

You can find two different examples in the help:
- under the "InputParameters Property" documentation, "Remarks" section,
"When used with a RecordSource property:"
- under the "Create and use a parameter query", "Create and run a
parameter query in the SQL SELECT statement of a form or report".

You can easily pull up both of these titles by entering "recordsource input
parameter" in the Answer Wizard box.

Oh, and thank you to everyone for all the OTHER ways of doing it, but that
wasn't the point here...the point was to figure out how to use THIS way of
doing it.



Thanks,
Rob

 

[aaron.kempf]

'i dont like your way of doing it, it's not as powerful and it takes a
lot more work'.