Passing a Parameter

[Lynn]

OK, I have to say I am not having fun today with passing
parameters and stored procs as record sources.

I have a search form (frmSearch) with a combo box that the
user picks a value from. The combo box is called
cboProjectNumber.

Once the user picks a value another form called
frmBudgetProject opens in which the recordsource is set
through code to a stored procedure. The reason the record
source is set via code is because depending where the form
is opened from determines the record source.

Everything works separately but not together. In other
words, the stored proc works. FrmSearch and
frmBudgetProject work but not together. After I choose a
value from the combo box I get a window asking for the
input parameter. If I manually type in the value the form
opens correctly.

However, I can't get the value from the search form passed
to the stored proc on frmBudgetProject.

Here is what I have so far.

on the on click event on the search form:

stDocName = "frmBudgetProject"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "Edit"

On the open and load events on frmBudgetProject:

If Me.OpenArgs = "Edit" Then
Me.RecordSource = "dbo.prB_frm_BudgetProject_Edit"
Me.InputParameters = "@ID = Forms!frmSearch!
cboProjectNumber"
End If

Thanks

 

[Andy Williams]

Lynn,

If Me.OpenArgs = "Edit" Then
Me.RecordSource = "dbo.prB_frm_BudgetProject_Edit"
Me.InputParameters = "@ID = Forms!frmSearch!
cboProjectNumber"
End If

I'm suspicious of the whole OpenArgs thing. Are you sure that Me.OpenArgs =
"Edit" is evaluating to true? Throw in some breakpoints and check that out.
I wonder if your form's recordset is already set to
prB_frm_BudgetProject_Edit and the If returns false. That might be why the
form is prompting you for the parameter.

-Andy

 

[Andrew Schonberger]

you need a Question Mark as a placeholder for the
parameter. Something like:

Me.RecordSource = "dbo.prB_frm_BudgetProject_Edit ?"
Me.InputParameters = "@ID = Forms!frmSearch!

I assume your record source is a Stored Proc which takes a
parameter named @ID.

 

[Vadim Rapp]

L> OK, I have to say I am not having fun today with
L> passing parameters and stored procs as record
L> sources.

drop us a line when you resolve this one as well ;-)

 

[Lynn]

I tried the place holder and that didn't work.

I ended up using a filter:

ElseIf Me.OpenArgs = "Edit" Then
Me.RecordSource = "dbo.prB_frm_BudgetProject"
Me.Filter = "BudgetProjectID=" & Forms!
frmSearchProject!cboProjectNumber
Me.FilterOn = True

Thanks for the suggestions though!!

 

[Jerry Boone]

I covered something similar to this in another post, it was aimed at reports
but works exactly the same for a form... I do this all of the time. Nothing
special required for your recordsource -- just use the dbo. prefix to avoid
having naming problems later.

http://groups.google.com/[email protected]&rnum=1

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

 

[Karen Vaudreuil]

It seems you can't change the InputParameters in the code on Form_Open/Load
and I think you can't put a reference to an object on another form either.

I made an application in which a form can be open from different forms and
that determines the recordsource. Since I couldn't change the
InputParameters, I made only one stored proc which has the logic in it to
determine the record source (mostly, it was always the same SELECT, only the
WHERE and ORDER BY changed). For the InputParameters, I put, for example,
@NoProd char(5) = GetNoProd(FrmSource). The function GetNoProd(FrmSource)
then finds the NoProd depending on the calling form (FrmSource).

Public Function GetNoProd(FrmSource As String) As String ' The function is
in a module
Select Case FrmSource
Case "frm_QteRequisesMP"
GetNoProd = Nz(Forms(FrmSource).Controls("txtNoPF"), "")
Case "frm_ProduitsFinis"
GetNoProd = Nz(Forms(FrmSource).Controls("txtNoProd"), "")
Else
GetNoProd = "" ' SQL Server interpretes it as a NULL value
and I can directly open the form without causing an error.
End Select
End Function

And I used OpenArgs to send which form is calling...

Karen

 

[Lyle Fairfield]

It seems you can't change the InputParameters in the code on
Form_Open/Load and I think you can't put a reference to an object on
another form either.

Wrong
and
Wrong.

....

Private Sub lblABD_MouseDown(Button As Integer, Shift As Integer, X As
Single, y As Single)
Me.InputParameters = "@SchoolID=" & CStr
(Form_frmSchoolTeachers.txtSchoolID) & ", @Like='" & Chr(64 - Int(-X /
Me.lblABD.Width * 13)) & "'"
End Sub

Private Sub lblNOP_MouseDown(Button As Integer, Shift As Integer, X As
Single, y As Single)
Me.InputParameters = "@SchoolID=" & CStr
(Form_frmSchoolTeachers.txtSchoolID) & ", @Like='" & Chr(64 - Int(-X /
Me.lblABD.Width * 13) + 13) & "'"
End Sub

Private Sub lblShowAll_MouseDown(Button As Integer, Shift As Integer, X As
Single, y As Single)
Me.InputParameters = "@SchoolID=" & CStr
(Form_frmSchoolTeachers.txtSchoolID) & ", @Like=NULL"
End Sub