Repost - mod to linked forms code

[JohnB]

Hi.

I though this would be an easy one, so I'm surprised not to get any replies.
I though I'd give it one more try. Basically I want to modify existing code
that opens a form using SchoolID as the linking criteria. I want to add to
this linking criteria so that the form opened shows only those subform
records where field cboSubject contains "Maths". Thanks. JohnB

Original post:

Hi. Can anyone suggest a modification to the following code please? Its part
of the On Click event of a command button on a form that opens a second form,
frmPlacementsBySchool. This second form has a subform called
frmPlacementsSubform with a combo called cboSubject. (The control source of
cboSubject is a table and the choice of a particular subject puts SubjectID
in a field of the same name in tblPlacements). In addition to the existing
criteria (SchoolID) I want frmPlacementsBySchool to open and show only those
subform records that show "Maths" (which is SubjectID 7) in the combo. How
would I do that? I'm using Access 2002 on XP.

Thanks, JohnB

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPlacementsBySchool"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Wayne Morgan]

You can filter the main form to only include those SchoolIDs that will have
Maths as a one of the records in the subform, but that won't limit the
subform to ONLY records that have Maths. You would also need to add criteria
for a filter on the main form to limit it to records that DON'T have
subrecords other than Maths. Is that what you want or do you want any record
on the main form where Maths may be one of the records on the subform?

There are a couple of problems.

1) Depending on how the combo box is set up, you may not need to filter on
Maths, but on the value of the Bound Column of the combo box if there is
more than one column.

2) This value probably isn't available to the main form. The subform is
probably linked to the main form on the SchoolID. You would actually need to
change the query feeding the main form to include this linked table and add
the field from the combo box's Control Source to the recordset for the main
form. You would now be able to filter on this field.

Assuming the bound column of the combo box is the column that contains the
word "Maths" and you've added that field to the main form's recordset, the
criteria would be similar to:

[SubjectField] = "Maths"

This would give you any school that has Maths, but may have other topics
also. To limit to schools that have ONLY Maths:

I was able to do this in a query, but I'm not sure about the Filter on a
form. You may need to change the query feeding the main form.

SELECT Table1.Test1, Table2.Field3, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.Test1 = Table2.Field1
WHERE (((Table2.Field3)=11) AND ((Table2.Field1) Not In (Select
Table2.Field1 From Table2 Where Table2.Field3 <> 11)));

 

[JohnB]

Thanks for replying Wayne.

Hmmmm this seems more difficult than I thought. You've described one way of
solving this problem for me and in fact I have already done that. I have
arranged for the form opening on the command button to show only those
subform records that have "Maths" in cboSubject. But that functionality is
built into the main/subform queries.

The reason I thought it would be best to have this functionality in the
stLinkCriteria code of the buttons On Click event is that I will actually
have seven such buttons, each one opening a main/subform with a different
Subject shown in the cboSubject. Doing it the way you suggest will work but
it means that I have seven different main/subform setups and if I need to
make a small change in the main or subform layout, I would need to do it
seven times. I just though it would be more elegant to have the
stLinkCriteria make the decision - I would set the criteria code in each
button to have the relevant subject - Maths, Music, History etc and then each
button calls up a common main/subform and causes the appropriate School to
show in the main form and cboSubject to show in the subform records. Perhaps
this is just not possible.

Just to labour the point, if I could describe the stLinkCriteria code for,
say, the Maths command button in plain English, it would be "When this button
is clicked, open frmPlacementsBySchool and show the same School as the one
currently being viewed and also show only those subform records where 'Maths'
is shown in cboSubject". Is there a way of doing that in the stLinkCriteria
code?

Thanks again for the help, JohnB

You can filter the main form to only include those SchoolIDs that will have
Maths as a one of the records in the subform, but that won't limit the
subform to ONLY records that have Maths. You would also need to add criteria
for a filter on the main form to limit it to records that DON'T have
subrecords other than Maths. Is that what you want or do you want any record
on the main form where Maths may be one of the records on the subform?

There are a couple of problems.

1) Depending on how the combo box is set up, you may not need to filter on
Maths, but on the value of the Bound Column of the combo box if there is
more than one column.

2) This value probably isn't available to the main form. The subform is
probably linked to the main form on the SchoolID. You would actually need to
change the query feeding the main form to include this linked table and add
the field from the combo box's Control Source to the recordset for the main
form. You would now be able to filter on this field.

Assuming the bound column of the combo box is the column that contains the
word "Maths" and you've added that field to the main form's recordset, the
criteria would be similar to:

[SubjectField] = "Maths"

This would give you any school that has Maths, but may have other topics
also. To limit to schools that have ONLY Maths:

I was able to do this in a query, but I'm not sure about the Filter on a
form. You may need to change the query feeding the main form.

SELECT Table1.Test1, Table2.Field3, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.Test1 = Table2.Field1
WHERE (((Table2.Field3)=11) AND ((Table2.Field1) Not In (Select
Table2.Field1 From Table2 Where Table2.Field3 <> 11)));


--
Wayne Morgan
MS Access MVP

Hi.

I though this would be an easy one, so I'm surprised not to get any
replies.
I though I'd give it one more try. Basically I want to modify existing
code
that opens a form using SchoolID as the linking criteria. I want to add to
this linking criteria so that the form opened shows only those subform
records where field cboSubject contains "Maths". Thanks. JohnB

 

[Wayne Morgan]

I understand why you want to use the Link Criteria, but it is possible to
change a stored query through code and to change the form's Record Source
through code also. This would allow you to use a query and still make the
changes you need as you open the form.

To change a stored query, you would edit its SQL property.

CurrentDb.QueryDefs("qryMyQuery").SQL = "Select....etc;"

or to have the queries stored with the different WHERE clauses and just swap
out the form's Record Source, you could pass the name of the query in the
OpenArgs argument and change the form's Record Source in the form's Open
event.

DoCmd.OpenForm "frmFormName",,,,,, "qryQueryName"

Then in the form's Open event:
If Me.OpenArgs <> "" Then Me.RecordSource = Me.OpenArgs

 

[JohnB]

Thanks Wayne.

I didn't know you could do those sorts of things. I'm think I may need only
the first option you mention. So, if I understand correctly, I could replace
the subforms stored query SQL with SQL sent from each command button. Would I
add the appropriate code, suitably modified of course, to my existing code as
follows? Or does it not matter where I put it, provided it's not after the
DoCmd.OpenForm line?

Thanks again, JohnB

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPlacementsBySchool"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]

CurrentDb.QueryDefs("qryMyQuery").SQL = "Select....etc;"

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Wayne Morgan]

Where you have it placed should work. As you mentioned, it just needs to be
changes before you open the form and try to use the query.

 

[JohnB]

Hi Wayne.

I'm not able to work on the actual mdb at present but I thought I'd try your
suggestion on sample Access 97 and Access 2002 mdbs here at home. I've
created tblStudents, stored query qryStudents, form frmStudents and a
switchboard form on which I have a command button with the code quoted below
as part of the On Click event to open frmStudents. I've tried adding your
suggested code but it is throwing up "Expected end of statement" and "Invalid
Syntax" errors. It's in it's simplest form at present - the SQL I pasted in
is exactly as it is in the query, which itself works perfectly - I haven't
tried modifying anything yet. The SQL code pastes in on separate lines and is
immediately coloured red, so I rearranged it all on one line, then added the
quotes on either side of the SQL. But it stays red and gives the various
error messages mentioned above. Can you see what is wrong? Thanks JohnB.
Here's my code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmStudents"
CurrentDb.QueryDefs ("qryStudents").SQL = "SELECT tblStudents.StudentID,
tblStudents.Name, tblStudents.Function FROM tblStudents WHERE
(((tblStudents.Function)="SBT"));"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Where you have it placed should work. As you mentioned, it just needs to be
changes before you open the form and try to use the query.

--
Wayne Morgan
MS Access MVP

Thanks Wayne.

I didn't know you could do those sorts of things. I'm think I may need
only
the first option you mention. So, if I understand correctly, I could
replace
the subforms stored query SQL with SQL sent from each command button.
Would I
add the appropriate code, suitably modified of course, to my existing code
as
follows? Or does it not matter where I put it, provided it's not after the
DoCmd.OpenForm line?

Thanks again, JohnB

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPlacementsBySchool"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]

CurrentDb.QueryDefs("qryMyQuery").SQL = "Select....etc;"

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[JohnB]

Hi again Wayne. Please ignore my last post of a few minutes ago. In my usual
"poke and hope" way, I sorted this myself. Apparently the SQL copied from the
query has to have an additional set of quotes in the Where statement, as
shown below. This is now working perfectly. Thanks for the help - I think I
will use this method quite a lot. JohnB

CurrentDb.QueryDefs("qryStudents").SQL = "SELECT tblStudents.StudentID,
tblStudents.Name, tblStudents.Function FROM tblStudents WHERE
(((tblStudents.Function)= ""SBT"" )); "

Where you have it placed should work. As you mentioned, it just needs to be
changes before you open the form and try to use the query.

--
Wayne Morgan
MS Access MVP

Thanks Wayne.

I didn't know you could do those sorts of things. I'm think I may need
only
the first option you mention. So, if I understand correctly, I could
replace
the subforms stored query SQL with SQL sent from each command button.
Would I
add the appropriate code, suitably modified of course, to my existing code
as
follows? Or does it not matter where I put it, provided it's not after the
DoCmd.OpenForm line?

Thanks again, JohnB

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPlacementsBySchool"

stLinkCriteria = "[SchoolID]=" & Me![SchoolID]

CurrentDb.QueryDefs("qryMyQuery").SQL = "Select....etc;"

DoCmd.OpenForm stDocName, , , stLinkCriteria