Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2013, 02:43 PM   #1
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Why Run time error 2452 for Select statement

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.

Code:
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 :

Code:
    SQLCount = "SELECT Count(*) FROM Contacts WHERE SiteID = " & Me.Parent!NewSiteID.Value
I have exhausted all possible thing to do so please help me.
Attached Images
File Type: png SiteDetailscontacts.png (42.8 KB, 146 views)

manusri is offline   Reply With Quote
Old 09-11-2013, 05:53 PM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,614
Thanks: 10
Thanked 1,260 Times in 1,199 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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 . . . .

Code:
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
manusri (05-17-2014)
Old 05-17-2014, 10:52 PM   #3
manusri
Newly Registered User
 
Join Date: Dec 2012
Posts: 29
Thanks: 11
Thanked 0 Times in 0 Posts
manusri is on a distinguished road
Re: Why Run time error 2452 for Select statement

Thanks it worked. No more hiccups so far.

manusri is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! Run-time Error '3144' Syntax error in UPDATE statement irunergoiam Forms 6 07-20-2015 11:36 AM
Error with select statement Coleman984 General 3 08-24-2011 09:49 PM
SQL Select statement with date syntax error Sunnray Modules & VBA 1 07-16-2010 05:41 AM
RUN TIME ERROR 3144, syntax error in update statement mjancic Modules & VBA 10 03-30-2010 05:13 AM
Select statement syntax error Sevn Modules & VBA 3 05-07-2009 09:52 AM




All times are GMT -8. The time now is 03:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World