Using Combobox to Sort subform

[Ian]

I've been using comboboxes in a main form to filter records in a subform and
wanted to try the same technique to sort the records.

I've placed a combobox in the main form called sortcombo
in the subform orderby property I've put;
forms.[frm_expiringdrugsmain].[sortcombo]
and in the OnChange property of the sortcombo I put;
Private Sub SortCombo_Change()
DoCmd.Requery "frm_expiringdrugssub"
End Sub

Doesn't work - any ideas?

 

[Larry Linson]

You might get better help if you told us what the Names of the main Form,
the Subform Control, and the Form Embedded in the Subform Control are.

I think you'll find that the Change event of a Combo fires at every
keystroke, and that where you want to put your code is in its AfterUpdate
event. And, with that code in an event of the main Form, you need to refer
to the Form embedded in the Subform control as the Form property of that
control...

Me.nameofsubformcontrol.Form.OrderBy

A Form embedded in a Subform Control, even when displayed, is not a member
of the Forms collection, but it would be the OrderBy of that embedded Form
that should be changed. I am not certain whether you will have to Requery...
if so, it is the Subform Control that you need to Requery.

Larry Linson
Microsoft Office Access MVP

 

[Ian]

The main form is "frm_drugdistmain" and the sub is "frm_drugdistsub". The
combobox is a is frm_drugdistmain!sortcombo (no text allowed, just pick from
menu, so the keystroke problem isn't really one)

I use a similiar technique to filter the records of frm_drugdistsub and it
works well.

I tried to rebuild the SQL statement of the sub's query but can't transfer
the text from the combobox properly into the ORDER BY clause (see SQL
statement below)

If I make the ORDER BY clause MasterDrugList.ExpireDate is sorts by the
expire date but I'd like the option of choosing one of the combobox options.

SELECT Distribution.ID, MasterDrugList.Drug, MasterDrugList.LotNo,
Distribution.Quantity, MasterDrugList.ExpireDate
FROM MasterDrugList INNER JOIN Distribution ON MasterDrugList.ID =
Distribution.ID
WHERE (((Distribution.Office)=[forms].[frm_expiringdrugsmain].[officecombo]))
GROUP BY Distribution.ID, MasterDrugList.Drug, MasterDrugList.LotNo,
Distribution.Quantity, MasterDrugList.ExpireDate
ORDER BY forms.frm_expiringdrugsmain.sortcombo;

You might get better help if you told us what the Names of the main Form,
the Subform Control, and the Form Embedded in the Subform Control are.

I think you'll find that the Change event of a Combo fires at every
keystroke, and that where you want to put your code is in its AfterUpdate
event. And, with that code in an event of the main Form, you need to refer
to the Form embedded in the Subform control as the Form property of that
control...

Me.nameofsubformcontrol.Form.OrderBy

A Form embedded in a Subform Control, even when displayed, is not a member
of the Forms collection, but it would be the OrderBy of that embedded Form
that should be changed. I am not certain whether you will have to Requery...
if so, it is the Subform Control that you need to Requery.

Larry Linson
Microsoft Office Access MVP

I've been using comboboxes in a main form to filter records in a subform
and
wanted to try the same technique to sort the records.

I've placed a combobox in the main form called sortcombo
in the subform orderby property I've put;
forms.[frm_expiringdrugsmain].[sortcombo]
and in the OnChange property of the sortcombo I put;
Private Sub SortCombo_Change()
DoCmd.Requery "frm_expiringdrugssub"
End Sub

Doesn't work - any ideas?

 

[Larry Linson]

The main form is "frm_drugdistmain" and the sub is "frm_drugdistsub". The
combobox is a is frm_drugdistmain!sortcombo (no text allowed, just pick from
menu, so the keystroke problem isn't really one)

By "sub" do you mean the Subform Control or do you mean the Form embedded in
the Subform Control (its Source Object)? There is no such object in Access
as a "Subform" -- there's a Subform Control object, into which a Source
Object is embedded, which may be a "Form".

Humor me by moving the code to the AfterUpdate event. I'm not certain at
what point the new value becomes available, but I know it is available in
the AfterUpdate event.

I use a similiar technique to filter the records of frm_drugdistsub and it
works well.

If you did not use an _identical_ technique, then it's good to remember "The
Devil's in the details."

I tried to rebuild the SQL statement of the sub's query but can't transfer
the text from the combobox properly into the ORDER BY clause (see SQL
statement below)

Now you have confused me, as I thought you were trying to replace the ORDER
BY property of the Form embedded in the Subform Control. That's a problem I
have with getting information in bits and pieces. I handle answers much
better when I get the full details in a precise and concise question, and
don't find out "little surprises" in subsequent posts.

But, if you intended to refer to the RecordSource of the Form embedded in
the Subform Control, there was no statement that I recall in the original
post, nor this one, that properly refers to it.

If I make the ORDER BY clause MasterDrugList.ExpireDate is sorts by the
expire date but I'd like the option of choosing one of the combobox
options.

SELECT Distribution.ID, MasterDrugList.Drug, MasterDrugList.LotNo,
Distribution.Quantity, MasterDrugList.ExpireDate
FROM MasterDrugList INNER JOIN Distribution ON MasterDrugList.ID =
Distribution.ID
WHERE
(((Distribution.Office)=[forms].[frm_expiringdrugsmain].[officecombo]))
GROUP BY Distribution.ID, MasterDrugList.Drug, MasterDrugList.LotNo,
Distribution.Quantity, MasterDrugList.ExpireDate
ORDER BY forms.frm_expiringdrugsmain.sortcombo;

A GROUP BY clause is normally used in Totals Queries, but this does not
appear to be a Totals Query, just a plain SELECT Query. I'm surprised that
it works at all, if indeed it does. Writing SQL from scratch is not a
trivial undertaking, even if one knows "some SQL". It is a good approach for
me to start from the Query Builder and modify if necessary. I don't know how
you could generate this SQL with the Query Builder.

I have always used the Forms! notation... ! refers to a member of a
Collection. "Forms" is a Collection of open Forms. (And, as I said earlier,
a form embedded in a Subform Control is not "Open" nor a member of the Forms
Collection, but that "instantiation" exists as the Form property of the
Subform Control). The dot notation refers to a Property but not, as far as I
know, to a member of the Forms Collection.

(You can use either . or ! to refer to a Control on a Form because the
Controls are both members of the Form's Control Collection -- the default
Collection for a Form object -- and Properties of the Form.)

Larry Linson
Microsoft Office Access MVP

 

[Elamri. Mohammed]

I've been using comboboxes in a main form to filter records in a subform
and
wanted to try the same technique to sort the records.

I've placed a combobox in the main form called sortcombo
in the subform orderby property I've put;
forms.[frm_expiringdrugsmain].[sortcombo]
and in the OnChange property of the sortcombo I put;
Private Sub SortCombo_Change()
DoCmd.Requery "frm_expiringdrugssub"
End Sub

Doesn't work - any ideas?