problem with subform based on combo box selection

I have a form with Listbox1, Listbox2, and subform1.
Listbox2's RowSource is conditioned on the selection made in Listbox1.
The subform is linked to Listbox2 (with the link Child and link Parent
properties).

When a selection is made in Listbox1, choices come up in Listbox2. When you
make a selection in Listbox2, the appropriate record comes up in the subform
and you can edit the data in the subform.

There are two problems that I can't figure out how to fix:
1) When you first go to an existing record on the main form, and you have
not yet made a selection in either listbox, a blank record is displayed in
the subform. The user can enter data in the subform, but shouldn't be able to
because this subrecord will not be associated with choices in the listboxes.
How is this problem usually handled - preventing the user from making these
extra subrecords, by mistake?

2) When I am in a record on the main form and choose an item in Listbox1 and
then choose an item in Listbox2, the correct subrecord appears in the suform.
But then when I go to a different record in the main form, while nothing has
yet been selected in Listbox1, and therefore Listbox2 remains blank, the
subform is still displaying the data from the last record that I was on in
the main form (The subform should be blank!). I tried in the ONCURRENT
property of the main form

[Subform1].Requery

but that did nothing. How can I correct this situation?

It seems like both problems may be related. Any help would be appreciated.

Thanks
Mark

Mark, I'm making these assumptions:
a) Your main form is unbound (and so its OnCurrent doesn't work.)
b) In the AfterUpdate event procedure of Listbox1, you assign the RowSource
of Listbox2.

To prevent the spurious record in the subform, cancel its BeforeInsert event
procedure if the main form's Listbox2 is null:
Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.Parent!Listbox2) Then
Cancel = True
MsgBox "Listbox selection first."
End If
End Sub

You might also open the subform's table in design view, select the foreign
key field (the one named in the subform linking to the listbox), and set its
Required property to Yes in the lower pane of table design.

Re your 2nd question, the unbound Listbox2 retains its value when its
RowSource changes. It might not show anything (if the bound column is not
the visible one), but the value is there. To prevent this, assign its value
as well as its RowSource.

This kind of thing:
Private Sub Listbox1_AfterUpdate()
Me.Listbox2 = Null
Me.Listbox2.RowSource = "SELECT ...
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"magicdds" wrote in message
...
I have a form with Listbox1, Listbox2, and subform1.
Listbox2's RowSource is conditioned on the selection made in Listbox1.
The subform is linked to Listbox2 (with the link Child and link Parent
properties).

When a selection is made in Listbox1, choices come up in Listbox2. When
you
make a selection in Listbox2, the appropriate record comes up in the
subform
and you can edit the data in the subform.

There are two problems that I can't figure out how to fix:
1) When you first go to an existing record on the main form, and you have
not yet made a selection in either listbox, a blank record is displayed in
the subform. The user can enter data in the subform, but shouldn't be able
to
because this subrecord will not be associated with choices in the
listboxes.
How is this problem usually handled - preventing the user from making
these
extra subrecords, by mistake?

2) When I am in a record on the main form and choose an item in Listbox1
and
then choose an item in Listbox2, the correct subrecord appears in the
suform.
But then when I go to a different record in the main form, while nothing
has
yet been selected in Listbox1, and therefore Listbox2 remains blank, the
subform is still displaying the data from the last record that I was on in
the main form (The subform should be blank!). I tried in the ONCURRENT
property of the main form

[Subform1].Requery

but that did nothing. How can I correct this situation?

It seems like both problems may be related. Any help would be appreciated.

Thanks
Mark

#1 Set the Subform's Allow Additions property to NO

#2 In the ON CURRENT event of the main form use:

Me.YourSubformCONTAINERNameHere.Form.Requery

Substituting YourSubformCONTAINERNameHere with the actual name of the
control that houses the subform on your main form (not the subform name,
although the container and subform can have the same name, but the container
name).

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"magicdds" wrote:

I have a form with Listbox1, Listbox2, and subform1.
Listbox2's RowSource is conditioned on the selection made in Listbox1.
The subform is linked to Listbox2 (with the link Child and link Parent
properties).

When a selection is made in Listbox1, choices come up in Listbox2. When you
make a selection in Listbox2, the appropriate record comes up in the subform
and you can edit the data in the subform.

There are two problems that I can't figure out how to fix:
1) When you first go to an existing record on the main form, and you have
not yet made a selection in either listbox, a blank record is displayed in
the subform. The user can enter data in the subform, but shouldn't be able to
because this subrecord will not be associated with choices in the listboxes.
How is this problem usually handled - preventing the user from making these
extra subrecords, by mistake?

2) When I am in a record on the main form and choose an item in Listbox1 and
then choose an item in Listbox2, the correct subrecord appears in the suform.
But then when I go to a different record in the main form, while nothing has
yet been selected in Listbox1, and therefore Listbox2 remains blank, the
subform is still displaying the data from the last record that I was on in
the main form (The subform should be blank!). I tried in the ONCURRENT
property of the main form

[Subform1].Requery

but that did nothing. How can I correct this situation?

It seems like both problems may be related. Any help would be appreciated.

Thanks
Mark

Allen

I tried both of your recommendations.

For problem #1, that worked perfectly.
For problem #2, since the main form was bound and the rowsource of listbox2
was set in the properties of listbox2, I put your suggested code in the
OnCurrent property of the main form

Me.Listbox2 = Null

and this resolved the problem.

As a test, I had first put a textbox on the main form with the control
source equal to listbox2 and you were right. When I changed to a new record
on the main form, that textbox still had the value of listbox2 from the
previous record!

So, both problems solved. Thanks for your help.

Mark



"Allen Browne" wrote:

Mark, I'm making these assumptions:
a) Your main form is unbound (and so its OnCurrent doesn't work.)
b) In the AfterUpdate event procedure of Listbox1, you assign the RowSource
of Listbox2.

To prevent the spurious record in the subform, cancel its BeforeInsert event
procedure if the main form's Listbox2 is null:
Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.Parent!Listbox2) Then
Cancel = True
MsgBox "Listbox selection first."
End If
End Sub

You might also open the subform's table in design view, select the foreign
key field (the one named in the subform linking to the listbox), and set its
Required property to Yes in the lower pane of table design.

Re your 2nd question, the unbound Listbox2 retains its value when its
RowSource changes. It might not show anything (if the bound column is not
the visible one), but the value is there. To prevent this, assign its value
as well as its RowSource.

This kind of thing:
Private Sub Listbox1_AfterUpdate()
Me.Listbox2 = Null
Me.Listbox2.RowSource = "SELECT ...
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"magicdds" wrote in message
...
I have a form with Listbox1, Listbox2, and subform1.
Listbox2's RowSource is conditioned on the selection made in Listbox1.
The subform is linked to Listbox2 (with the link Child and link Parent
properties).

When a selection is made in Listbox1, choices come up in Listbox2. When
you
make a selection in Listbox2, the appropriate record comes up in the
subform
and you can edit the data in the subform.

There are two problems that I can't figure out how to fix:
1) When you first go to an existing record on the main form, and you have
not yet made a selection in either listbox, a blank record is displayed in
the subform. The user can enter data in the subform, but shouldn't be able
to
because this subrecord will not be associated with choices in the
listboxes.
How is this problem usually handled - preventing the user from making
these
extra subrecords, by mistake?

2) When I am in a record on the main form and choose an item in Listbox1
and
then choose an item in Listbox2, the correct subrecord appears in the
suform.
But then when I go to a different record in the main form, while nothing
has
yet been selected in Listbox1, and therefore Listbox2 remains blank, the
subform is still displaying the data from the last record that I was on in
the main form (The subform should be blank!). I tried in the ONCURRENT
property of the main form

[Subform1].Requery

but that did nothing. How can I correct this situation?

It seems like both problems may be related. Any help would be appreciated.

Thanks
Mark