List box values not changing on sub-form

[JWOL]

Hi,

I have a list box on a sub-form which has a query as its row source.
What I am trying to do is have the list box display child records that
are linked to their parent record which is displayed on a main form.
So the main form, for example, contains a person's name and address
then the sub-form displays the details of one of their children. The
list box should display details of all of their children and if one of
the records in the list box is clicked on, the details should then be
displayed in the sub-form. I have managed to get the child record
displayed in the list box but as I move through the parent records on
the main form, the values in the list box do not change. It just
shows the first record in the child table and then never changes.

I've tried a couple of different queries but I'm not sure that the
query is the problem. Could there be something in the list box
properties that needs setting? I'm fairly new to Access so I could be
missing something really obvious but it's not obvious to me. These
are the queries that I've tried so far:

SELECT field1, field2 FROM tableA JOIN tableB ON tableA.ID = tableB.ID
WHERE forms!mainform!ctrl_ID = forms!subform!ctrl_ID

(the above one causes a popup box to appear asking for a value for
forms!subform!ctrl_ID)

SELECT field1, field2 FROM tableA JOIN tableB ON tableA.ID = tableB.ID
WHERE forms!mainform!ctrl_ID = tableB.ID

(the above query is the one that just shows one record in the list box
and then it never changes)

I've made the two ID controls on the main form and the sub-form
visible so that I can see the values are changing as the records
change. The first query seems correct to me but the popup box means
that it probably isn't.

Any help appreciated.

 

[Douglas J. Steele]

Try issuing a Requery whenever you change records on the main form.

In the form's Current event, you'll want something like:

Private Sub Form_Current()

Me!SubformControl.Form!ListboxOnSubform.Requery

End Sub

Note that depending how you added the subform to the main form, the name of
the subform control on the parent form may not be the same as the name of
the form being used as a subform.

 

[JWOL]

Try issuing a Requery whenever you change records on the main form.

In the form's Current event, you'll want something like:

Private Sub Form_Current()

Me!SubformControl.Form!ListboxOnSubform.Requery

End Sub

Note that depending how you added the subform to the main form, the name of
the subform control on the parent form may not be the same as the name of
the form being used as a subform.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)














- Show quoted text -

Thank you so much! That was exactly it. I wish I'd just posted on
here straight away instead of stubbornly working at it for half a
day. Two lessons learned then.

 

[JWOL]

Try issuing a Requery whenever you change records on the main form.

In the form's Current event, you'll want something like:

Private Sub Form_Current()

Me!SubformControl.Form!ListboxOnSubform.Requery

End Sub

Note that depending how you added the subform to the main form, the name of
the subform control on the parent form may not be the same as the name of
the form being used as a subform.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)














- Show quoted text -

By the way, how do I then get the sub-form to show the new values of a
record selected in the list box?

 

[Douglas J. Steele]

Sorry, I'm not sure I understand what you're asking.

Are you saying that you want the subform populated with details about the
item selected in the list box that's on the subform? You put code in the
list box's AfterUpdate event to take values from the list box and populate
text boxes on the form.

Private Sub MyListbox_AfterUpdate()

Me.Text1 = Me.MyListbox.Column(1)
Me.Text2 = Me.MyListbox.Column(2)

End Sub

That code would take the value in the 2nd column of the currently selected
row in the list box and put in into a control named Text1 on the same form,
and the value in the 3rd column and put it into a control named Text2. (The
Column collection starts numbering at 0. This technique will only work if
the list box's MultiSelect property is set to None.)

 

[JWOL]

Sorry, I'm not sure I understand what you're asking.

Are you saying that you want the subform populated with details about the
item selected in the list box that's on the subform? You put code in the
list box's AfterUpdate event to take values from the list box and populate
text boxes on the form.

Private Sub MyListbox_AfterUpdate()

Me.Text1 = Me.MyListbox.Column(1)
Me.Text2 = Me.MyListbox.Column(2)

End Sub

That code would take the value in the 2nd column of the currently selected
row in the list box and put in into a control named Text1 on the same form,
and the value in the 3rd column and put it into a control named Text2. (The
Column collection starts numbering at 0. This technique will only work if
the list box's MultiSelect property is set to None.)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -

Thanks again, we're getting there. Only problem now is that the
actual underlying values in the table are changing too. So I can
click on a row in the list box and the sub-form values change but the
actual data is also changing. Is there a property I should be setting
somewhere to prevent this?

Thanks.

 

[Douglas J. Steele]

Since the data that you're getting from the listbox already exists
elsewhere, there's no reason you should be storing it redundantly. In other
words, those fields shouldn't be bound. Unfortunately, when you put a value
in an unbound text box, it'll put that value in that text box on each row.

 

[JWOL]

Since the data that you're getting from the listbox already exists
elsewhere, there's no reason you should be storing it redundantly. In other
words, those fields shouldn't be bound. Unfortunately, when you put a value
in an unbound text box, it'll put that value in that text box on each row.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -

It isn't being stored redundantly. The listbox says "unbound" when
the form is in design mode. I'm sure this must be possible so I guess
I'm doing something wrong.