Auto Numbering a field

RichO

Registered Yoozer
Local time
Today, 05:13
Joined
Jan 14, 2004
Messages
1,036
I have a simple form and a subform within, for entering music from compact discs.

The subform has a text box for the track #, that I want to increment each time a new record is added, starting with 1 for the first record. Basically I want the number of each record in the subform to appear in the text box, and of course, when you advance a record in the main form, start back at 1 for the next CD.

I set the data type in the table to autoincrement but there are 2 problems. First, it prints (autoincrement) in the text box, and when you advance to a new record in the main form, the number doesn't go back to 1.

This is easy to do on a report but I can't figure it out in this form.
 
I don't understand how you could use autonumbers for track numbers since you will obviously have more than 1 CD with track 1....but anyway...

I would suggest you not use the autonumber as a way of assigning track numbers. If you start editing a record, then cancel out, that autonumber will be assigned. Access will try to assign the next one, and you'll have to compact the database to get back to it.

Autonumbers are best used when the autonumber itself has no intrinsic meaning, but you simply need a way of unique identifying each record of a table.

Create a track number field, set it up to incremement by 1 more than the highest previously assigned number. That topic has been covered in the forums many times.
 
To clarify, I have 2 tables. The first table has an autoincrement index number to link the 2 tables together. It also has a field for the name of the CD.

The 2nd table has fields for the matching index number as well as the track #, song title, and artist.

From what I've read, this is the best way to store this type of information rather than having a bunch of duplicate records in one table.

Here's the problem now... If I manually enter the track numbers, my table looks like this example (showing 3 songs per CD)...

index.....track......Song title.........Artist
-------------------------------------------------------
...1...........1.........Hey Jude..........Beatles
...1...........2.........Satisfaction......Rolling Stones
...1...........3.........Free Ride..........Edgar Winter
...2...........1.........Take It Easy.....Eagles
...2...........2.........Smokin'.............Boston
...2...........3.........Rock & Roll........Led Zeppelin
...3...........1.........Brown Sugar.....Rolling Stones
...3...........2.........L A Woman.......Doors
...3...........3.........Slow Ride..........Foghat

...which is how it should look.

OK, now if I use "txtTrackNumber = Form.CurrentRecord" in the subform On Current event, the data enters properly but the table ends up like this...

index.....track......Song title.........Artist
-------------------------------------------------------
................1.........Hey Jude..........Beatles
...1...........2.........Satisfaction......Rolling Stones
...1...........3.........Free Ride..........Edgar Winter
...1...........4......................................................
................1.........Take It Easy.....Eagles
...2...........2.........Smokin'.............Boston
...2...........3.........Rock & Roll........Led Zeppelin
...2...........4.......................................................
................1.........Brown Sugar.....Rolling Stones
...3...........2.........L A Woman.......Doors
...3...........3.........Slow Ride..........Foghat
...3...........4.......................................................

When the current record number advances and you are finished entering data for that CD, it still regards it as a record, and why the index doesn't take to the first track is puzzling.

Any ideas how to fix this?

Thanks
 
Using autonumbers for the 2nd table part of this is probably going to cause you issues down the road....BUT sounds like your subform is not set up properly.

I don't see why you need code to tie the "index" number from the form to the subform. Why not let Access handle the link for you in the Link Master and Link Child Fields? You've got the tables set up properly (with the first one having the autonumber). You should set up the relationship as a one-to-many with referential integrity checking in the Access Relationships window.
 
Access did automatically set it up as a one to many relationship. There is no code tying the 2 tables together. Both tables contain an index field to tie them together and it is set up that way in the master and child field links. In the table containing the main title of the CD the index field is the primary key. Does that sound like the correct set up?

Like I said, everything works great when I manually number the tracks but it's tedious. I don't understand why Access cannot successfully autonumber this field.
 
Something is still wrong because somehow, you have records in the 2nd table without an index value. How can that happen if there is a one-to-many relationship between the tables?
 
Oops, I just noticed that Wadero was still logged in on my last post.


I think I know why this is happening...

When using linked tables in a form/subform, apparently Access does not auto-increment the index (primary key) until you enter data into that record first. Am I correct? The main form uses the table containing the auto-incremented index number (the "one" in the one-to-many relationship).

I have found that any data I enter into the subform, while leaving the main form field blank, does not get an index number assigned to it. Is this how it's supposed to work?

OK, when I have the subform set up to auto-number the track field, the text box for the first track already has a "1" in it before I even enter the CD title in the main form. That would explain why track 1 gets no index #. Once I have entered the last song, it still increments the track number field (4 in this case), and because of this, track 4 is now a new record in the table even if I don't enter a song title.

The autonumbering is done from the On Current Event in the subform. I suppose I should try moving it to another event.

I did try to set up VBA to remove the unwanted track 4 but being a VBA novice, I don't think I have the code correct. In the After Update Event I put:

If IsNull([SongTitle]) = True then Me.TrackNumber = Null

...but that didn't do the trick.

I'll keep experimenting with it. Any suggestions that might help?

Thanks
 

Users who are viewing this thread

Back
Top Bottom