Parameters for the recordsource of a form

[Craig]

Hi

I am new to access and am using SQL Server 2000 as the back end.

I have a list page where I click on a button and it opens a new form page
that shows records. That all works.

The RecordSource on the form page is......

SELECT tblActivities.* FROM tblActivities WHERE (ProfileID = @ProfileID)

What's the proper way to substitute or set the value of a parameter??

Please Help!

Thanks



The code below worked but I know its not the right place or way to set a
parameters value.

Private Sub Form_Load()
Dim intProfileID As Integer

intProfileID = Forms!frmActivitiesList![ProfileID]

Form.RecordSource = "SELECT tblActivities.* FROM tblActivities WHERE
(ProfileID = " & intProfileID & ")"
Form.Requery

End Sub

 

[Tom Ellison]

Dear Craig:

Having worked in ADPs for 3 years now, I would say this pretty much IS
the way to do it. I don't think you need to requery as setting the
RecordSource triggers a requery automatically.

That's not to say it is a way that is recommended by Microsoft. But
we have not found any problems in doing this, nor have we a better way
to recommend to you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Craig]

Doesn't this load the data twice?

Dear Craig:

Having worked in ADPs for 3 years now, I would say this pretty much IS
the way to do it. I don't think you need to requery as setting the
RecordSource triggers a requery automatically.

That's not to say it is a way that is recommended by Microsoft. But
we have not found any problems in doing this, nor have we a better way
to recommend to you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi

I am new to access and am using SQL Server 2000 as the back end.

I have a list page where I click on a button and it opens a new form page
that shows records. That all works.

The RecordSource on the form page is......

SELECT tblActivities.* FROM tblActivities WHERE (ProfileID = @ProfileID)

What's the proper way to substitute or set the value of a parameter??

Please Help!

Thanks



The code below worked but I know its not the right place or way to set a
parameters value.

Private Sub Form_Load()
Dim intProfileID As Integer

intProfileID = Forms!frmActivitiesList![ProfileID]

Form.RecordSource = "SELECT tblActivities.* FROM tblActivities WHERE
(ProfileID = " & intProfileID & ")"
Form.Requery

End Sub

 

[Craig]

Isn't there an event to code to set the value for the parameter like....
the onBeforeDataLoad event or something and put in the code

@ProfileID = intProfileID

or

@Param1 = intProfileID ?????

Other wise why set the recordsource property to
SELECT tblActivities.* FROM tblActivities WHERE (ProfileID = @ProfileID)

Thanks

Dear Craig:

Having worked in ADPs for 3 years now, I would say this pretty much IS
the way to do it. I don't think you need to requery as setting the
RecordSource triggers a requery automatically.

That's not to say it is a way that is recommended by Microsoft. But
we have not found any problems in doing this, nor have we a better way
to recommend to you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi

I am new to access and am using SQL Server 2000 as the back end.

I have a list page where I click on a button and it opens a new form page
that shows records. That all works.

The RecordSource on the form page is......

SELECT tblActivities.* FROM tblActivities WHERE (ProfileID = @ProfileID)

What's the proper way to substitute or set the value of a parameter??

Please Help!

Thanks



The code below worked but I know its not the right place or way to set a
parameters value.

Private Sub Form_Load()
Dim intProfileID As Integer

intProfileID = Forms!frmActivitiesList![ProfileID]

Form.RecordSource = "SELECT tblActivities.* FROM tblActivities WHERE
(ProfileID = " & intProfileID & ")"
Form.Requery

End Sub

 

[Tom Ellison]

Dear Craig:

We generally use a SELECT TOP 1 query for the RecordSource during
construction of the form. You can take this out and have the form
saved without a RecordSource, only setting a RecordSource for it
during the Open (or Load) event. That way it only loads once, with
the desired RecordSource query.

You are asking all the right, intelligent questions! You bring out
the best answers that way. I didn't know at first you would be this
advanced!

I've never really checked to see if this would "load the data twice".
It may be it would not, but we've had the same thoughts you have, and
program around it as described above. Actually, the "load" of the
data might be occurring later, at the time of the CurrentEvent firing.
I'm not sure, but you're right to consider a small performance issue
here, for the sake of both the server and the network.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Doesn't this load the data twice?

Dear Craig:

Having worked in ADPs for 3 years now, I would say this pretty much IS
the way to do it. I don't think you need to requery as setting the
RecordSource triggers a requery automatically.

That's not to say it is a way that is recommended by Microsoft. But
we have not found any problems in doing this, nor have we a better way
to recommend to you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi

I am new to access and am using SQL Server 2000 as the back end.

I have a list page where I click on a button and it opens a new form page
that shows records. That all works.

The RecordSource on the form page is......

SELECT tblActivities.* FROM tblActivities WHERE (ProfileID = @ProfileID)

What's the proper way to substitute or set the value of a parameter??

Please Help!

Thanks



The code below worked but I know its not the right place or way to set a
parameters value.

Private Sub Form_Load()
Dim intProfileID As Integer

intProfileID = Forms!frmActivitiesList![ProfileID]

Form.RecordSource = "SELECT tblActivities.* FROM tblActivities WHERE
(ProfileID = " & intProfileID & ")"
Form.Requery

End Sub