Form/Subform Help (1 Viewer)

pinkpanther6666 · Mar 10, 2018

Good Evening

I have a form called frmSupplies with 2 x ComboBoxes in it called Combo1 and Combo2

Combo1 shows data from a table called Supplier1
Combo1 shows data from a table called Product1

I have a subform called subData in the same form
This has various fields in it and 2 are called Supplier1 and Product1

If i want to select in the main from from Combo1 a supplier and in combo2 select a product how do i get the subform to filter this data out:banghead:

The subform contains about 1200 lines of data and is constantly been added to.

Many Thanks in advance

Cheers

Steve

isladogs · Mar 11, 2018

First of all, are the form/subform linked using parent/child field(s)
If so, what are they?

The standard approach to this is to use code in the after update event of each combo to filter the record source for the subform
As part of this you may also need to requery the subform

Assuming the subform record source is all fields in a query qrySubData then you need something like

Code:

Private Sub Combo1_AfterUpdate()

Me.subData.Form.RecordSource = "SELECT * FROM qrySubData WHERE Supplier1 = '" & Me.Combo1 & "' AND Product1 = '" & Me.Combo2 & "'")

Me.subData.Requery 'if needed to refresh display

End Sub

Similarly for the other combo
Alternatively, and probably better, use the above code on a button click INSTEAD of both of the after update events

bob fitz · Mar 11, 2018

You could filter the sub form after selection of each of the combo boxes.

I have attached a db to illustrate how this might be done. It uses data from Northwind and the form and control names that you have used.

Post back if you have any questions on how it works.

pinkpanther6666 · Mar 17, 2018

Sorry for the delay work and family have been in the way but i have looked at at the attached database in the week ..will get back with any questions

Many thanks in advance

Steve

jdraw · Mar 17, 2018

Steve,

Tell us more about the tables?
And tell us about the application in plain English ---no database jargon.

pinkpanther6666 · Mar 17, 2018

Basically i have a form with,

2 x Combo Boxes and 1 x subform

i want to select values in both combo boxes and then the data within the subform is filtered by those values.

The Subform contains about 10 fields of which 2 are the values in the combo boxes

Hope this helps

Many Thanks in Advance

Steve

bob fitz · Mar 18, 2018

Can you post a copy of your db

arnelgp · Mar 18, 2018

add this public function to your form:

Code:

Public Function SubformFilter()
    Dim strMasterFields As String
    Dim strChildFields As String
    If Me.Combo1.ListIndex > -1 Then
        strMasterFields = "combo1;"
        strChildFields = "supplier1;"
    End If
    If Me.Combo2.ListIndex > -1 Then
        strMasterFields = strMasterFields & "combo2;"
        strChildFields = strChildFields & "product1;"
    End If
    Application.Echo False
    With Me.subData
        .LinkMasterFields = ""
        .LinkChildFields = ""
    End With
    If strMasterFields <> "" Then
        strMasterFields = Left(strMasterFields, Len(strMasterFields) - 1)
        strChildFields = Left(strChildFields, Len(strChildFields) - 1)
    End If
    With Me.subData
        .LinkMasterFields = strMasterFields
        .LinkChildFields = strChildFields
    End With
    Application.Echo True
End Function

next click on combo1, on its Property->Event->After Update, put:

=SubformFilter().

do the same thing with combo2.

pinkpanther6666 · Apr 15, 2018

[SOLVED]Re: Form/Subform Help

Many Many Thanks for All your replies

Ive been away from here due to work and family time

i have deleted my database and restarted it and its coming along very nicely at the moment.

Many Thanks again

Steve

Form/Subform Help (1 Viewer)

pinkpanther6666

New member

isladogs

MVP / VIP

bob fitz

AWF VIP

Attachments

pinkpanther6666

New member

jdraw

Super Moderator

pinkpanther6666

New member

bob fitz

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

pinkpanther6666

New member

Similar threads

Users who are viewing this thread