ListBox not updating on subform

[TheStriker]

Hi,

I am using Access 2000 and I am having a bit of trouble trying to update a subform when the user selects an option from the listbox. The subforms were created by the wizard so the child/master fields were created successfully. The listbox was also created by the wizard with the option of:

'Find a record on my form based on the value I selected in my list box'

This option makes the listbox unbound and produces the following code in VBA:

***********************************************************
Private Sub ListBox_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SiteID] = " & Str(Me![List2])
Me.Bookmark = rs.Bookmark

End Sub

***********************************************************

I've also inserted the 'Me.Form.Requery' or 'Me.[ListBox].Requery' procedure in the On Current Event of the subform.

What I am trying to accomplish is to update the subform on which the listbox resides once the user selects a record from the listbox. The fields on the subform will populate corresponding with the selected record. The code produced by the wizard is sufficient for that when viewed in the subform itself, however when I open the main form and select from the same listbox, the subform won't update. I've even tried to requery/refresh/repaint the main form but to no avail. I hope this is enough information to provide a suggestion. Thanks in advance.

TheStriker

 

[WayneRyan]

Striker,

I don't use the Wizard for this, but I don't think
that it is going to "reach out" and alter the
values on your suborm.

You can use the Double-Click event (or something
else) on your listbox to do:

Forms![YourForm]![YourSubform].Form![SomeField] = Me.ListBox.Column(3)

Note that the subscripts for the listbox start
at 0. The second subscript is (1).

hth,
Wayne

 

[TheStriker]

WayneRyan,

Thank you for your timely response. It seems that your solution will work nicely. However, when I used the OnClick event of the listbox, I get the following error:

Cannot add record(s); join key of table 'MyTable' not in recordset.

Any suggestions?
Thanks in advance

 

[WayneRyan]

Striker,

I didn't think that you were adding records. It's
my understanding that the message is telling you
that your sub-form does not have a required key
field present (on the form OR the query that
populates it). This should be in both, it doesn't
have to be visible, and don't let the users have
the ability to change it. It can't put the record in
the table if its missing a required key; and Access
won't "guess" at a value.

Wayne

 

[TheStriker]

WayneRyan,

I've inserted the join field on the main form AND on the subform AND it is listed on the listbox. Am I missing something?

 

[WayneRyan]

Striker,

I would guess that the subform is based on a query and the
key field is not in the query. Does it appear in the drop-down
list when you have your sub-form in design view?

Wayne

 

[TheStriker]

Yeah, I've been having alot of 'unexplainable' issues with this subform. This is one of many of it's type and when I tried your suggestion on other subforms of it's type and it worked like a charm. However the fields would only reflect the first record the user selected and will not update with each selection if the user selects another record. Also, it is adding records to the recordset which I did not want to happen. So now when a user clicks a selection, it is added to the recordset creating duplicates in the underlying query. I need to know how to stop adding records when a selection is clicked. I will probably spend the better part of today trying to figure out why it's doing that...unless you have any ideas. Also, does it matter if some of the fields are string/text because I notice that the updates are happening to number fields and not text fields.

TheStriker

 

[TheStriker]

Nevermind, I deleted and re-inserted the control that wasn't updating and now it works. Thanks for your help!

Life is good now.