Requery subform from new criteria

[Tom McMillion]

I have a subform created from a select query, and a
button on the main form on which the subform resides,
which refreshes the content displayed on the subform when
the button is clicked.

I would like to create an additional button which will in
effect requery the subform using a different Select
Query, or to put it another way will use a different set
of criteria for running the query from which the data on
the subform is populated. I understand that there is a
Me.RecordSource command which can help accomplish this.

My question is, how and where do I program this
Me.RecordSource command?

I hope this is adequate info to answer the question.

Thanks in advance,
Tom

 

[Guest]

You are correct on the recordsource part, but don't use me in the main form, that will return the main form as the form object, instead use the full form name for the subform. SOmething like
Forms!Hostform!Subform.Recordsource = "SELECT * FROM table WHERE This = True;"
where hostform is the name of the form you have the subform in and
subform is the name of the subform.
i think this is correct, but it has been a year or so since I programed this and I did this in Access97.

 

[Tom McMillion]

Hmm, I tried this but I'm obviously doing something
fundamentally wrong. I created a click event in VBA code
and then simply entered the complete Recordsource command
line.

When I click on the button it returns run time error 438:
Object doesn't support this property or method.

I hope you or someone else cand guide me through the rest
of the way. Thanks for your help.

-----Original Message-----
You are correct on the recordsource part, but don't use

me in the main form, that will return the main form as
the form object, instead use the full form name for the
subform. SOmething like

Forms!Hostform!Subform.Recordsource = "SELECT * FROM table WHERE This = True;"
where hostform is the name of the form you have the subform in and
subform is the name of the subform.
i think this is correct, but it has been a year or so

since I programed this and I did this in Access97.

 

[Guest]

Post your Code under your button and let me take a look.

 

[Tom McMillion]

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto.[Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

 

[John Spencer (MVP)]

Pardon me. How about

Forms![frmPackaging]![frmPackaging subform].Form.RecordSource = ...

And yes that is "Form" not "FormS" after the reference to the subform control.

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto.[Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

-------------------------------------------

-----Original Message-----
Post your Code under your button and let me take a look.
.

-----Original Message-----
Post your Code under your button and let me take a look.
.

 

[Guest]

By all means no apology necessary for 'jumping in'. On
the contrary, many thanks. I'll give that a try.

Also do I need an "On Error GoTo..." command line right
under the "Sub cmdReSort_Click()" line? Just curious.

-----Original Message-----
Pardon me. How about

Forms![frmPackaging]![frmPackaging

subform].Form.RecordSource = ...

And yes that is "Form" not "FormS" after the reference to the subform control.

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto.[Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

-------------------------------------------

-----Original Message-----
Post your Code under your button and let me take a look.
.

-----Original Message-----
Post your Code under your button and let me take a look.
.

.

 

[Tom McMillion]

John,

Your suggested change mostly works. Thank you very
much. The button basically applies a different query to
the subform, exactly what I needed. I'll try to describe
the only outstanding issue I've noticed so far, after
making the change.

There are three fields for each record which represent
three types of packaging machines that each packaging
order can be scheduled to run on. Only one of the three
types of machines is going to be selected (only one of
three fields populated) for each and every record.

After I click on the button and the info displayed on the
subform refreshes, the following characters are displayed
in both of the two empty fields for each record: #Name?

I know there's a reason for this but I don't know enough
to know what the cause is, and the fix for it. I would
greatly appreciate any additional assistance.

-----Original Message-----
Pardon me. How about

Forms![frmPackaging]![frmPackaging

subform].Form.RecordSource = ...

And yes that is "Form" not "FormS" after the reference to the subform control.

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto.[Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

-------------------------------------------

-----Original Message-----
Post your Code under your button and let me take a look.
.

-----Original Message-----
Post your Code under your button and let me take a look.
.

.

 

[John Spencer (MVP)]

The problem is that the controls DO NOT see the field they are bound to in the
new query.

The fix might be to have one control (not three) and when you reset the
RecordSource of the subcontrol's form, you would also need to reset the
control's source to the new field.

Forms![frmPackaging]![frmPackaging subform].Form.ControlName.ControlSource = "NameOfTheField"

John,

Your suggested change mostly works. Thank you very
much. The button basically applies a different query to
the subform, exactly what I needed. I'll try to describe
the only outstanding issue I've noticed so far, after
making the change.

There are three fields for each record which represent
three types of packaging machines that each packaging
order can be scheduled to run on. Only one of the three
types of machines is going to be selected (only one of
three fields populated) for each and every record.

After I click on the button and the info displayed on the
subform refreshes, the following characters are displayed
in both of the two empty fields for each record: #Name?

I know there's a reason for this but I don't know enough
to know what the cause is, and the fix for it. I would
greatly appreciate any additional assistance.

-----Original Message-----
Pardon me. How about

Forms![frmPackaging]![frmPackaging

subform].Form.RecordSource = ...

And yes that is "Form" not "FormS" after the reference to the subform control.

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto.[Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

-------------------------------------------

-----Original Message-----
Post your Code under your button and let me take a look.
.


-----Original Message-----
Post your Code under your button and let me take a look.
.

.

 

[Guest]

Thanks John for all your help. I figured it out now. I
had controls on the subform that were not referenced in
the query which was opened by the RecordSource
reassignment.

I modified the query to include the additional controls
(fields) which the subform itself already contained, and
all is well now.

-----Original Message-----
The problem is that the controls DO NOT see the field they are bound to in the
new query.

The fix might be to have one control (not three) and when you reset the
RecordSource of the subcontrol's form, you would also need to reset the
control's source to the new field.

Forms![frmPackaging]![frmPackaging

subform].Form.ControlName.ControlSource = "NameOfTheField"

John,

Your suggested change mostly works. Thank you very
much. The button basically applies a different query to
the subform, exactly what I needed. I'll try to describe
the only outstanding issue I've noticed so far, after
making the change.

There are three fields for each record which represent
three types of packaging machines that each packaging
order can be scheduled to run on. Only one of the three
types of machines is going to be selected (only one of
three fields populated) for each and every record.

After I click on the button and the info displayed on the
subform refreshes, the following characters are displayed
in both of the two empty fields for each record: #Name?

I know there's a reason for this but I don't know enough
to know what the cause is, and the fix for it. I would
greatly appreciate any additional assistance.

-----Original Message-----
Pardon me. How about

Forms![frmPackaging]![frmPackaging

subform].Form.RecordSource = ...

And yes that is "Form" not "FormS" after the reference to the subform control.



Tom McMillion wrote:

Sure. Thanks very much in advance for your assistance.

Hope this sheds enough of light on things.

-------------------------------------------

Private Sub cmdReSort_Click()
Forms![frmPackaging]![frmPackaging subform].RecordSource
= "SELECT qryPkgOrdersAuto.[W/O Number],
qryPkgOrdersAuto.Title, qryPkgOrdersAuto.[Due Date],
qryPkgOrdersAuto.Priority, qryPkgOrdersAuto.[Bill Qty],
qryPkgOrdersAuto.[Pkg status], qryPkgOrdersAuto. [Auto
Line Number] FROM qryPkgOrdersAuto"
End Sub

-------------------------------------------

-----Original Message-----
Post your Code under your button and let me take a look.
.


-----Original Message-----
Post your Code under your button and let me take a look.
.

.

.