Using DAO to copy/update a complete record from table? 1

[Apollo6]

I am attempting to get a specific record from a table using DAO. Copy that record into a new dataset, modify that dataset and put back into the table as a new updated record. I am receiving a DataType Mismatch error when I try running through the For Loop when assigning the values to what will be the new updated record.

I am able to manually build the new recordset using variables but the problem is that I have tables that have way to many fields to do this effeciantly so I wanted to find a way to loop through the dataset and assign the fields.

Let me know if more information is needed and thanks for any assistance. Also, given what I want to do, I may be way off and am open to suggestions.

Dim rsOldField As Recordset
Dim rsNewField As Recordset
Dim fld As Field
Dim rsOldVisit As DAO.Recordset
Dim rsReturnVisit As DAO.Recordset

Set rsOldVisit = CurrentDb.OpenRecordset("SELECT * FROM tbl_visits WHERE [ptnumber] = '" & strptnumber & "' And [visit_type] = " & "'" & stricvisit & "'", dbOpenDynaset)

Set rsReturnVisit = CurrentDb.OpenRecordset("SELECT * FROM tbl_visits WHERE [ptnumber] = '" & strptnumber & "' And [visit_type] = " & "'" & stricvisit & "'", dbOpenDynaset)

For Each fld In rsReturnVisit.Fields
rsReturnVisit(fld.Name) = rsOldVisit(fld.Name)
Next
rsReturnVisit!Visit_Date = Format(Now(), "mm/dd/yyyy")
rsReturnVisit!visit_type = "RV"
rstTo.Update

 

[MajP]

You explicitly identified
Dim rsOldVisit As DAO.Recordset
Dim rsReturnVisit As DAO.Recordse
As DAO recordsets, but these may be dimensioned as ADO
Dim rsOldField As Recordset
Dim rsNewField As Recordset
Dim fld As Field
depending on how you have your references set up.

Thus
Dim rsOldField As DAO.Recordset
Dim rsNewField As DAO.Recordset
Dim fld As DAO.Field

 

[Apollo6]

MajP-

In looking at the References for the project, I have referenced:

Microsoft DAO 3.6 Object Library

That said, are the variables dimensioned correctly? Also, in looking back against my code, I can see that this would not work as I am thinking. Meaning that my rsReturnVisit needs to actually add a new record to the table, something like rsReturnVisit.AddNew, then populate all the fields using the loop and getting the values from rsOldVisit.Fields, update a few specific fields to a new value, then rsReturnVisit.Update.

It would seem that if I don't do the above, I would actually be updating the record that I initially pulled from the table. That is why I think that I needed a second recordset so I could get all the values that I simply want to copy into the new recordset/record, then push a new record into the table.

Again, I am not sure of this but that is the goal of what I am trying to do.

 

[MajP]

On the reference issue. With A2k and later by default you also get a reference to ADODB. Since there exists both a
ADO.Recordset and a DAO.recordset, if you dimension a variable as only
Dim rs as Recordset
It goes through your references and stops at the first library that it finds with a "Recordset" object. Often this is ADO because it is the default after A2K. Thus as I said ALWAYS explicitly identify DAO and ADO objects. Actually I was in a rush this morning so I have not really looked at your code. I will try later.

 

[Apollo6]

I see, will make sure they are explicitly DAO and try again.

 

[MajP]

Have you considered using an append query to do this? It would be more efficient.

 

[Apollo6]

Yes, I did take that approach at first but ran into issues. I don't recall now why it did not work as I expected. One key thing I have not mentioned... The project(FrontEnd) is in MSAccess2k which attaches to a backend in MySQL, connecting via ODBC. I will revisit the append idea to determine why it did not work out and put me on the recordset path.

 

[PHV]

I thought it was an UPDATE query instead of an APPEND query ?

 

[MajP]

Yeah, actually I see that you are not adding a record only changing the data in the record. So it would be an update query as PHV said. I think your code would work if you add and "edit", fix the RS name

rsReturnVisit.edit
For Each fld In rsReturnVisit.Fields
rsReturnVisit(fld.Name) = rsOldVisit(fld.Name)
Next fld
rsReturnVisit!Visit_Date = Format(Now(), "mm/dd/yyyy")
rsReturnVisit!visit_type = "RV"
rstReturnVisit.Update

 

[Apollo6]

Got it working using the DAO method!!!

Thanks for the input!!!