Using a listbox in a subform

[Nick Mirro]

Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record that matches the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the record of its subform,
and all children subforms should be filtered to that listbox selection.
(For some reason, this only works if I break the Master/Child link to the
parent form)

2. When the parentmost form is cycled, the recordset in the listbox
should reflect only the contacts for the organization being displayed (in
the parent form).

If I break the Master/Child link from parent form to the first subform, the
listbox works, but of course, cycling the parent form will not change the
listbox. If I leave the link in place, then the listbox becomes
deactivated.

Thanks,

Nick

 

[Dan Artuso]

Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called it)
=Forms!Parentform!OrgId

You would then requery the list box in the current event of the
Parentform.

 

[Dan Artuso]

Hi,
It is possible to call the listboxes's AfterUpdate event from your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

 

[Nick Mirro]

Well clicking a row in the listbox (housed in the subform) doesn't cause the
child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Dan Artuso]

Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no problem.
Is ContactId a string or number? I notice you're using Str to convert the
listbox value to a string. If it is a string, you need to delimit your
criteria
with quotes.
Have you stepped through the code to check the values of the variables?

--
HTH
Dan Artuso, Access MVP

Well clicking a row in the listbox (housed in the subform) doesn't cause the
child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Nick Mirro]

Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not sure how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no problem.
Is ContactId a string or number? I notice you're using Str to convert the
listbox value to a string. If it is a string, you need to delimit your
criteria
with quotes.
Have you stepped through the code to check the values of the variables?

--
HTH
Dan Artuso, Access MVP

Well clicking a row in the listbox (housed in the subform) doesn't cause the
child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Hi,
It is possible to call the listboxes's AfterUpdate event from your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception. The
listbox
updates as I cycle the parent form, but the listboxes afterupdate event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing parent form,
while the subform housing the listbox gets updated by the row

selected
in

the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called it)
=Forms!Parentform!OrgId

You would then requery the list box in the current event of the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the record of its
subform,
and all children subforms should be filtered to that listbox
selection.
(For some reason, this only works if I break the Master/Child

link
to

the
parent form)

2. When the parentmost form is cycled, the recordset in the listbox
should reflect only the contacts for the organization being displayed
(in
the parent form).

If I break the Master/Child link from parent form to the first
subform,
the
listbox works, but of course, cycling the parent form will not change
the
listbox. If I leave the link in place, then the listbox becomes
deactivated.

Thanks,

Nick

 

[Dan Artuso]

Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP

Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not sure how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no problem.
Is ContactId a string or number? I notice you're using Str to convert the
listbox value to a string. If it is a string, you need to delimit your
criteria
with quotes.
Have you stepped through the code to check the values of the variables?

--
HTH
Dan Artuso, Access MVP

Well clicking a row in the listbox (housed in the subform) doesn't

cause
the

child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event from your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception. The
listbox
updates as I cycle the parent form, but the listboxes afterupdate event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing parent
form,
while the subform housing the listbox gets updated by the row selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called it)
=Forms!Parentform!OrgId

You would then requery the list box in the current event of the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the record of its
subform,
and all children subforms should be filtered to that listbox
selection.
(For some reason, this only works if I break the Master/Child link
to
the
parent form)

2. When the parentmost form is cycled, the recordset in the
listbox
should reflect only the contacts for the organization being
displayed
(in
the parent form).

If I break the Master/Child link from parent form to the first
subform,
the
listbox works, but of course, cycling the parent form will not
change
the
listbox. If I leave the link in place, then the listbox becomes
deactivated.

Thanks,

Nick

 

[Dan Artuso]

Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP

It showed "Nothing" as the value. Hope this helps.

Nick

Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP

Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not

sure
how

to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no
problem.
Is ContactId a string or number? I notice you're using Str to

convert
the

listbox value to a string. If it is a string, you need to delimit your
criteria
with quotes.
Have you stepped through the code to check the values of the variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform) doesn't cause
the
child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event from your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception. The
listbox
updates as I cycle the parent form, but the listboxes afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing parent
form,
while the subform housing the listbox gets updated by the row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called it)
=Forms!Parentform!OrgId

You would then requery the list box in the current event of the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the record

of
its

subform,
and all children subforms should be filtered to that listbox
selection.
(For some reason, this only works if I break the Master/Child
link
to
the
parent form)

2. When the parentmost form is cycled, the recordset in the
listbox
should reflect only the contacts for the organization being
displayed
(in
the parent form).

If I break the Master/Child link from parent form to the first
subform,
the
listbox works, but of course, cycling the parent form will not
change
the
listbox. If I leave the link in place, then the listbox becomes
deactivated.

Thanks,

Nick

 

[Nick Mirro]

Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.

Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP

It showed "Nothing" as the value. Hope this helps.

Nick

Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no
problem.
Is ContactId a string or number? I notice you're using Str to convert
the
listbox value to a string. If it is a string, you need to delimit your
criteria
with quotes.
Have you stepped through the code to check the values of the variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform) doesn't
cause
the
child forms to update. This seems to be the case when there is a
Master/Child link between the subform housing the listbox, and the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event from your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception.
The
listbox
updates as I cycle the parent form, but the listboxes afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing
parent
form,
while the subform housing the listbox gets updated by the row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called it)
=Forms!Parentform!OrgId

You would then requery the list box in the current event

of
the

Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record
that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the

record

of in
the will
not

 

[Dan Artuso]

Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP

Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.

Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP

It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should be no
problem.
Is ContactId a string or number? I notice you're using Str to convert
the
listbox value to a string. If it is a string, you need to

delimit
your

criteria
with quotes.
Have you stepped through the code to check the values of the
variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform) doesn't
cause
the
child forms to update. This seems to be the case when there

is

a

Master/Child link between the subform housing the listbox, and the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event

from
your

main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing
parent
form,
while the subform housing the listbox gets updated by the row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you

called
it)

=Forms!Parentform!OrgId

You would then requery the list box in the current event of
the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record
that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the

record

of
its
subform,
and all children subforms should be filtered to that listbox
selection.
(For some reason, this only works if I break the
Master/Child
link
to
the
parent form)

2. When the parentmost form is cycled, the

recordset

in

 

[Nick Mirro]

Thanks for being patient with me. I had to rem out
"Form_sbfrmContactInfo.ContactsList_AfterUpdate" in the parent form
oncurrent event or else I got an invalid use of null error.

It flashed the OrganizationID autonumber value of 1. As before, when I
broke the masterchild link, cycling the parent form would show the correct
contacts in the listbox for the current organization - and the popup would
show the correct number, but...

It seems that the listbox will update successfully if the master/child link
is broken, but only one time - after a parent form record change
(organization). A second attempt to change the contact does not respond.

Nick

Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP

Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.

Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP


It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate code was
generated by the wizard. I have little knowledge of vb and am not
sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's recordset,
which I assume from your previous post they are, there should

be
no

problem.
Is ContactId a string or number? I notice you're using Str to
convert
the
listbox value to a string. If it is a string, you need to delimit
your
criteria
with quotes.
Have you stepped through the code to check the values of the
variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform) doesn't
cause
the
child forms to update. This seems to be the case when there

is

a
Master/Child link between the subform housing the listbox,

and
the

parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &

Str(Nz(Me![ContactsList],
0))

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event from
your
main form. First, you have to go into the subform's code module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing
parent
form,
while the subform housing the listbox gets updated by

the
row

selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based on a
query with a criteria on the OrgId (or whatever you called
it)
=Forms!Parentform!OrgId

You would then requery the list box in the current

event
of

the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find the record
that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the record
of
its
subform,
and all children subforms should be filtered to that
listbox
selection.
(For some reason, this only works if I break the
Master/Child
link
to
the
parent form)

2. When the parentmost form is cycled, the

recordset

in
the
listbox
should reflect only the contacts for the organization
being
displayed
(in
the parent form).

If I break the Master/Child link from parent form to the
first
subform,
the
listbox works, but of course, cycling the parent

form
will

not
change
the
listbox. If I leave the link in place, then the listbox
becomes
deactivated.

Thanks,

Nick

 

[Dan Artuso]

Okay, let me get this straight.
Your parent form and subform are linked by OrganizationID.
You have the listbox filtered to display only those contacts who
are in the Organization that is displayed on the parent form.

Now, the value that should be displayed in your message box should
be the ContactId, not the OrganizationID because what you're searching on is
Contact, not Organization.

If all the above conditions are met, it will function correctly.

--
HTH
Dan Artuso, Access MVP

Thanks for being patient with me. I had to rem out
"Form_sbfrmContactInfo.ContactsList_AfterUpdate" in the parent form
oncurrent event or else I got an invalid use of null error.

It flashed the OrganizationID autonumber value of 1. As before, when I
broke the masterchild link, cycling the parent form would show the correct
contacts in the listbox for the current organization - and the popup would
show the correct number, but...

It seems that the listbox will update successfully if the master/child link
is broken, but only one time - after a parent form record change
(organization). A second attempt to change the contact does not respond.

Nick

Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP

Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.


Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP


It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate

code
was

generated by the wizard. I have little knowledge of vb and am not
sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's
recordset,
which I assume from your previous post they are, there

should

be

no
problem.
Is ContactId a string or number? I notice you're using Str to
convert
the
listbox value to a string. If it is a string, you need to delimit
your
criteria
with quotes.
Have you stepped through the code to check the values of the
variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform)
doesn't
cause
the
child forms to update. This seems to be the case when

there
is

a
Master/Child link between the subform housing the listbox, and
the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event from
your
main form. First, you have to go into the subform's code
module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one
exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the changing
parent
form,
while the subform housing the listbox gets updated by the
row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be based

on

a to
the

 

[Nick Mirro]

The listbox is filtered based on this criteria:
[Forms]![frmDataExchange]![OrgId]

There is a good overview in the newer post. Sorry I thought I'd used up
your patience. I'm being called away for about 4 hours. back by 4pm est.
Please hang in there with me on this.

Nick

Okay, let me get this straight.
Your parent form and subform are linked by OrganizationID.
You have the listbox filtered to display only those contacts who
are in the Organization that is displayed on the parent form.

Now, the value that should be displayed in your message box should
be the ContactId, not the OrganizationID because what you're searching on is
Contact, not Organization.

If all the above conditions are met, it will function correctly.

--
HTH
Dan Artuso, Access MVP

Thanks for being patient with me. I had to rem out
"Form_sbfrmContactInfo.ContactsList_AfterUpdate" in the parent form
oncurrent event or else I got an invalid use of null error.

It flashed the OrganizationID autonumber value of 1. As before, when I
broke the masterchild link, cycling the parent form would show the correct
contacts in the listbox for the current organization - and the popup would
show the correct number, but...

It seems that the listbox will update successfully if the master/child link
is broken, but only one time - after a parent form record change
(organization). A second attempt to change the contact does not respond.

Nick

Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP


Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.


Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected value'

--
HTH
Dan Artuso, Access MVP


It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate code
was
generated by the wizard. I have little knowledge of vb and

am
not

sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's
recordset,
which I assume from your previous post they are, there

should

be
no
problem.
Is ContactId a string or number? I notice you're using Str to
convert
the
listbox value to a string. If it is a string, you need to
delimit
your
criteria
with quotes.
Have you stepped through the code to check the values of the
variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform)
doesn't
cause
the
child forms to update. This seems to be the case when there
is
a
Master/Child link between the subform housing the

listbox,
and

the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event
from
your
main form. First, you have to go into the subform's code
module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one
exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the
changing
parent
form,
while the subform housing the listbox gets updated

by
the

row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be

based

on

form

to

 

[Dan Artuso]

Hi,
I have endless patience

I think the problem is that the bound column of your listbox should
be ContactId. It appears that OrganizationID is presently your bound column.
I'm not sure of your knowledge level so post back if you
don't know what that means.

--
HTH
Dan Artuso, Access MVP

The listbox is filtered based on this criteria:
[Forms]![frmDataExchange]![OrgId]

There is a good overview in the newer post. Sorry I thought I'd used up
your patience. I'm being called away for about 4 hours. back by 4pm est.
Please hang in there with me on this.

Nick

Okay, let me get this straight.
Your parent form and subform are linked by OrganizationID.
You have the listbox filtered to display only those contacts who
are in the Organization that is displayed on the parent form.

Now, the value that should be displayed in your message box should
be the ContactId, not the OrganizationID because what you're searching

on

is
Contact, not Organization.

If all the above conditions are met, it will function correctly.

--
HTH
Dan Artuso, Access MVP

Thanks for being patient with me. I had to rem out
"Form_sbfrmContactInfo.ContactsList_AfterUpdate" in the parent form
oncurrent event or else I got an invalid use of null error.

It flashed the OrganizationID autonumber value of 1. As before, when I
broke the masterchild link, cycling the parent form would show the correct
contacts in the listbox for the current organization - and the popup would
show the correct number, but...

It seems that the listbox will update successfully if the master/child link
is broken, but only one time - after a parent form record change
(organization). A second attempt to change the contact does not respond.

Nick



Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP


Well this time I selected one of the rows and the same result occurred.
Multi is set to none. I'm not sure what that means.


Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected
value'

--
HTH
Dan Artuso, Access MVP


It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this
line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The

afterupdate
code

was
generated by the wizard. I have little knowledge of vb

and

am

not
sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's
recordset,
which I assume from your previous post they are, there should
be
no
problem.
Is ContactId a string or number? I notice you're using

Str
to

convert
the
listbox value to a string. If it is a string, you need to
delimit
your
criteria
with quotes.
Have you stepped through the code to check the values of the
variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform)
doesn't
cause
the
child forms to update. This seems to be the case when there
is
a
Master/Child link between the subform housing the listbox,
and
the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event
from
your
main form. First, you have to go into the subform's code
module
and make the event Public. Just change Private to public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one
exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is disabled.

Is it possible for the listbox to be updated by the
changing
parent
form,
while the subform housing the listbox gets updated by
the
row
selected
in
the listbox?

Nick



Hi,
The Row Source for the combo would have to be

based

on
a
query with a criteria on the OrgId (or whatever you
called
it)
=Forms!Parentform!OrgId

You would then requery the list box in the current
event
of
the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to

"Find
the

record
that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is selected, the
record
of
its
subform,
and all children subforms should be filtered

to
that

listbox
selection.
(For some reason, this only works if I break the
Master/Child
link
to
the
parent form)

2. When the parentmost form is cycled, the
recordset
in
the
listbox
should reflect only the contacts for the
organization
being
displayed
(in
the parent form).

If I break the Master/Child link from parent

form

to
the
first
subform,
the
listbox works, but of course, cycling the parent
form
will
not
change
the
listbox. If I leave the link in place, then the
listbox
becomes
deactivated.

Thanks,

Nick

 

[Nick Mirro]

Well you'd be surprised how little I know. Turns out it was the bound
column, which was just as you said. I guess I selected the wrong one in the
wizard. Thanks for taking the time to go through this. This project is
important to me and I'm thrilled that I can include this type of
functionality now. Thanks!

Nick

Hi,
I have endless patience

I think the problem is that the bound column of your listbox should
be ContactId. It appears that OrganizationID is presently your bound column.
I'm not sure of your knowledge level so post back if you
don't know what that means.

--
HTH
Dan Artuso, Access MVP

The listbox is filtered based on this criteria:
[Forms]![frmDataExchange]![OrgId]

There is a good overview in the newer post. Sorry I thought I'd used up
your patience. I'm being called away for about 4 hours. back by 4pm est.
Please hang in there with me on this.

Nick

Okay, let me get this straight.
Your parent form and subform are linked by OrganizationID.
You have the listbox filtered to display only those contacts who
are in the Organization that is displayed on the parent form.

Now, the value that should be displayed in your message box should
be the ContactId, not the OrganizationID because what you're searching

on

is
Contact, not Organization.

If all the above conditions are met, it will function correctly.

--
HTH
Dan Artuso, Access MVP


Thanks for being patient with me. I had to rem out
"Form_sbfrmContactInfo.ContactsList_AfterUpdate" in the parent form
oncurrent event or else I got an invalid use of null error.

It flashed the OrganizationID autonumber value of 1. As before,

when

I

broke the masterchild link, cycling the parent form would show the correct
contacts in the listbox for the current organization - and the popup would
show the correct number, but...

It seems that the listbox will update successfully if the master/child
link
is broken, but only one time - after a parent form record change
(organization). A second attempt to change the contact does not respond.

Nick



Okay, one more try.
Put this line of code in the event:
MsgBox Me.ContactsList

ContactsList is the name of the listbox?

Select a value and you should see the message box displaying
the value you selected.

--
HTH
Dan Artuso, Access MVP


Well this time I selected one of the rows and the same result
occurred.
Multi is set to none. I'm not sure what that means.


Hi Nick,
Two things:
Has a selection been made in the listbox?
The list box must not be Multi-Select

Those are the only two reasons to cause there to be no 'selected
value'

--
HTH
Dan Artuso, Access MVP


It showed "Nothing" as the value. Hope this helps.

Nick



Hi Nick,
Okay, lets try changing some of the code.
Change:
rs.FindFirst "[ContactID] = " & Str(Nz(Me![ContactsList], 0))
To:
rs.FindFirst "[ContactID] = " & Nz(Me![ContactsList], 0)

Now, in the vb editor, click in the left 'margin' beside this
line:
Set rs = Me.Recordset.Clone
You should see a big 'dot' appear, this is a breakpoint.
When the event fires, you'll be taken into the editor.
You then press F8 to step through the code.
If you place your mouse over [ContactsList],
you should see the value of it.
Check to see if it's a valid value.

--
HTH
Dan Artuso, Access MVP


Hi Dan. ContactID is an autonumber field. The afterupdate
code
was
generated by the wizard. I have little knowledge of vb

and

am
not
sure
how
to walk through code. Here is a summary of what I've done:


[frmDataExchange - Master]

Public Sub Form_Current()

Me![sbfrmContactInfo].Form![ContactsList].Requery
Form_sbfrmContactInfo.ContactsList_AfterUpdate

End Sub



[sbfrmContactInfo - Child]

[ContactsList - listbox]

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
As long as the contacts in the listbox are in the form's
recordset,
which I assume from your previous post they are, there
should
be
no
problem.
Is ContactId a string or number? I notice you're using Str
to
convert
the
listbox value to a string. If it is a string, you need to
delimit
your
criteria
with quotes.
Have you stepped through the code to check the values

of
the

variables?

--
HTH
Dan Artuso, Access MVP


Well clicking a row in the listbox (housed in the subform)
doesn't
cause
the
child forms to update. This seems to be the case when
there
is
a
Master/Child link between the subform housing the listbox,
and
the
parent
form.

Nick

Here is the after update procedure for the listbox:

Public Sub ContactsList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " &
Str(Nz(Me![ContactsList],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Hi,
It is possible to call the listboxes's AfterUpdate event
from
your
main form. First, you have to go into the

subform's
code

module
and make the event Public. Just change Private to
public.
Then in the Current even of your main form put:
Form_frmCat.List5_AfterUpdate

Substitute your names.
My form name is frmCat and the listbox is List5

--
HTH
Dan Artuso, Access MVP


Hi Dan. Thanks for this info. This works with one
exception.
The
listbox
updates as I cycle the parent form, but the listboxes
afterupdate
event
"Find the record that matches the control" is
disabled.

Is it possible for the listbox to be updated by the
changing
parent
form,
while the subform housing the listbox gets

updated
by

the
row
selected
in
the listbox?

Nick



message
Hi,
The Row Source for the combo would have to be based
on
a
query with a criteria on the OrgId (or

whatever
you

called
it)
=Forms!Parentform!OrgId

You would then requery the list box in the current
event
of
the
Parentform.

--
HTH
Dan Artuso, Access MVP


Can't seem to solve this.

Parentform = Organizations (one)
1st subform = Contacts (many)
Listbox = Contacts


The listbox resides in the first child subform.

The listbox's afterupdate event is set to "Find
the
record
that
matches
the
control" (via wizard).


What I want:

1 When a record in the listbox is

selected,
the

record
of
its
subform,
and all children subforms should be filtered to
that
listbox
selection.
(For some reason, this only works if I break the
Master/Child
link
to
the
parent form)

2. When the parentmost form is cycled, the
recordset
in
the
listbox
should reflect only the contacts for the
organization
being
displayed
(in
the parent form).

If I break the Master/Child link from parent form
to
the
first
subform,
the
listbox works, but of course, cycling the parent
form
will
not
change
the
listbox. If I leave the link in place, then the
listbox
becomes
deactivated.

Thanks,

Nick