If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
problem with subform based on combo box selection
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 |
#3
|
|||
|
|||
problem with subform based on combo box selection
#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 |
#4
|
|||
|
|||
problem with subform based on combo box selection
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 |
Thread Tools | |
Display Modes | |
|
|