Access 2003 ADP/SQL Server 2005 - parameterised row sources do not work

[bcap]

I'm using the well-established technique of parameterising combo box row
sources using an inline function or an sproc which has a parameter name
equivalent to the name of a form control.

What I've found in an Access 2003/SQL Server 2005 environment is that this
will only work if I'm logged on as a sysadmin. With any other user, I get
the following:

With an sproc:

"The record source <sproc name> specified on this form or report does not
exist"

With an inline function:

"Parameters were not supplied for the function <function name>"

I've tried, without success:

- giving the user *every* permission to the sproc, the function, and the
underlying tables
- qualifying the sproc or procedure name with the schema name.

The only thing that works is to log off, and log back on as a sysadmin.
This is a non-starter for a production system, I can't make every user a
sysadmin!

I realise that I can programmatically assign a query to the row source in,
e.g., the OnCurrent event, but this doesn't help where the combo box is on a
continuous form and the bound column is not visible.

Help anyone? Or is this just another nail in the coffin of ADP's?

 

[Tore]

You can try to prefix your stored prodedure names with dbo
(dbo.storedprocedurename).

Tore

 

[Tore]

This is how I set rowsource for a couple of comboboxes using a stored
procedure (MSP_.....) with parameters. It is quite dirty code as i refer to
comboboxes on some other form (Menu), but it works. I have turned more to
using ADO which I find more robust, although it requires a bit more of coding.


Me.cboAccount1No.RowSource = "exec MSP_SELECTACCOUNTNUMBERS " &
Forms!Menu!cboCompany & ", " & Forms!Menu!cboFiscalYear

Me.cboAccount1Name.RowSource = "exec MSP_SelectAccountNames " &
Forms!Menu!cboCompany & ", " & Forms!Menu!cboFiscalYear

Tore

 

[bcap]

Unfortunately, as I said, I already tried prefixing the SP name with the
schema name.

As I also said, using code to assign a rowsource to a combo box doesn't work
when the combo is on a continuous form, and the visible column is not the
bound column. What happens is that the current record shows the correct
value in the combo box, and all other records show blank. In this
circumstance the row source needs to be parameterised.

 

[Paul Shapiro]

I think the problem is the continuous form, not SQL Server. Access doesn't
support different rowsources for the combo boxes in different rows of a
continous form. I have mitigated this a bit by using an inclusive rowsource
that contains the data applicable to all rows. This works great for
displaying the data. When a user starts to edit a row, or drop-down the
combo box, you can change the rowsource to just show the applicable data
they should be able to select. When they save the row, restore the inclusive
rowsource so all the rows show data. It's a pain, and difficult to make it
100% robust, but can be made to work well enough.

 

[a a r o n _ k e m p f]

I've not seen this issue. I almost only programmatically assign a
query to the row source
(or of course, I keep my variables in a table, called SysAppSettings--
with an identifier of @@SPID)

I'd call up Microsoft and ask them with Office 2003 Sp4 is going to be
released.. and if they give you any lip; ask them why 'SQL 2000' and
'Access 2000' didn't work together when they shipped.

-Aaron