I have three tables, each creates it own set of ID numbers (through autonumber). therefore i have Table A, with ID's 1-10, Table B, with ID's 1-10, & Table C, with ID's 1-10.
Once details have been filled into one of these tables, a Fourth table is opened. Within this 4th table, i want an ID field created automatically by Combining the id field (from the record you just filled) with thetables name, therefore if i had just created in Table B, a record with the ID 23, then the combind ID would be B23.
Hope this makes sense!!
This conversation is currently closed to new comments.
You don't mention what version of Access you're using as well as what version of Jet. If you're using a newer version of access you can do it like this.
Private Sub WriteTableB()
Dim db As Database Dim rs As Recordset Dim ID As Long
Set db = CurrentDb Set rs = db.OpenRecordset("TableB", dbOpenTable)
With rs .AddNew !Field1 = "Something" !Field2 = "Something else" .Update ID = rs.Fields(0).Value End With
Good luck on finding an answer to this that doesn't involve some coding. If you want to get past the beginner stage with access you will need to learn how to code.
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Access - Combination ID field
Once details have been filled into one of these tables, a Fourth table is opened. Within this 4th table, i want an ID field created automatically by Combining the id field (from the record you just filled) with thetables name, therefore if i had just created in Table B, a record with the ID 23, then the combind ID would be B23.
Hope this makes sense!!