Why Run time error 2452 for Select statement

[manusri]

hi everyone,
I have a Access 2010 database with multiple unbound form and subforms. One subform has a list box that lists all personal name and role. the personal information is stored on the same subform itself.image is attahced.

I have been wondering and trying to fix the Run-Time Error 2452 : "The expression you entered has an invalid reference to the Parent property."

This is the code for the Open statement of the form.

Private Sub Form_Open(Cancel As Integer)

    Me.author.Value = ""
    Me.AuthorRole.Value = ""
    Me.fix.Value = ""
    Me.ORole.Value = ""
    Me.Condition.Value = ""
    Me.othrcondition.Value = ""
    Me.email.Value = ""
    Me.phone.Value = ""
    Me.Fx.Value = ""
    Me.CmbTest.Value = ""
    Me.chkCurrent.Value = ""
    Me.txtAction.Value = ""
        
 
    Dim db As DAO.Database
    Dim rcount As DAO.Recordset
    Dim rs As DAO.Recordset
    
    Dim SQLCount As String
    Dim SQLStr As String
    Dim Records As String
    
    Dim Count As Integer
    Dim i As Integer
    'Dim rsName As String
    'Dim rsRole As String
    'Dim rsOthRole As String
   
     
    SQLCount = "SELECT Count(*) FROM Contacts WHERE SiteID = " & Me.Parent!NewSiteID.Value
        
    Set db = CurrentDb
    Set rcount = db.OpenRecordset(SQLCount, dbOpenDynaset)
                
    rcount.MoveFirst
    
    Count = rcount.Fields(0).Value
    rcount.Close
    Set rcount = Nothing
    
     ' recordset
     
    SQLStr = "SELECT Contacts.ContactID, Contacts.Name, Contacts.Role, Contacts.Suff, Contacts.OthRole, Contacts.Speciality, Contacts.SpecialityOther, Contacts.Email, Contacts.Phone, Contacts.Fax, Contacts.Test, Contacts.IsCurrent" & _
        " FROM Contacts WHERE Contacts.SiteID = " & Me.Parent!NewSiteID.Value
        
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
     
    Me.LstAuthors.RowSourceType = "Value List"

    If Count = 0 Then
        Me.LstAuthors.RowSource = ""
    Else
        
        rs.MoveFirst
        Do Until rs.EOF
        
            For i = 0 To Count - 1
                With Me.LstAuthors
                                  
                    .RowSourceType = "Value List"
                    .ColumnCount = 4
                    .AddItem rs![ContactID] & ";" & rs![Name] & ";" & rs![Role] & ";" & rs![OthRole], Index:=i
                    
                    'If IsNull(rs![Name]) = True Then rsName = "N/A" Else rsName = rs![Name]
                    'If IsNull(rs![Role]) = True Then rsRole = "N/A" Else rsRole = rs![Role]
                    'If IsNull(rs![OthRole]) = True Then rsOthRole = "N/A" Else rsOthRole = rs![OthRole]
                                        
                    'take care of cases when people use quotation marks in their memo
                    
                    ' strReturn = Replace(rsNote, "'", "''")
                    ' strReturn = "'" & strReturn & "'"
                End With
                
        rs.MoveNext
        Next i
        Loop
    End If

    
    rs.Close
    Set rs = Nothing
   db.Close
End Sub

My error appear on the statement :

    SQLCount = "SELECT Count(*) FROM Contacts WHERE SiteID = " & Me.Parent!NewSiteID.Value

I have exhausted all possible thing to do so please help me.

 

[MarkK]

When you open a form that contains a subform, the open event occurs first for the subform and then for the parent. As a result, during the open event of the subform the parent doesn't really exist yet, and references to it--as you've seen--will fail.

There are a few work-arounds. The thing I most commonly do is load the subform programmatically, so first of all, delete it's name from the subform control's SourceObject property, which is on the data tab of the parent form's subform control in design view.

Then, at some point during open or load on the parent form, explicitly set the SourceObject property of the subform control to the name of the subform you want to load. Code will look like . . . .

Private Sub Form_Open(Cancel As Integer)
   Me.MySubformControlName.SourceObject = "NameOfSubform"
End Sub

This loads the subform into the subform control in the parent, and then, during the open event of the subform, that parent will be present.

hth

 

[manusri]

Thanks it worked. No more hiccups so far.