compile error method or data member not found in Access 2003

Hi all.

I get the following error: compile error method or data member not found and it highlights : '" & rsAGTop_Parent("Print_Media_ID") & "' in the following line in my code provided below:CurrentDb.Execute "insert into temptable_RejectedAG_Assignments(PrintMediaID, GPO, Entity_Code, AG) values ('" & rsAGTop_Parent("Print_Media_ID") & "','" & Me.cmbGPO_Name & "','" & rsAGTop_Parent("Facility_ID") & "','" & Me.cmbAG & "')"


What am I doing wrong? It's supposed to insert those records that already in the tblAG_Assignments table based on their top parent number. Which is the number a collection of records may share when they are part of the same family.

Dim rsAGTop_Parent As DAO.Recordset
 
Set rsAGTop_Parent = CurrentDb.OpenRecordset("Select Distinct [Contract_Assignment_Number],[Facility_ID], [Print_Media_ID], [Top_Parent_Number] from tblContractAssignment_Detail where [Contract_Assignment_Number]=" & Me.txtContractAssignmentNumber & " and [CAssignment_Detail_Entity] = '" & Me.Assignment_Entity & "'")
    
    If rsAGTop_Parent.EOF Then
    
        MsgBox "No Records 5"
        Exit Sub
    
    End If
    
    rsAGTop_Parent.MoveFirst
   
   Do Until rsAGTop_Parent.EOF
   
   If IsNull(DLookup("Entity_Code", "tblAG_Assignments", "[GPO]='" & Me.cmbGPO & "' and [Entity_Code]='" & rsAGTop_Parent("Facility_ID") & "' and [AG]='" & Me.cmbAG & "'")) Then
  
        CurrentDb.Execute "insert into tblAG_Assignments(AG_Assignment_Type,PrintMediaID, GPO,Entity_Code,Top_Parent_Number,AG,Contract_Assignment_Number, Status, Create_Username, Create_Date) values ('" & 4 & "','" & rsAGTop_Parent("Print_Media_ID") & "','" & Me.cmbGPO & "','" & rsAGTop_Parent("Facility_ID") & "','" & rsAGTop_Parent("Top_Parent_Number") & "','" & Me.cmbAG & "','" & Me.txtContractAssignmentNumber & "','" & "Active" & "','" & GetUserName() & "', '" & Now() & "')"
 
 ElseIf IsNull(DLookup("Entity_Code", "tblAG_Assignments", "[GPO]='" & Me.cmbGPO & "' and [Entity_Code]='" & rsAGTop_Parent("Facility_ID") & "' and [AG]='" & Me.cmbAG & "' and [Status]='Active'")) Then
 
        CurrentDb.Execute "insert into tblAG_Assignments(AG_Assignment_Type,PrintMediaID, GPO,Entity_Code,AG,Contract_Assignment_Number, Status, Create_Username, Create_Date) values ('" & 4 & "','" & rsAGTop_Parent("Print_Media_ID") & "','" & Me.cmbGPO & "','" & rsAGTop_Parent("Facility_ID") & "','" & rsAGTop_Parent("Top_Parent_Number") & "','" & Me.cmbAG & "','" & Me.txtContractAssignmentNumber & "','" & "Active" & "','" & GetUserName() & "', '" & Now() & "')"
        
 Else
 'use a temptable
 
CurrentDb.Execute "insert into temptable_RejectedAG_Assignments(PrintMediaID, GPO, Entity_Code, AG) values ('" & rsAGTop_Parent("Print_Media_ID") & "','" & Me.cmbGPO_Name & "','" & rsAGTop_Parent("Facility_ID") & "','" & Me.cmbAG & "')"
Rejected_Flag = "True"
 
 
End If
    rsAGTop_Parent.MoveNext
      
    Loop
    
    rsAGTop_Parent.Close

Open in new window

printmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
change

rsAGTop_Parent("Print_Media_ID")

to

rsAGTop_Parent![Print_Media_ID]
0
Rey Obrero (Capricorn1)Commented:
also change

rsAGTop_Parent("Facility_ID")

to

rsAGTop_Parent![Facility_ID]
0
printmediaAuthor Commented:
I did the changes you suggested, but I get the same error. It highlights: ![Print_Media_ID]
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:
do this as a test

Set rsAGTop_Parent = CurrentDb.OpenRecordset("Select Distinct [Contract_Assignment_Number],[Facility_ID], [Print_Media_ID], [Top_Parent_Number] from tblContractAssignment_Detail where [Contract_Assignment_Number]=" & Me.txtContractAssignmentNumber & " and [CAssignment_Detail_Entity] = '" & Me.Assignment_Entity & "'")
   
    If rsAGTop_Parent.EOF Then
   
        MsgBox "No Records 5"
        Exit Sub
   
    End If

rsAGTop_Parent.MoveFirst

msgbox  rsAGTop_Parent![Print_Media_ID]  'ADD this line




see if you get the message box with the value of ![Print_Media_ID]
0
printmediaAuthor Commented:
It does not show the msgbox it gives me the same error and highlights the same code.
0
NorieAnalyst Assistant Commented:
Have you tried using the recordset's Fields collection?

rsAGTop_Parent.Fields("Facility_ID")

There are various ways to refer to a field in a recordset and as far as I know the original code and what has been suggested are all valid.

If they aren't working then there could be some other problem.
0
Rey Obrero (Capricorn1)Commented:
do a Compact and repair
tools > database utilities > compact and repair

in the code window
Tools  >references
check if you have some reference with MISSING: prefix
uncheck and locate the available version and check

then do a DEBUG>Compile

correct any errors that maybe raised


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
printmediaAuthor Commented:
I did the compact and repair and the DEBUG, the DEBUG takes me to the same line of code.

I use another similar code in the same form (but it only retrieves one record, unlike the one that is giving me the error which goes through the loop to obtain all the records who match the criteria: Contract_Assignment_Number and CAssignment_Detail_Entity. I have provided it below as you can see it's very similar, but it doesn't have the loop. Could that be what's wrong?
      Dim rsAGEntity_Code As DAO.Recordset
 
 
Set rsAGEntity_Code = CurrentDb.OpenRecordset("Select Distinct [Contract_Assignment_Number],[Facility_ID], [Print_Media_ID] from tblContractAssignment_Detail where [Contract_Assignment_Number]=" & Me.txtContractAssignmentNumber & " and [CAssignment_Detail_Entity]='" & Me.Assignment_Entity & "'")
 
 
If IsNull(DLookup("Entity_Code", "tblAG_Assignments", "[GPO]='" & Me.cmbGPO & "' and [Entity_Code]='" & rsAGEntity_Code("Facility_ID") & "' and [AG]='" & Me.cmbAG & "'")) Then
  
        CurrentDb.Execute "insert into tblAG_Assignments(AG_Assignment_Type,PrintMediaID, GPO,Entity_Code,AG,Contract_Assignment_Number,CAssignment_Detail_Entity, Status, Create_Username, Create_Date) values ('" & 3 & "','" & rsAGEntity_Code("Print_Media_ID") & "','" & Me.cmbGPO & "','" & rsAGEntity_Code("Facility_ID") & "','" & Me.cmbAG & "','" & Me.txtContractAssignmentNumber & "','" & Me.Assignment_Entity & "','" & "Active" & "','" & GetUserName() & "', '" & Now() & "')"
 
 ElseIf IsNull(DLookup("Entity_Code", "tblAG_Assignments", "[GPO]='" & Me.cmbGPO & "' and [Entity_Code]='" & rsAGEntity_Code("Facility_ID") & "' and [Status]='Active'")) Then
 
        CurrentDb.Execute "insert into tblAG_Assignments(AG_Assignment_Type,PrintMediaID, GPO,Entity_Code,AG,Contract_Assignment_Number, CAssignment_Detail_Entity, Status, Create_Username, Create_Date) values ('" & 3 & "','" & rsAGEntity_Code("Print_Media_ID") & "','" & Me.cmbGPO & "','" & rsAGEntity_Code("Facility_ID") & "','" & Me.cmbAG & "','" & Me.txtContractAssignmentNumber & "','" & Me.Assignment_Entity & "','" & "Active" & "','" & GetUserName() & "', '" & Now() & "')"
 
 Else
 'use a temptable
 
CurrentDb.Execute "insert into temptable_RejectedAG_Assignments(PrintMediaID, GPO, Entity_Code, AG, Create_Date) values ('" & rsAGEntity_Code("Print_Media_ID") & "','" & Me.cmbGPO & "','" & rsAGEntity_Code("Facility_ID") & "','" & Me.cmbAG & "', '" & Now() & "')"
Rejected_Flag = "True"
 
 
End If

Open in new window

0
printmediaAuthor Commented:
I was missing: Option Explicit

at the top of my code. After adding it it found an undeclared variable. Now it works! Thanks for all your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.