Addressing a control on a form from another form
Hey folks,
I'm designing a form with 2 (nested) subforms on it. I need to clear the contents of the listboxes on the subforms from the main form using VBA before I populate them - is there a way to do this?
The way the form is structured right now is, the main form populates its own listbox, then creates a query which the subform uses to later populate its own listbox.
Simple diagram: Form1(Form2(Form3)))
The code used to create the query in Form1:
Set qry = dbs.CreateQueryDef("qryConSelect", "SELECT tblConsultants.ConsultantName, tblConsultants.Phone, tblConsultants.RelationshipGrade, tblConsultants.Tiering " & _
"FROM tblConsultants WHERE (((tblConsultants.CallerID)= " & Me![CallerID] & "));")
This code then runs on Form2 to populate its listbox (this runs in Form_Current):
checkFlag = False
For Each qdf In dbs.QueryDefs
If qdf.Name = "qryConSelect" Then checkFlag = True
Next qdf
If checkFlag = True Then
Set rst = dbs.OpenRecordset("qryConSelect")
MSListBox21.Clear
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
For i = 1 To rst.RecordCount
MSListBox21.AddItem CStr(rst![ConsultantName])
rst.MoveNext
Next
End If
End If
The form then makes another query which its subform in turn uses to populate its listbox.
This works well, but the problem is that when the user clicks on the very top list box, the ones underneath are already populated. They clear & repopulate when clicked, but until they are they display misleading data. Ideally, I'd like to be able to clear MSListBox21 right when I make thge very first query, in Form1, but I can't access it as it's not a control on it. Is there a way to access a control on another form from within VBA to fix this?
Otherwise, any suggestions on where to stick "MSListBox21.Clear" on the subform?
|