Change Rowsource

[Guest]

I have a form where there is a record selection combo box. Standard
FindFirst code. I have another check box to select a different subset of
records. Tick the box and you change me.rowsource to a different stored
procedure. So far so good. What I also want to do is to change the combo to
a different stored procedure as well.

My problem is that the new combo stored proc has a parameter which is the
CurrentUser - @UserID. I have a field on the form with a ControlSource
"=CurrentUser" so I can refer to the form field.

This is the stored procedure

ALTER PROCEDURE dbo.qryCmbProject_AllAccess
@UserID nvarchar(20)
AS SELECT DISTINCT dbo.tblProject.ProjectNo, dbo.tblProject.ProjectName,
dbo.tblProject.ProjectID, dbo.tblProjectAccess.UserName
FROM dbo.tblProjectAccess LEFT OUTER JOIN
dbo.tblProject ON dbo.tblProjectAccess.ProjectNo =
dbo.tblProject.ProjectNo
WHERE dbo.tblProjectAccess.UserName = @UserID
GO

Problem is I don't know how to pass the parameter to the query which is the
rowsource once the checkbox is ticked. Do I use VBA to create a parameter
when the combo got focus event occurs? Can I add it to the rowsource
property?

 

[Sylvain Lafontaine]

You can add a parameter to the rowsource by making a dynamic sql string and
using the EXEC statement to call the stored procedure:

ComboBox.RowSource = "exec qryCmbProject_AllAccess " & UserId

If UserId is of type string or date, then you must enclose it between single
quote:

ComboBox.RowSource = "exec qryCmbProject_AllAccess '" & UserId & "'"

and if it contains single quotes, you must first double them using the
Replace command:

ComboBox.RowSource = "exec qryCmbProject_AllAccess '" & Replace (UserId,
"'", "''") & "'"

The other way is to use the InputParameters property but as this property is
global to the form, all the parameters used for each control must also be
part of the list of parameters of the SP used as the record source of the
form.

A third possibility would be to use a control with the same name as the
parameter but personally, I never had any good chance using this one.
Finally, you should take a look at the previous posts on this newsgroup. On
Google:

http://groups.google.ca/group/microsoft.public.access.adp.sqlserver

 

[Vadim Rapp]

In addition to what Sylvain said - one more possibility: since your stored
procedure is in fact a single statement, you can safely discard stored
procedure and specify the statement directly as the rowsource:

Private Sub CurrentUser _AfterUpdate()
Combo0.RowSource = "SELECT DISTINCT ProjectNo, ProjectName, ProjectID,
UserName
FROM dbo.tblProjectAccess
LEFT OUTER JOIN dbo.tblProject ON dbo.tblProjectAccess.ProjectNo =
dbo.tblProject.ProjectNo
WHERE dbo.tblProjectAccess.UserName ='" CurrentUser & "'"
End Sub


Vadim Rapp

 

[Malcolm Cook]

FWIW,

I have used the third possibility on many occasions to good effect. SImply name the parameter to the stored proc and the control
identically. For instance, you migth name them both cboUserID. Though this may look unusally as the name of a param to a stored
proc, it will in fact be a reminder to you and any coder that comes later that the parameter value is intended to come from an
access control. Then, all you need to do is call .Requery after changing the rowsource.

A 4th possibility is to set the 2nd combo boxes .recordset property to one built dynamically, but I don't recommend this route.

 

[Jello World]

Malcolm;

I strongly, very strongly-- applaud your posting. We don't see many
people around here that say positive things about ADP.

Thanks so much; I personally appreciate it very very much.
It almost made me cry

-Charlie