I am having difficulty figuring out DAO recordsets. Supposed to be easy, but I am guessing I am a little dense when it comes to these. Here is what I am trying to accomplish. I have a form and it has a field with an AutoNumber. When I hit a command button, I want to copy that number to a separate table. Thats it! Simple! But I am hitting a brick wall.:banghead: Can someone point me to, or assist me working through this so I can possibly understand it? Is recordset the correct method for this?
DAO Confusion (1 Viewer)
- Thread starter DMerchen
- Start date
Steve R.
Retired
- Local time
- Today, 14:50
- Joined
- Jul 5, 2006
- Messages
- 4,749
Take a look at Comparison of DAO and ADO Recordset Syntax.
You did not identify if the act of copying to a table would be create a new record or be an edit of an existing record. That has a significant effect on how your code would be developed. I will assume that you intend to create a new record.
First create a DAO recordset. Once created you can use the code below. Note: The field into which you intend to place your autonumber value needs to be identified as a long integer.
If you are editing an existing record, use the DAO find command.. Note you can't use your autonumber since it would not exist in the receiving table.
You did not identify if the act of copying to a table would be create a new record or be an edit of an existing record. That has a significant effect on how your code would be developed. I will assume that you intend to create a new record.
First create a DAO recordset. Once created you can use the code below. Note: The field into which you intend to place your autonumber value needs to be identified as a long integer.
Code:
rs.AddNew
rs!FieldName=me.fieldnameofthevaluetobecopied
rs.UpdateIf you are editing an existing record, use the DAO find command.. Note you can't use your autonumber since it would not exist in the receiving table.
My bad. The number is not an AutoNumber, this number is something I enter. I use a separate AutoNumber for the ID in both tables. So all I am trying to do is copy one number and basically add a new record to the other table as you stated.
Steve R.
Retired
- Local time
- Today, 14:50
- Joined
- Jul 5, 2006
- Messages
- 4,749
The receiving field needs to be of the same data type as the field value being copied. You can also use MS Access conversion type functions.
They are both of the same type. So, this is the only method for copying from one table and adding a record to another table? Basically the steps are, pull from one table the value I want in the recordset, then copy to the other table from the recordset? I think I am getting this, but not sure. Thanks for your assistance with this. Might have to upload some code. 
