Recordset Problem

B

Bill Parker

H All,
I am having a problem appending a new record to a table from a filtered
recordset. The recordset is filtered from a large table we'll call
"tblPrime". I want to append to a table called "tblRetrievalVest". The
error message I get is "Method or Data Member Not Found", and the
"rsPRT.fieldname " is highlighted. Here is the code I've been using:

If Me.FilterOn = True And Forms!DelOption!rbPRTVL = -1 Then

Dim rsPRT As DAO.Recordset
Dim rsDEST As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rsPRT = Me.Recordset
Set rsDEST = dbs.OpenRecordset("tblRetrievalVest")

Do Until rsPRT.EOF
rsDEST.AddNew
rsDEST.DOB = rsPRT.DOB
rsDEST.[Exam Year] = rsPRT.[Exam Year]
rsDEST.Notes = rsPRT.Notes
rsDEST.[Job#] = rsPRT.[Job#]
rsDEST.[Box#] = rsPRT.[Box#]
rsDEST.[Rec Pos] = rsPRT.[Rec Pos]
rsDEST.[Volume#] = rsPRT.[Volume#]
rsDEST.Name = rsPRT.Name
rsDEST.MRN = rsPRT.MRN
rsDEST.Accession = rsPRT.Accession
rsDEST.Update
rsPRT.MoveNext
End If

Thanks for your help!
 
B

boblarson

I'd guess that your issue is with

rsDEST.Name = rsPRT.Name

In case you didn't know, NAME is an Access reserved word and means something
in this case, which you aren't wanting. So, I would definitely rename your
fields something other than Name (even if it is tName or something) and in
the meantime, what may work is putting the name in brackets

rstDEST.[Name] = rsPRT.[Name]


--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________
 
B

Bill Parker

Thanks for your quick reply. I pulled the "name" field lne and put the other
field names in brackets, but that didn't solve the problem. It must be some
other problem with "grammer".
--
William E. Parker
IT Manager
MetalQuest



boblarson said:
I'd guess that your issue is with

rsDEST.Name = rsPRT.Name

In case you didn't know, NAME is an Access reserved word and means something
in this case, which you aren't wanting. So, I would definitely rename your
fields something other than Name (even if it is tName or something) and in
the meantime, what may work is putting the name in brackets

rstDEST.[Name] = rsPRT.[Name]


--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


Bill Parker said:
H All,
I am having a problem appending a new record to a table from a filtered
recordset. The recordset is filtered from a large table we'll call
"tblPrime". I want to append to a table called "tblRetrievalVest". The
error message I get is "Method or Data Member Not Found", and the
"rsPRT.fieldname " is highlighted. Here is the code I've been using:

If Me.FilterOn = True And Forms!DelOption!rbPRTVL = -1 Then

Dim rsPRT As DAO.Recordset
Dim rsDEST As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rsPRT = Me.Recordset
Set rsDEST = dbs.OpenRecordset("tblRetrievalVest")

Do Until rsPRT.EOF
rsDEST.AddNew
rsDEST.DOB = rsPRT.DOB
rsDEST.[Exam Year] = rsPRT.[Exam Year]
rsDEST.Notes = rsPRT.Notes
rsDEST.[Job#] = rsPRT.[Job#]
rsDEST.[Box#] = rsPRT.[Box#]
rsDEST.[Rec Pos] = rsPRT.[Rec Pos]
rsDEST.[Volume#] = rsPRT.[Volume#]
rsDEST.Name = rsPRT.Name
rsDEST.MRN = rsPRT.MRN
rsDEST.Accession = rsPRT.Accession
rsDEST.Update
rsPRT.MoveNext
End If

Thanks for your help!
Click to expand...
Click to expand...
 
M

Marshall Barton

Bill said:
I am having a problem appending a new record to a table from a filtered
recordset. The recordset is filtered from a large table we'll call
"tblPrime". I want to append to a table called "tblRetrievalVest". The
error message I get is "Method or Data Member Not Found", and the
"rsPRT.fieldname " is highlighted. Here is the code I've been using:

If Me.FilterOn = True And Forms!DelOption!rbPRTVL = -1 Then

Dim rsPRT As DAO.Recordset
Dim rsDEST As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rsPRT = Me.Recordset
Set rsDEST = dbs.OpenRecordset("tblRetrievalVest")

Do Until rsPRT.EOF
rsDEST.AddNew
rsDEST.DOB = rsPRT.DOB
rsDEST.[Exam Year] = rsPRT.[Exam Year]
rsDEST.Notes = rsPRT.Notes
rsDEST.[Job#] = rsPRT.[Job#]
rsDEST.[Box#] = rsPRT.[Box#]
rsDEST.[Rec Pos] = rsPRT.[Rec Pos]
rsDEST.[Volume#] = rsPRT.[Volume#]
rsDEST.Name = rsPRT.Name
rsDEST.MRN = rsPRT.MRN
rsDEST.Accession = rsPRT.Accession
rsDEST.Update
rsPRT.MoveNext
End If
Click to expand...


When refering to a field in a recordset (or a member of any
collection), you need to use ! instead of dot.
 
B

Bob Larson

Marshall:

Good point - I usually do it with
rst.Fields("MyFieldName")
so I forgot about the bang being necessary for the other way

Bob

Marshall Barton said:
Bill said:
I am having a problem appending a new record to a table from a filtered
recordset. The recordset is filtered from a large table we'll call
"tblPrime". I want to append to a table called "tblRetrievalVest". The
error message I get is "Method or Data Member Not Found", and the
"rsPRT.fieldname " is highlighted. Here is the code I've been using:

If Me.FilterOn = True And Forms!DelOption!rbPRTVL = -1 Then

Dim rsPRT As DAO.Recordset
Dim rsDEST As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rsPRT = Me.Recordset
Set rsDEST = dbs.OpenRecordset("tblRetrievalVest")

Do Until rsPRT.EOF
rsDEST.AddNew
rsDEST.DOB = rsPRT.DOB
rsDEST.[Exam Year] = rsPRT.[Exam Year]
rsDEST.Notes = rsPRT.Notes
rsDEST.[Job#] = rsPRT.[Job#]
rsDEST.[Box#] = rsPRT.[Box#]
rsDEST.[Rec Pos] = rsPRT.[Rec Pos]
rsDEST.[Volume#] = rsPRT.[Volume#]
rsDEST.Name = rsPRT.Name
rsDEST.MRN = rsPRT.MRN
rsDEST.Accession = rsPRT.Accession
rsDEST.Update
rsPRT.MoveNext
End If
Click to expand...


When refering to a field in a recordset (or a member of any
collection), you need to use ! instead of dot.
Click to expand...
 
B

Bill Parker

Thanks, Marshall, it now works a treat. Somewhere in the musty place that is
my brain, I knew that, but it just wasn't registering. Data out of range...

Best,
Bill
--
William E. Parker
IT Manager
MetalQuest



Bob Larson said:
Marshall:

Good point - I usually do it with
rst.Fields("MyFieldName")
so I forgot about the bang being necessary for the other way

Bob

Marshall Barton said:
Bill said:
I am having a problem appending a new record to a table from a filtered
recordset. The recordset is filtered from a large table we'll call
"tblPrime". I want to append to a table called "tblRetrievalVest". The
error message I get is "Method or Data Member Not Found", and the
"rsPRT.fieldname " is highlighted. Here is the code I've been using:

If Me.FilterOn = True And Forms!DelOption!rbPRTVL = -1 Then

Dim rsPRT As DAO.Recordset
Dim rsDEST As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rsPRT = Me.Recordset
Set rsDEST = dbs.OpenRecordset("tblRetrievalVest")

Do Until rsPRT.EOF
rsDEST.AddNew
rsDEST.DOB = rsPRT.DOB
rsDEST.[Exam Year] = rsPRT.[Exam Year]
rsDEST.Notes = rsPRT.Notes
rsDEST.[Job#] = rsPRT.[Job#]
rsDEST.[Box#] = rsPRT.[Box#]
rsDEST.[Rec Pos] = rsPRT.[Rec Pos]
rsDEST.[Volume#] = rsPRT.[Volume#]
rsDEST.Name = rsPRT.Name
rsDEST.MRN = rsPRT.MRN
rsDEST.Accession = rsPRT.Accession
rsDEST.Update
rsPRT.MoveNext
End If
Click to expand...


When refering to a field in a recordset (or a member of any
collection), you need to use ! instead of dot.
Click to expand...
Click to expand...
 
Top