Change a Form's recordsource dynamically?

E

efandango

Is it possible to change a Form's RecordSource from one table to another
table on-the-fly?

The two tables are identical, except they use a set of two different
criteria linked to two different pairs of combo boxes on the main form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID, tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue, tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1 FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1 ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
 
K

Ken Snell \(MVP\)

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
 
E

efandango

Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL" confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form [frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requer
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I
 
K

Ken Snell \(MVP\)

Open your main form in design view. Click on the top edge of the subform.
Open the Properties window. Click on the Other tab. What is the value in the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your "name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




efandango said:
Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form [frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


Ken Snell (MVP) said:
Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
Click to expand...
Click to expand...
 
E

efandango

Ken,

Yes the subform is called: 'frm_Point_2_Point_any_Postcodes_B'

So far, when I update the combo box all that happens is that the
'replacement' query opens (which I don't want to happen), and the form's
Record Source does not change in the properties box.

this is my current code:

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"


Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requer
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery





Ken Snell (MVP) said:
Open your main form in design view. Click on the top edge of the subform.
Open the Properties window. Click on the Other tab. What is the value in the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your "name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




efandango said:
Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form [frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


Ken Snell (MVP) said:
Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two different
criteria linked to two different pairs of combo boxes on the main form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1
ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Ken,

I kind of have it working with the code below with this line in particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

which gives first pops up a box asking me to 'Specify Relationship' between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between the two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

Ken Snell (MVP) said:
Open your main form in design view. Click on the top edge of the subform.
Open the Properties window. Click on the Other tab. What is the value in the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your "name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




efandango said:
Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form [frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


Ken Snell (MVP) said:
Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two different
criteria linked to two different pairs of combo boxes on the main form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode, tbl_Point_2_Point_2.Point_ID1
ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

OK sorry. My code suggestion was based on an erroneous assumption that the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""


--

Ken Snell
<MS ACCESS MVP>



efandango said:
Ken,

I kind of have it working with the code below with this line in particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

Ken Snell (MVP) said:
Open your main form in design view. Click on the top edge of the subform.
Open the Properties window. Click on the Other tab. What is the value in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




efandango said:
Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


:

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two different
criteria linked to two different pairs of combo boxes on the main
form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Ken,

To clarify, My 'subform' child and master properties have always been empty.
Though the subform is sitting in the mainform (frm_Runs), it has never been
linked.

I tried your code, like this:

*************
Private Sub cbo_Point2Point_Postcode_To_AfterUpdate()

'DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""


DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

'DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

End Sub

*************


But get an error:

Error: 2465
Application defined or object-defined error

and this line is highlighted yellow in the VBA window:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields = ""


Ken Snell (MVP) said:
OK sorry. My code suggestion was based on an erroneous assumption that the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields =
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields =
""


--

Ken Snell
<MS ACCESS MVP>



efandango said:
Ken,

I kind of have it working with the code below with this line in particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

Ken Snell (MVP) said:
Open your main form in design view. Click on the top edge of the subform.
Open the Properties window. Click on the Other tab. What is the value in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


:

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two different
criteria linked to two different pairs of combo boxes on the main
form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Is the form "frm_Runs" open when you're running your code? Is the subform
control "frm_Point_2_Point_any_Postcodes_B" visible when the code runs?

Changing the RecordSource is not a difficult thing to do in code, so I must
assume that there is more going on here than what you've described so far.
Can you give us a wider overview of your setup and actions?

--

Ken Snell
<MS ACCESS MVP>


efandango said:
Ken,

To clarify, My 'subform' child and master properties have always been
empty.
Though the subform is sitting in the mainform (frm_Runs), it has never
been
linked.

I tried your code, like this:

*************
Private Sub cbo_Point2Point_Postcode_To_AfterUpdate()

'DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

'DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

End Sub

*************


But get an error:

Error: 2465
Application defined or object-defined error

and this line is highlighted yellow in the VBA window:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""


Ken Snell (MVP) said:
OK sorry. My code suggestion was based on an erroneous assumption that
the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the
subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


--

Ken Snell
<MS ACCESS MVP>



efandango said:
Ken,

I kind of have it working with the code below with this line in
particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

:

Open your main form in design view. Click on the top edge of the
subform.
Open the Properties window. Click on the Other tab. What is the value
in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that
contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form
that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on
the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


:

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two
different
criteria linked to two different pairs of combo boxes on the main
form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
ORDER
BY tbl_Point_2_Point_2.Point2Point_ID DESC;

After a combo box on the main form is updated, I want the Sub
Form's
RecordSource to change to this:

SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

The Form "frm_Runs" is an overall main form that contains a number of tabs.
On one of these tab pages is the "frm_Point_2_Point_any_Postcodes_B". above
this form (on the main form) is a combo box that selects postcodes.

Using a criteria in a query ("Qry_Point_2_Point_any_Postcodes"
) linked to the combo (postcode) I am able to append to a table that in-turn
feeds the "frm_Point_2_Point_any_Postcodes_B".

the code for the combo is what I have previously posted where it first
deletes the old data from the table with this:

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"

Then it appends the new data with this:
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

then I requery the form, and should see the new results.



Ken Snell (MVP) said:
Is the form "frm_Runs" open when you're running your code? Is the subform
control "frm_Point_2_Point_any_Postcodes_B" visible when the code runs?

Changing the RecordSource is not a difficult thing to do in code, so I must
assume that there is more going on here than what you've described so far.
Can you give us a wider overview of your setup and actions?

--

Ken Snell
<MS ACCESS MVP>


efandango said:
Ken,

To clarify, My 'subform' child and master properties have always been
empty.
Though the subform is sitting in the mainform (frm_Runs), it has never
been
linked.

I tried your code, like this:

*************
Private Sub cbo_Point2Point_Postcode_To_AfterUpdate()

'DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

'DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

End Sub

*************


But get an error:

Error: 2465
Application defined or object-defined error

and this line is highlighted yellow in the VBA window:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""


Ken Snell (MVP) said:
OK sorry. My code suggestion was based on an erroneous assumption that
the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the
subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


--

Ken Snell
<MS ACCESS MVP>



Ken,

I kind of have it working with the code below with this line in
particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

:

Open your main form in design view. Click on the top edge of the
subform.
Open the Properties window. Click on the Other tab. What is the value
in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that
contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step. Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form
that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource = strSQL"
confuses
me because the Record Source is not linked to a specific control on
the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


:

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Is it possible to change a Form's RecordSource from one table to
another
table on-the-fly?

The two tables are identical, except they use a set of two
different
criteria linked to two different pairs of combo boxes on the main
form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No, tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

OK.

First, a correction on my code for the Link properties. Those lines should
be these:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkMasterFields = ""

Even though these properties have been empty, when you change a
RecordSource, it's good to explicitly tell ACCESS what they should be so
that it (1) doesn't have to ask, or (2) doesn't assume it knows what you
wanted and puts values into those properties for you.

Your code logic does not display any obvious problems/flaws to me. So, let's
start with some basic troubleshooting steps.

Have you checked that your query "Qry_Point_2_Point_any_Postcodes" is
providing the expected records when you run it? Check the table that holds
the data that it's providing after the query runs; perhaps the reason you're
not seeing the new data results is because this query is not providing the
correct results?
--

Ken Snell
<MS ACCESS MVP>



efandango said:
The Form "frm_Runs" is an overall main form that contains a number of
tabs.
On one of these tab pages is the "frm_Point_2_Point_any_Postcodes_B".
above
this form (on the main form) is a combo box that selects postcodes.

Using a criteria in a query ("Qry_Point_2_Point_any_Postcodes"
) linked to the combo (postcode) I am able to append to a table that
in-turn
feeds the "frm_Point_2_Point_any_Postcodes_B".

the code for the combo is what I have previously posted where it first
deletes the old data from the table with this:

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"

Then it appends the new data with this:
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

then I requery the form, and should see the new results.



Ken Snell (MVP) said:
Is the form "frm_Runs" open when you're running your code? Is the subform
control "frm_Point_2_Point_any_Postcodes_B" visible when the code runs?

Changing the RecordSource is not a difficult thing to do in code, so I
must
assume that there is more going on here than what you've described so
far.
Can you give us a wider overview of your setup and actions?

--

Ken Snell
<MS ACCESS MVP>


efandango said:
Ken,

To clarify, My 'subform' child and master properties have always been
empty.
Though the subform is sitting in the mainform (frm_Runs), it has never
been
linked.

I tried your code, like this:

*************
Private Sub cbo_Point2Point_Postcode_To_AfterUpdate()

'DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

'DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

End Sub

*************


But get an error:

Error: 2465
Application defined or object-defined error

and this line is highlighted yellow in the VBA window:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""


:

OK sorry. My code suggestion was based on an erroneous assumption that
the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the
subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


--

Ken Snell
<MS ACCESS MVP>



Ken,

I kind of have it working with the code below with this line in
particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between
the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

:

Open your main form in design view. Click on the top edge of the
subform.
Open the Properties window. Click on the Other tab. What is the
value
in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that
contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step.
Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form
that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource =
strSQL"
confuses
me because the Record Source is not linked to a specific control
on
the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9
tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery




what would I


:

Yes:

Private Sub NameOfComboBox_AfterUpdate()
Dim strSQL As String
strSQL = "The actual SQL Statement or Name of table/query"
Me.NameOfSubformControl.Form.RecordSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



message
Is it possible to change a Form's RecordSource from one table
to
another
table on-the-fly?

The two tables are identical, except they use a set of two
different
criteria linked to two different pairs of combo boxes on the
main
form

The subform is called: frm_Point_2_Point_any_Postcodes_B

Linked to a table with this RecordSource:

SELECT TOP 9 tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
tbl_Point_2_Point_2.Run_Point_Postcode,
tbl_Point_2_Point_2.Point_ID1
FROM
tbl_Point_2_Point_2 GROUP BY
tbl_Point_2_Point_2.Point2Point_ID,
tbl_Point_2_Point_2.Run_No,
tbl_Point_2_Point_2.Run_point_Venue,
tbl_Point_2_Point_2.Run_point_Address,
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Ken,

It works! (that last correction you made seemed to do the trick) I didn;t
change anything elsewhere, my queries were producing the correct results as
was the inter-table that was holding the results.

FYI: If i just used this (new) line (instead of your strSQL lines):

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
"tbl_Point_2_Point_2_Postcodes"

then the form would not require the requery lines, but your way allows me to
do an in-line filter on the table where I am asking for the top 9 values.


the working code:

********************************

DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkMasterFields = ""

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource =
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkMasterFields = ""

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

********************************

Thank you for your patience in helping me through this tricky problem. much
appreciated.

regards

Eric




Ken Snell (MVP) said:
OK.

First, a correction on my code for the Link properties. Those lines should
be these:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkChildFields = ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].LinkMasterFields = ""

Even though these properties have been empty, when you change a
RecordSource, it's good to explicitly tell ACCESS what they should be so
that it (1) doesn't have to ask, or (2) doesn't assume it knows what you
wanted and puts values into those properties for you.

Your code logic does not display any obvious problems/flaws to me. So, let's
start with some basic troubleshooting steps.

Have you checked that your query "Qry_Point_2_Point_any_Postcodes" is
providing the expected records when you run it? Check the table that holds
the data that it's providing after the query runs; perhaps the reason you're
not seeing the new data results is because this query is not providing the
correct results?
--

Ken Snell
<MS ACCESS MVP>



efandango said:
The Form "frm_Runs" is an overall main form that contains a number of
tabs.
On one of these tab pages is the "frm_Point_2_Point_any_Postcodes_B".
above
this form (on the main form) is a combo box that selects postcodes.

Using a criteria in a query ("Qry_Point_2_Point_any_Postcodes"
) linked to the combo (postcode) I am able to append to a table that
in-turn
feeds the "frm_Point_2_Point_any_Postcodes_B".

the code for the combo is what I have previously posted where it first
deletes the old data from the table with this:

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"

Then it appends the new data with this:
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

then I requery the form, and should see the new results.



Ken Snell (MVP) said:
Is the form "frm_Runs" open when you're running your code? Is the subform
control "frm_Point_2_Point_any_Postcodes_B" visible when the code runs?

Changing the RecordSource is not a difficult thing to do in code, so I
must
assume that there is more going on here than what you've described so
far.
Can you give us a wider overview of your setup and actions?

--

Ken Snell
<MS ACCESS MVP>


Ken,

To clarify, My 'subform' child and master properties have always been
empty.
Though the subform is sitting in the mainform (frm_Runs), it has never
been
linked.

I tried your code, like this:

*************
Private Sub cbo_Point2Point_Postcode_To_AfterUpdate()

'DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

'DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

End Sub

*************


But get an error:

Error: 2465
Application defined or object-defined error

and this line is highlighted yellow in the VBA window:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
= ""


:

OK sorry. My code suggestion was based on an erroneous assumption that
the
subform was in the form that was running the code.

What you can do is to change the subform's LinkChildFields and
LinkMasterFields properties to an empty string before you set the
subform's
RecordSource property, and then do it again right after you set the
RecordSource:

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkChildFields
=
""
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.LinkMasterFields
=
""


--

Ken Snell
<MS ACCESS MVP>



Ken,

I kind of have it working with the code below with this line in
particular
seeming to make some difference:
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

which gives first pops up a box asking me to 'Specify Relationship'
between
the subform (frm_Point_2_Point_any_Postcodes_B) and the master form
(frm_Runs). I don't want, or have to specfiy a relationship between
the
two
forms as they are entirely unrelated.

and if I use (your original) line:
Me.frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL

I just get a blank form.







*********************
DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "SELECT TOP 9 tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 FROM
tbl_Point_2_Point_2_Postcodes
GROUP BY tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
tbl_Point_2_Point_2_Postcodes.Point_ID1 ORDER BY
tbl_Point_2_Point_2_Postcodes.Point2Point_ID DESC;"

frm_Point_2_Point_any_Postcodes_B.Form.RecordSource = strSQL
'Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form.RecordSource
=
strSQL

DoCmd.OpenQuery "Qry_Point_2_Point_Postcodes_Delete"
DoCmd.OpenQuery "Qry_Point_2_Point_any_Postcodes"

DoCmd.SetWarnings True

Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Requery
Forms![frm_Runs]![frm_Point_2_Point_any_Postcodes_B].Form![tbl_Points.Run_point_Venue].Requery

*********************

:

Open your main form in design view. Click on the top edge of the
subform.
Open the Properties window. Click on the Other tab. What is the
value
in
the
Name box? That is the name of the subform control.

A subform is contained within a subform control -- that box that
contains
the subform form. That is the name that you must use in the
"Me.NameOfSubformControl.Form.RecordSource = strSQL" code step.
Your
"name",
"frm_Point_2_Point_any_Postcodes_B", likely is the name of the form
that
you're using as the subform's SourceObject.
--

Ken Snell
<MS ACCESS MVP>




Ken,

The code didn't change the Record Source for the form.

Where you say "Me.NameOfSubformControl.Form.RecordSource =
strSQL"
confuses
me because the Record Source is not linked to a specific control
on
the
subform, but instead is linked to the Subform itself.

The Subform is called: [frm_Point_2_Point_any_Postcodes_B]

which is on a main form called [frm_Runs]

I have this code in the Afterupdate for the combo on the form
[frm_Runs]

Dim strSQL As String
strSQL = "SELECT TOP 9
tbl_Point_2_Point_2_Postcodes.Point2Point_ID,
tbl_Point_2_Point_2_Postcodes.Run_No,
tbl_Point_2_Point_2_Postcodes.Run_point_Venue,
tbl_Point_2_Point_2_Postcodes.Run_point_Address,
tbl_Point_2_Point_2_Postcodes.Run_Point_Postcode,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Random number append query fails, why? 1
unique and optimized pairs of records - no dupes. 6
Can't change a form's recordsource 1
Setting the RecordSource dynamically with a global variable 3

Top