MS Access project with SQL Server

Author

Input Parameters

Robert Morley

2005-10-27, 8:32 am

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

2005-10-27, 8:32 am

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

2005-10-27, 8:32 am

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

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:eMVJXMN1FHA.3924@TK2MSFTNGP14.phx.gbl...
>
> 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
>
>

Sylvain Lafontaine

2005-10-27, 8:32 am

Personally, I use the syntax « Forms!Name_of_your_F
orm!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

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:uP9uDSN1FHA.1564@tk2msftngp13.phx.gbl...
>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

2005-10-27, 8:32 am

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

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OHn94pY1FHA.2132@TK2MSFTNGP15.phx.gbl...
> Personally, I use the syntax « Forms!Name_of_your_F
orm!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
>
>
> "Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
> news:uP9uDSN1FHA.1564@tk2msftngp13.phx.gbl...
>
>

Robert Morley

2005-10-27, 8:32 am

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" <JClay@NoSpam.nospam> wrote in message
news:uPRgIdY1FHA.1256@TK2MSFTNGP09.phx.gbl...
>
> Set up your record source as a stored procedure with a parameter. This is
> where the input parameter will be used.

J. Clay

2005-10-27, 8:32 am

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

Jim

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:ORkVkSd1FHA.3188@TK2MSFTNGP14.phx.gbl...
>
> 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" <JClay@NoSpam.nospam> wrote in message
> news:uPRgIdY1FHA.1256@TK2MSFTNGP09.phx.gbl...
>
>
>

Sylvain Lafontaine

2005-10-27, 8:32 am

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

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

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:ORkVkSd1FHA.3188@TK2MSFTNGP14.phx.gbl...
> 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" <JClay@NoSpam.nospam> wrote in message
> news:uPRgIdY1FHA.1256@TK2MSFTNGP09.phx.gbl...
>
>

aaron.kempf@gmail.com

2005-10-27, 8:32 am

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

2005-10-27, 8:32 am

> 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

Sponsored Links