Parameters

[Craig]

I have a form whose whose RecordSource property is.....

SELECT tblProfile.ProfileID
FROM tblProfile
WHERE (((tblProfile.ProfileID)=[pProfileID]));

I will open this form from many places in the application, so how do I
supply the value for the parameter programmatically?

Use the InputParameters property? If so how?

Isn't there some event I can use to code the parameter values before the
RecordSource executes?


I have done it in the Form_Open Event as shown below but I think I am
missing something

I would prefer to use the RecordSource builder to create the SQL and supply
the values for the parameters and NOT have to cut and paste the results into
a string each time the SQL changes.

Private Sub Form_Open(Cancel As Integer)
Dim strSQL as string
strSQL = SELECT tblProfile.ProfileID FROM tblProfile WHERE
(((tblProfile.ProfileID = [pProfileID]));
Replace(strSQL, "[pProfileID]", "222")
Me.RecordSource = strSQL
End Sub

 

[Duane Hookom]

I would not add any filter/criteria/prompt/parameter in the query. Open your
form with code like:
Dim strWhere as String
strWhere = "[ProfileID] = " & Me.txtProfileID
DoCmd.OpenForm "frmYourForm", , , strWhere

 

[Craig]

Thanks for you help!!!!!!

That makes a lot of sense when opening one form from many other forms and
using different parameters.

That's what I did in the .NET version of this same system. I am learning
Access by replicating the .NET system and want to use all the 'built in'
power Access has to get the job done. I don't want to reinvent the wheel.

So thanks!

I would not add any filter/criteria/prompt/parameter in the query. Open your
form with code like:
Dim strWhere as String
strWhere = "[ProfileID] = " & Me.txtProfileID
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
MS Access MVP

I have a form whose whose RecordSource property is.....

SELECT tblProfile.ProfileID
FROM tblProfile
WHERE (((tblProfile.ProfileID)=[pProfileID]));

I will open this form from many places in the application, so how do I
supply the value for the parameter programmatically?

Use the InputParameters property? If so how?

Isn't there some event I can use to code the parameter values before the
RecordSource executes?


I have done it in the Form_Open Event as shown below but I think I am
missing something

I would prefer to use the RecordSource builder to create the SQL and supply
the values for the parameters and NOT have to cut and paste the results into
a string each time the SQL changes.

Private Sub Form_Open(Cancel As Integer)
Dim strSQL as string
strSQL = SELECT tblProfile.ProfileID FROM tblProfile WHERE
(((tblProfile.ProfileID = [pProfileID]));
Replace(strSQL, "[pProfileID]", "222")
Me.RecordSource = strSQL
End Sub