How to edit a DAO recordset and insert a new record into table?

[Apollo6]

I am wanting to use DAO.RecordSet to pull a specific record. This I am able to do; slice and dice through the data for that record retrieved.

What I want to do is modify a few elements(fields) in the recordset and insert the modified recordset back into the same table as a new record.

I am not real familiar with the syntax/abilities of DAO so I just need some suggestions. It may be that I shouldn't be using DAO at all, just not sure.

Suggestions???

 

[PHV]

What have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Apollo6]

PHV-
Nothing at this point. I can pull get the recordset, know if my count is > or < etc... Pull out the different pieces of the recordset and load into variables etc. I just don't know the syntax of how to do what I want to do.

I've read countless articles on pros/cons of DAO vs. ADO which just leaves me with more questions about which one to go with. Regardless, if I go with say DAO, I want to create a new record with the results of the original recordset(DAO) and insert back into a table as a new record. I kind of have an idea of how but what I'm struggling with is that my recordset could have a lot of fields, i.e. the table(record returned matching my criteria) will have, say 40 fields. I want to update two fields in the recordset and insert a new record back into the table.

For example, the recordset brought back will have a date field(previous_date) and a visit_type="IC". I want to change the date field to Now() and the visit_type="RV". Then insert the entire record, 40 fields, with two of those fields being my updated values, making a new record in the table.

Make sense?

 

[Zion7]

With all due respect Apollo, it same strange that, with all
your research, you couldn't find the simple solution?
Just curious, if i've understood correctly?
Well, simply put

To loop thru your DAO recordset.

Do Until rec.EOF

If rec!txtName = ... Then
rec.edit
rec!txtDate = Date

rec.AddNew
rec!txtDate = Date


rec.Update
rec.movenext
loop

 

[Apollo6]

Thanks for the tips Zion... My research for the most part got side tracked by all the articles discussing DAO vs. ADO, not really how you actually work with and write code for either.

I'll try out your code snippets and post back.