If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Increment composite key
I have two datasheet forms, one linked to the other using a textbox link.
The fist datasheet form I have an auto increment field, and working fine. In the second datasheet form I would also like a auto increment number. The second form uses a composite key so I don't get duplicate numbers. Table 1, auto increments the number 01 02 03 04 Table 2 is a composite key from Table 1 & 2 Two fields 01 01 01 02 01 03 02 01 02 02 02 03 These are two seperate fields in table two. They are linked by the first field from table 1. I have both fields as a composite key. I would like to auto increment the second field in table 2. (if possible) -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#2
|
|||
|
|||
Auto Increment composite key
On Sun, 07 Jun 2009 01:28:46 GMT, "chopper7898 via AccessMonster.com"
u43982@uwe wrote: I have two datasheet forms, one linked to the other using a textbox link. What is a "textbox link"? The fist datasheet form I have an auto increment field, and working fine. In the second datasheet form I would also like a auto increment number. The second form uses a composite key so I don't get duplicate numbers. Table 1, auto increments the number 01 02 03 04 Table 2 is a composite key from Table 1 & 2 Two fields 01 01 01 02 01 03 02 01 02 02 02 03 These are two seperate fields in table two. They are linked by the first field from table 1. I have both fields as a composite key. I would like to auto increment the second field in table 2. (if possible) The simplest way to do this is to use a Form (single, not datasheet) based on table 1, with a Subform based on table 2. Let's call the two fields Table1ID and Table2ID. You'ld use Table1ID as the Master/Child Link Field of the subform (to populate it into new records on the subform). In the subform's Beforeinsert event put code to increment the ID: Private Sub Form_BeforeInsert(Cancel as Integer) Me!Table2ID = NZ(DMax("[Table2ID]", "[Table2]", _ "[Table1ID] = " & [Table1ID])) + 1 End Sub This will look up the largest existing value of Table2ID for the currently selected Table1ID; if there is none the NZ() function will return 0. The +1 increments the largest existing value by one to assign a new incremented value. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|