Software

Ask something
Discussion Locked

Access - Combination ID field

0 helpful
4 replies

by jdlp

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!!

4 replies

by badermsb

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

Call WriteTable4(ID & "B")

End Sub

by jdlp

Am using Access 2000
Where am i meant to use this code within the system?? iknow access pretty well, but not to good on coding!!

by badermsb

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.

This discussion is closed to new replies.