Filter Form by Subform

K

Katherine

Hello,

I've been working on filtering a form based on data contained in a
subform. I got some code from Allen Browne's site
(http://allenbrowne.com/tips.html), but I haven't been able to get it
to do exactly what I need yet.

Here's the code I have right now:

Private Sub Combo3272_AfterUpdate()
Dim strSQL As String
If IsNull(Me.Combo3272) Then
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Else
strSQL = "SELECT DISTINCTROW EventDonationCalTotals.* FROM
EventDonationCalTotals " & _
"INNER JOIN MailingList ON " & _
"EventDonationCalTotals.MailingListID =
MailingList.MailingListID " & _
"WHERE EventDonationCalTotals.Total = " & Me.Combo3272 & ";"
Me.RecordSource = strSQL
End If
End Sub

When I enter something into the combo box, it will filter the records
accordingly in the subform, and will lower the number of records found
based on how many meet the criteria I entered. However, while it
populates the subform with the right data, it does not populate the
main form with the corresponding data. The main form just displays
"#Name?" in every field. When I remove the filtering criteria, both
forms go back to being populated with the correct information.

What code can I add to have the main form populated with the correct
info when the filter is on? I've tried a few things, but with no luck
so far. Any suggestions?

Thanks!
Katherine
 
A

Allen Browne

Where is Combo3272? On the main form? Or in the subform?

Is the combo unbound (i.e. nothing in its Control Source property)? It needs
to be.

You probably don't need to change both the main form's RecordSource and the
subform's RecordSource.

If we assume Combo3272 is an unbound combo on the main form, I don't follow
this code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
That would seem to be setting the main form's RecordSource to
EventDonationCalTotals, and then straight away setting it to something
different again.
 
K

Katherine

Combo3272 is on the main form. It is unbound.

This code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Click to expand...
was included because if I didn't include the second line, the mainform
would not populate with all the correct data once I set the combo box
back to null. It would have the correct number of records, but all the
fields would say "#Name?" instead of the desired data. This way, all
of the fields are set back to what they should be.


Allen said:
Where is Combo3272? On the main form? Or in the subform?

Is the combo unbound (i.e. nothing in its Control Source property)? It needs
to be.

You probably don't need to change both the main form's RecordSource and the
subform's RecordSource.

If we assume Combo3272 is an unbound combo on the main form, I don't follow
this code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
That would seem to be setting the main form's RecordSource to
EventDonationCalTotals, and then straight away setting it to something
different again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Katherine said:
Hello,

I've been working on filtering a form based on data contained in a
subform. I got some code from Allen Browne's site
(http://allenbrowne.com/tips.html), but I haven't been able to get it
to do exactly what I need yet.

Here's the code I have right now:

Private Sub Combo3272_AfterUpdate()
Dim strSQL As String
If IsNull(Me.Combo3272) Then
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Else
strSQL = "SELECT DISTINCTROW EventDonationCalTotals.* FROM
EventDonationCalTotals " & _
"INNER JOIN MailingList ON " & _
"EventDonationCalTotals.MailingListID =
MailingList.MailingListID " & _
"WHERE EventDonationCalTotals.Total = " & Me.Combo3272 & ";"
Me.RecordSource = strSQL
End If
End Sub

When I enter something into the combo box, it will filter the records
accordingly in the subform, and will lower the number of records found
based on how many meet the criteria I entered. However, while it
populates the subform with the right data, it does not populate the
main form with the corresponding data. The main form just displays
"#Name?" in every field. When I remove the filtering criteria, both
forms go back to being populated with the correct information.

What code can I add to have the main form populated with the correct
info when the filter is on? I've tried a few things, but with no luck
so far. Any suggestions?

Thanks!
Katherine
Click to expand...
Click to expand...
 
A

Allen Browne

If the main form's fields show #Name when its RecordSource is set to
EventDonationCalTotals, then it sounds like this is the wrong table/query to
use for the main form.

You say it works if you use MailingListQuery as its source query, so
apparently its fields come from there, and not from EventDonationCalTotals?
If so, the SQL statement you are building in the ELSE part won't work, since
it is getting its records from EventDonationCalTotals, and that's apparently
the wrong place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Katherine said:
Combo3272 is on the main form. It is unbound.

This code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Click to expand...
was included because if I didn't include the second line, the mainform
would not populate with all the correct data once I set the combo box
back to null. It would have the correct number of records, but all the
fields would say "#Name?" instead of the desired data. This way, all
of the fields are set back to what they should be.


Allen said:
Where is Combo3272? On the main form? Or in the subform?

Is the combo unbound (i.e. nothing in its Control Source property)? It
needs
to be.

You probably don't need to change both the main form's RecordSource and
the
subform's RecordSource.

If we assume Combo3272 is an unbound combo on the main form, I don't
follow
this code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
That would seem to be setting the main form's RecordSource to
EventDonationCalTotals, and then straight away setting it to something
different again.

Katherine said:
Hello,

I've been working on filtering a form based on data contained in a
subform. I got some code from Allen Browne's site
(http://allenbrowne.com/tips.html), but I haven't been able to get it
to do exactly what I need yet.

Here's the code I have right now:

Private Sub Combo3272_AfterUpdate()
Dim strSQL As String
If IsNull(Me.Combo3272) Then
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Else
strSQL = "SELECT DISTINCTROW EventDonationCalTotals.* FROM
EventDonationCalTotals " & _
"INNER JOIN MailingList ON " & _
"EventDonationCalTotals.MailingListID =
MailingList.MailingListID " & _
"WHERE EventDonationCalTotals.Total = " & Me.Combo3272 & ";"
Me.RecordSource = strSQL
End If
End Sub

When I enter something into the combo box, it will filter the records
accordingly in the subform, and will lower the number of records found
based on how many meet the criteria I entered. However, while it
populates the subform with the right data, it does not populate the
main form with the corresponding data. The main form just displays
"#Name?" in every field. When I remove the filtering criteria, both
forms go back to being populated with the correct information.

What code can I add to have the main form populated with the correct
info when the filter is on? I've tried a few things, but with no luck
so far. Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
K

Katherine

I must have just have it backwards! I flip-flopped most of the names,
and now it seems to work fine and consistently. Thanks for the help!
Now I will move on to make it more complicated.


Allen said:
If the main form's fields show #Name when its RecordSource is set to
EventDonationCalTotals, then it sounds like this is the wrong table/query to
use for the main form.

You say it works if you use MailingListQuery as its source query, so
apparently its fields come from there, and not from EventDonationCalTotals?
If so, the SQL statement you are building in the ELSE part won't work, since
it is getting its records from EventDonationCalTotals, and that's apparently
the wrong place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Katherine said:
Combo3272 is on the main form. It is unbound.

This code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Click to expand...
was included because if I didn't include the second line, the mainform
would not populate with all the correct data once I set the combo box
back to null. It would have the correct number of records, but all the
fields would say "#Name?" instead of the desired data. This way, all
of the fields are set back to what they should be.


Allen said:
Where is Combo3272? On the main form? Or in the subform?

Is the combo unbound (i.e. nothing in its Control Source property)? It
needs
to be.

You probably don't need to change both the main form's RecordSource and
the
subform's RecordSource.

If we assume Combo3272 is an unbound combo on the main form, I don't
follow
this code:
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
That would seem to be setting the main form's RecordSource to
EventDonationCalTotals, and then straight away setting it to something
different again.

Hello,

I've been working on filtering a form based on data contained in a
subform. I got some code from Allen Browne's site
(http://allenbrowne.com/tips.html), but I haven't been able to get it
to do exactly what I need yet.

Here's the code I have right now:

Private Sub Combo3272_AfterUpdate()
Dim strSQL As String
If IsNull(Me.Combo3272) Then
Me.RecordSource = "EventDonationCalTotals"
Forms![Mailing List Form].RecordSource = "MailingListQuery"
Else
strSQL = "SELECT DISTINCTROW EventDonationCalTotals.* FROM
EventDonationCalTotals " & _
"INNER JOIN MailingList ON " & _
"EventDonationCalTotals.MailingListID =
MailingList.MailingListID " & _
"WHERE EventDonationCalTotals.Total = " & Me.Combo3272 & ";"
Me.RecordSource = strSQL
End If
End Sub

When I enter something into the combo box, it will filter the records
accordingly in the subform, and will lower the number of records found
based on how many meet the criteria I entered. However, while it
populates the subform with the right data, it does not populate the
main form with the corresponding data. The main form just displays
"#Name?" in every field. When I remove the filtering criteria, both
forms go back to being populated with the correct information.

What code can I add to have the main form populated with the correct
info when the filter is on? I've tried a few things, but with no luck
so far. Any suggestions?
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

Populate main form from subform record 0
Unremovable horizontal scroll bar on subform 1
Filter in the subform 1
Filter on Subform Not Displaying as Expected 1
record source on subform 4
Filtering subform combo from main form combo 2
How do I clear a form? 0
subform 0

Top