Dynamically changing recordsource of a subform

[bsagal]

Hello,

I have the following code i am using to change the recordsource of a subform at run time:

Dim recordsource
Set db = CurrentDb
Set recordsource = db.OpenRecordset(??????)
Me![employee].Form.recordsource = ??????

I want to use the query named "Employee" as my recordsource, but what is the syntax for using a query?

Thanks!

 

[BillPower]

Hi bsagal,

In the On Load event of your Main form:

Me!YourSubformName.Form.RecordSource = "Employee"

Bill

 

[bsagal]

Thanks for your reply Bill!

 

[jdegeorge]

Bill

I want to do something similar without a subform, but tie the record source to a toggle button.

The default record source will be a SQL statement that I have. Then, if the user clicks the button I want to requery the SQL to set the filter to that user's info only.

Can this be done?

Thanks!

Jim DeGeorge

 

[jdegeorge]

Bill

I tried the following code behind the toggle button's OnClick property:

If Me.tglFilter = 0 Then
Me.RecordSource = "SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null " _
& "AND tblGaps.Resolution Is Null AND tblGaps.Conversion=[Forms]![frmSignOn]![ctlConversion] " _
& "ORDER BY tblGaps.[Gap#];"
Else
Me.RecordSource = "SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null " _
& "AND tblGaps.Resolution Is Null AND tblGaps.Conversion=[Forms]![frmSignOn]![ctlConversion] " _
& "AND tblGaps.NFS_Owner=[Forms]![frmSignOn]![SignOn] " _
& "ORDER BY tblGaps.[Gap#];"
End If

Me.Requery

The form's OnLoad property has this code:

Me.RecordSource = "SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null " _
& "AND tblGaps.Resolution Is Null AND tblGaps.Conversion=[Forms]![frmSignOn]![ctlConversion] " _
& "ORDER BY tblGaps.[Gap#];"

It works great.

If you can think of a better way to do this, I'm open for ideas. Thanks!

Jim DeGeorge

 

[BillPower]

Hi jdegeorge,

I can't improve on your method other than to shorten your queries:

Select * instead of SELECT tblGaps.*

Have removed the table name from the query as you are only querying 1 table.

To refer to a control on a Form/Report use !

This site shows how to refer to Form/Report controls and properties:

http://www.mvps.org/access/forms/frm0031.htm

If Me!tglFilter = 0 Then
Me.RecordSource = "SELECT * FROM tblGaps WHERE DateClosed Is Null " _
& "AND Resolution Is Null AND Conversion=[Forms]![frmSignOn]![ctlConversion] " _
& "ORDER BY [Gap#];"
Else
Me.RecordSource = "SELECT * FROM tblGaps WHERE DateClosed Is Null " _
& "AND Resolution Is Null AND Conversion=[Forms]![frmSignOn]![ctlConversion] " _
& "AND NFS_Owner=[Forms]![frmSignOn]![SignOn] " _
& "ORDER BY [Gap#];"
End If

Hope this helps.

Bill

 

[jdegeorge]

Bill

I'll change the "." to "!", but it seems to be working as "." for now.

Didn't know you could leave out the table name in the SQL field references if there was only 1 table. Thanks!

Jim DeGeorge