Listbox controlling Sub-form

[John Petty]

I have a listbox that is populated from a sub query (qyListFEA). I have set
the subform's LinkMasterField and LinkChildField to this listbox.

The list box contains 3 columns (Project Name, Project Number and Iteration).

When I select let's say Wheel 1, 5214, 1C, the subform only shows the Wheel
1 at iteration 1.

How can I get the data to synchronize so that if I select Wheel 1 at
iteration 1c or 2, etc. I can get the correct record to show?

Note: the sub query "qyListFEA" is based upon "qyFEA" which is where I need
the actual data from. If I use the master query in the listbox, I get a
bunch of ID's instead of the text.

Any help would be appreciated.

 

[Klatuu]

You don't use form controls in the link master and link child properties of
the subform control. Use the names of fields in the form and subform
recordsets.

 

[John Petty]

Dave,

Thanks for the response. I would have responded sooner but I had a calculus
class to attend.

That being said, I tried to set the master and child field links to tje
field names but to no avail. I get input boxes asking for the field
criteria, or when I link all fields in the listbox, I get an error stating
that I must have the same number of fields defined on both the Master and
Child link fields (and I do and in the same order).

I did forget to mention that I have the listbox on on tab control and the
subform on another. Could this be the problem? If so, how can I define the
code to link them together?

 

[John Petty]

In short, I am trying to use the listbox to filter the data. If possible, I
am wanting to use this method throughout my app.

 

[Klatuu]

As I said before, you have to link the data in the subform to the data in the
master form using the fields in the tables. There is no way to link using
any kind of control.

You could filter the data, but depending how may rows are in your list box,
it may cause the form to appear slow.

Is this a multi select list box? If so, I can send you a function that will
return a string you can use for filtering (note, I have not used it for this
purpose, but it does work filtering a report)

 

[John Petty]

As far as the number of items that would be in the listbox at any one time,
maybe 25-30 items. (This list box is designed to show all active projects (we
have a quick throughput time. Signoff is the only bottleneck)).

Also, this is not a multiselect list box.

I will see how well my coding skills work and try to create a counter and a
DLookup filter.

 

[Klatuu]

You don't need a count or a dlookup. If it is a single select list box, you
need to set the subforms filter using the current value of the list box:
In the code below, please not that sfrmStuff is the name of the subform
control on the main form, not the name of the form being used in the subform
control. Also, all other names are made up:

If Not IsNull(Me.MyListBox) Then
Me.sfrmStuff.Form.Filter = "[Some Field] = '" & Me.MyListBox & "'"
Me.sfrmStuff.Form.FilterOn = True
End If

Now, if it happens the list box has multiple columns, you can use the list
box Column property to identify which column to use. Note the Column
property index starts at 0, so the first column in the list is .Column(0) and
the second is .Column(1), etc.

Me.sfrmStuff.Form.Filter = "[Some Field] = '" &
Me.MyListBox.Column(3) & "'"

 

[John Petty]

Dave,

What event do I place the 'Multi-Column" code into. I don't see a change
event for the Listbox or the fmFEAData form

Also, I need 2 of the listbox columns to match for the record. (i.e. Project
Name and Iteration since I can have up to 99 iterations for the same project)

You don't need a count or a dlookup. If it is a single select list box, you
need to set the subforms filter using the current value of the list box:
In the code below, please not that sfrmStuff is the name of the subform
control on the main form, not the name of the form being used in the subform
control. Also, all other names are made up:

If Not IsNull(Me.MyListBox) Then
Me.sfrmStuff.Form.Filter = "[Some Field] = '" & Me.MyListBox & "'"
Me.sfrmStuff.Form.FilterOn = True
End If

Now, if it happens the list box has multiple columns, you can use the list
box Column property to identify which column to use. Note the Column
property index starts at 0, so the first column in the list is .Column(0) and
the second is .Column(1), etc.

Me.sfrmStuff.Form.Filter = "[Some Field] = '" &
Me.MyListBox.Column(3) & "'"

--
Dave Hargis, Microsoft Access MVP

As far as the number of items that would be in the listbox at any one time,
maybe 25-30 items. (This list box is designed to show all active projects (we
have a quick throughput time. Signoff is the only bottleneck)).

Also, this is not a multiselect list box.

I will see how well my coding skills work and try to create a counter and a
DLookup filter.

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Dave,

What event do I place the 'Multi-Column" code into. I don't see a change
event for the Listbox or the fmFEAData form

Since I don't know how your form works, I can't say.

Also, I need 2 of the listbox columns to match for the record. (i.e. Project
Name and Iteration since I can have up to 99 iterations for the same project)

Is the Iteration a value in the child table? If it is not in the parent
table, then you would have to add code to whatever event you use to populate
the subform to filter on that value.

You don't need a count or a dlookup. If it is a single select list box, you
need to set the subforms filter using the current value of the list box:
In the code below, please not that sfrmStuff is the name of the subform
control on the main form, not the name of the form being used in the subform
control. Also, all other names are made up:

If Not IsNull(Me.MyListBox) Then
Me.sfrmStuff.Form.Filter = "[Some Field] = '" & Me.MyListBox & "'"
Me.sfrmStuff.Form.FilterOn = True
End If

Now, if it happens the list box has multiple columns, you can use the list
box Column property to identify which column to use. Note the Column
property index starts at 0, so the first column in the list is .Column(0) and
the second is .Column(1), etc.

Me.sfrmStuff.Form.Filter = "[Some Field] = '" &
Me.MyListBox.Column(3) & "'"

--
Dave Hargis, Microsoft Access MVP

As far as the number of items that would be in the listbox at any one time,
maybe 25-30 items. (This list box is designed to show all active projects (we
have a quick throughput time. Signoff is the only bottleneck)).

Also, this is not a multiselect list box.

I will see how well my coding skills work and try to create a counter and a
DLookup filter.

:

As I said before, you have to link the data in the subform to the data in the
master form using the fields in the tables. There is no way to link using
any kind of control.

You could filter the data, but depending how may rows are in your list box,
it may cause the form to appear slow.

Is this a multi select list box? If so, I can send you a function that will
return a string you can use for filtering (note, I have not used it for this
purpose, but it does work filtering a report)
--
Dave Hargis, Microsoft Access MVP


:

In short, I am trying to use the listbox to filter the data. If possible, I
am wanting to use this method throughout my app.

:

Dave,

Thanks for the response. I would have responded sooner but I had a calculus
class to attend.

That being said, I tried to set the master and child field links to tje
field names but to no avail. I get input boxes asking for the field
criteria, or when I link all fields in the listbox, I get an error stating
that I must have the same number of fields defined on both the Master and
Child link fields (and I do and in the same order).

I did forget to mention that I have the listbox on on tab control and the
subform on another. Could this be the problem? If so, how can I define the
code to link them together?


:

You don't use form controls in the link master and link child properties of
the subform control. Use the names of fields in the form and subform
recordsets.
--
Dave Hargis, Microsoft Access MVP


:

I have a listbox that is populated from a sub query (qyListFEA). I have set
the subform's LinkMasterField and LinkChildField to this listbox.

The list box contains 3 columns (Project Name, Project Number and Iteration).

When I select let's say Wheel 1, 5214, 1C, the subform only shows the Wheel
1 at iteration 1.

How can I get the data to synchronize so that if I select Wheel 1 at
iteration 1c or 2, etc. I can get the correct record to show?

Note: the sub query "qyListFEA" is based upon "qyFEA" which is where I need
the actual data from. If I use the master query in the listbox, I get a
bunch of ID's instead of the text.

Any help would be appreciated.

 

[Rick Brandt]

As I said before, you have to link the data in the subform to the
data in the master form using the fields in the tables. There is no
way to link using any kind of control.

That is actually only true of the ChildLink property. The MasterLink property
can be set to the name of a control, even an umbound control. In fact this is
what you are forced to do when the parent form is unbound (not your typical
scenario granted).

You just can't use the builder to make the setting, but have to type it into the
property box manually.

 

[John Petty]

Dave,

The iteration value is both a column in the Listbox and a textbox field in
the subform.

I can get the subform to populate based upon the Project Name from the
Listbox, but I can't seem to get the subform to acknowledge the correct
record (i.e. wheelName, Iteration).

I tried using DLookup("[FIteration]", "[qyFEA]", "[WheelName] =" &
lbWheels.Column(3)), (where qyFEA is the master query (from which the
qyListFEA is based upon, and lbWHeels is the Listbox),

But I get an error that the Column Object is being used incorrectly.

FListC = Forms!fmFormMaster.Controls!LbWheels

With FListC
If DLookup("WheelName", "qyFEA", "WheelName ='" & Me.Column(1) & "'") And _
DLookup("FIteration", "qyFEA", "WheelName ='" & Me.Column(1) & "'") Then

DoCmd.FindRecord , , acEntire
End If

and I tried the following filter code in the subform "Filter Event"

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
If Not IsNull(Forms!fmMasterForm!Controls.LbWheels) Then

Me.Filter = "[WheelName] ='" &
Forms!fmMasterForm!Controls.LbWheels.Column(3) & "'"

End If
End Sub

The same error comes up.

This is definitely user error, but I can't seem to debug it out.

--
Dave Hargis, Microsoft Access MVP

Dave,

What event do I place the 'Multi-Column" code into. I don't see a change
event for the Listbox or the fmFEAData form

Since I don't know how your form works, I can't say.

Also, I need 2 of the listbox columns to match for the record. (i.e. Project
Name and Iteration since I can have up to 99 iterations for the same project)

Is the Iteration a value in the child table? If it is not in the parent
table, then you would have to add code to whatever event you use to populate
the subform to filter on that value.

You don't need a count or a dlookup. If it is a single select list box, you
need to set the subforms filter using the current value of the list box:
In the code below, please not that sfrmStuff is the name of the subform
control on the main form, not the name of the form being used in the subform
control. Also, all other names are made up:

If Not IsNull(Me.MyListBox) Then
Me.sfrmStuff.Form.Filter = "[Some Field] = '" & Me.MyListBox & "'"
Me.sfrmStuff.Form.FilterOn = True
End If

Now, if it happens the list box has multiple columns, you can use the list
box Column property to identify which column to use. Note the Column
property index starts at 0, so the first column in the list is .Column(0) and
the second is .Column(1), etc.

Me.sfrmStuff.Form.Filter = "[Some Field] = '" &
Me.MyListBox.Column(3) & "'"

--
Dave Hargis, Microsoft Access MVP


:

As far as the number of items that would be in the listbox at any one time,
maybe 25-30 items. (This list box is designed to show all active projects (we
have a quick throughput time. Signoff is the only bottleneck)).

Also, this is not a multiselect list box.

I will see how well my coding skills work and try to create a counter and a
DLookup filter.

:

As I said before, you have to link the data in the subform to the data in the
master form using the fields in the tables. There is no way to link using
any kind of control.

You could filter the data, but depending how may rows are in your list box,
it may cause the form to appear slow.

Is this a multi select list box? If so, I can send you a function that will
return a string you can use for filtering (note, I have not used it for this
purpose, but it does work filtering a report)
--
Dave Hargis, Microsoft Access MVP


:

In short, I am trying to use the listbox to filter the data. If possible, I
am wanting to use this method throughout my app.

:

Dave,

Thanks for the response. I would have responded sooner but I had a calculus
class to attend.

That being said, I tried to set the master and child field links to tje
field names but to no avail. I get input boxes asking for the field
criteria, or when I link all fields in the listbox, I get an error stating
that I must have the same number of fields defined on both the Master and
Child link fields (and I do and in the same order).

I did forget to mention that I have the listbox on on tab control and the
subform on another. Could this be the problem? If so, how can I define the
code to link them together?


:

You don't use form controls in the link master and link child properties of
the subform control. Use the names of fields in the form and subform
recordsets.
--
Dave Hargis, Microsoft Access MVP


:

I have a listbox that is populated from a sub query (qyListFEA). I have set
the subform's LinkMasterField and LinkChildField to this listbox.

The list box contains 3 columns (Project Name, Project Number and Iteration).

When I select let's say Wheel 1, 5214, 1C, the subform only shows the Wheel
1 at iteration 1.

How can I get the data to synchronize so that if I select Wheel 1 at
iteration 1c or 2, etc. I can get the correct record to show?

Note: the sub query "qyListFEA" is based upon "qyFEA" which is where I need
the actual data from. If I use the master query in the listbox, I get a
bunch of ID's instead of the text.

Any help would be appreciated.

 

[Klatuu]

Thanks, Rick. I was not aware of that.

 

[John Petty]

Rick,

If I use the control "lbWheels" in the LinkMasterField object, how can I
access the 2 columns that I need to match for my subform to sychronize and
populate properly?

 

[Rick Brandt]

Rick,

If I use the control "lbWheels" in the LinkMasterField object, how
can I access the 2 columns that I need to match for my subform to
sychronize and populate properly?

If you need to refernec two field in the ChildLink property then you need to
have two references in the MasterLink property. If the two values are found in
tow different column of the ListBox then you can use...

lbWheels.column(0);lbWheels.column(1)