ListBox not updating on Subform

[TheStriker]

Hi,
I have a subform that gets it data from the main form. The main form consists of a combo box that updates the subforms on the main form with matching UniqueID (nSiteID), along with other subforms. The other subforms updates with no problem after the user selects a nSiteCode from the combo box. This particular subform has a list box that also gets its values from the combo box on the main form via the code below.

SELECT 'MyFields' FROM 'MyQuery'
WHERE "[nSiteID]= " & [Forms]![New]![nSiteID]

This works fine as it updates the listbox with the appropriate records corresponding with the 'nSiteID' selected in the combo box from the main form. What I would like it to do is, when the user selects from the list box, the subform will update with the appropriate record, much like with the combo box on the main form. When I do it in the subform itself, it works fine because I created the ListBox with the wizard for that purpose, however when I open the main form that has that same subform, it will not update when the user selects a record from the ListBox. Any suggestions would be most appreciated.

Happy New Year!!

 

[bpurser]

One assumes that you have the child/master fields set for the subform/mainform?

Are you trying to update the field to which the listbox is bound with something that's not in the new listbox rowsource, and if so, is Limit to List set to no?

After changing the listbox rowsource, do you requery it?

Lastly, are you sure that the listbox is bound to the correct control?

HTH
Ben

 

[TheStriker]

Hey bpurser,

Thanks for your response. The subforms were created by the wizard so the child/master fields were created successfully. The listbox in question 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[/blue] 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

 

[bpurser]

Yeah, I'm not sure you can use the recordsetclone on a subform. modify your code to read as follows, and see if the message box has a positive number in it...

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

Dim rs As Object

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

End Sub

If it doesn't error out, then try using the On click event, AND if SiteID is a number, then don't convert the listbox value to a string...

HTH
Ben

 

[TheStriker]

bpurser,

I tried your test method and it returned a value of 33. When I inserted your method into the OnClick event and opened the main form and made a selection from the listbox on the subform, it still didn't update. I've tried populating the fields using the listbox ColumnCount via:

Me!Form!MySubform.Form![MyField] = Me.Listbox.Column(1)

This works for the date/time and number fields but not the string/text fields. Any suggestions?