MS Access Question for Access Gurus

[Elliot W. Scott]

I have one table called Album
another table called Song

One Album has many songs

I created an Album form that contains a Song Subform.

in the Song table I have a field id which is the primary key, and
another field called index

The song index is a number that represents the song number on the record.

For example on the Beatle's Album: Abbey Road the song indices are as
follows:

Track Listings
1. Come Together
2. Something
3. Maxwell's Silver Hammer
4. Oh! Darling
5. Octopus's Garden
6. I Want You (She's So Heavy)
7. Here Comes the Sun
8. Because
9. You Never Give Me Your Money
10. Sun King
11. Mean Mr. Mustard
12. Polythene Pam
13. She Came in Through the Bathroom Window
14. Golden Slumbers
15. Carry That Weight
16. End
17. Her Majesty

My question is.....on the song subform how can I get the song index to
automatically have the index inserted based on the number of tracks
already entered. In other words a running count?

For your information, here are my tables

Table: Album
Primary Key: id (number)
name (text)

Table: Song
Primary Key: id (number)
name (text)
index (number)
albumID (foreign key)

Relationship is One Album to Many Songs with Referential Integrity.

I really appreciate your help.

[windandwaves]

"Elliot W. Scott" <no****@nospam.com> wrote in message news:mu********************@comcast.com...

I have one table called Album
another table called Song

One Album has many songs

I created an Album form that contains a Song Subform.

in the Song table I have a field id which is the primary key, and another field called index

The song index is a number that represents the song number on the record.

For example on the Beatle's Album: Abbey Road the song indices are as follows:

Track Listings
1. Come Together 2. Something 3. Maxwell's Silver Hammer 4. Oh! Darling 5. Octopus's Garden 6. I Want You (She's So
Heavy) 7. Here Comes the Sun 8. Because 9. You Never Give Me Your Money 10. Sun King 11. Mean Mr. Mustard 12.
Polythene Pam 13. She Came in Through the Bathroom Window 14. Golden Slumbers 15. Carry That Weight 16. End 17. Her
Majesty
My question is.....on the song subform how can I get the song index to automatically have the index inserted based on the number
of tracks already entered. In other words a running count?

For your information, here are my tables

Table: Album
Primary Key: id (number)
name (text)

Table: Song
Primary Key: id (number)
name (text)
index (number)
albumID (foreign key)

Relationship is One Album to Many Songs with Referential Integrity.

I really appreciate your help.

the short answer, I think, is that you should use this function as the default value for the ID (index) number:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

This means: the maximum value for index in the song table where the albumID is equal to the album ID that is currently shown in
myform.

I am not sure what you should have instead of myform, but someone else or yourself may figure this out.

Nicolaas

[Trevor Best]

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

--
This sig left intentionally blank

[Elliot W. Scott]

Trevor Best wrote:

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

[Elliot W. Scott]

Trevor Best wrote:

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

[Elliot W. Scott]

Trevor Best wrote:

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

[Trevor Best]

Elliot W. Scott wrote:

Trevor Best wrote:

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

dmax(fieldname,tablename,criteria)
Yes, that should be your song table.

Also, where is this DMax being executed from?

Have you tried the:
Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
approach?

If addressing the form from code, you should use Me instead of Form, e.g.
Nz(dmax("[index]","[song]", "[albumID]=" & Me.Parent!albumID),0)+1

Alternatively you should put this DMax into the DefaultValue property,
don't put in the ControlSource property, if you do it needs a "=" before
it and it won't save it anywhere.

--
This sig left intentionally blank

[Elliot W. Scott]

Trevor Best wrote:

Elliot W. Scott wrote:

Trevor Best wrote:

windandwaves wrote:

dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1


Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

dmax(fieldname,tablename,criteria)
Yes, that should be your song table.

Also, where is this DMax being executed from?

Have you tried the:
Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
approach?

If addressing the form from code, you should use Me instead of Form, e.g.
Nz(dmax("[index]","[song]", "[albumID]=" & Me.Parent!albumID),0)+1

Alternatively you should put this DMax into the DefaultValue property,
don't put in the ControlSource property, if you do it needs a "=" before
it and it won't save it anywhere.

Thanks. I had to do the it as follows:
=Nz(DMax("[index]","[Song]","[albumID]=" &[Forms]![AlbumForm]![id]),0)+1

but it still doesn't work, it does it in a weird pattern...

It does this for my index:
1
1
2
2
3
3
4
4
5
5
6
6
.....
I think it does this because the new record is created before I am done
with the one I am currently creating. :(

[Trevor Best]

Elliot W. Scott wrote:

Trevor Best wrote:

Elliot W. Scott wrote:

Trevor Best wrote:

windandwaves wrote:

> dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID)+1

Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
As the first one will return a null.
or (not sure but try)
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
would be portable to another form.

I think something is wrong I get an #error
I don't know what this is.
\/
Nz(dmax("[index]","[song]", "[albumID]=" & form.Parent!albumID),0)+1
Is [song] my Song table? or should it be the song id?

dmax(fieldname,tablename,criteria)
Yes, that should be your song table.

Also, where is this DMax being executed from?

Have you tried the:
Nz(dmax("[index]","[song]", "[albumID]=" & forms!myform!albumID),0)+1
approach?

If addressing the form from code, you should use Me instead of Form, e.g.
Nz(dmax("[index]","[song]", "[albumID]=" & Me.Parent!albumID),0)+1

Alternatively you should put this DMax into the DefaultValue property,
don't put in the ControlSource property, if you do it needs a "="
before it and it won't save it anywhere.

Thanks. I had to do the it as follows:
=Nz(DMax("[index]","[Song]","[albumID]=" &[Forms]![AlbumForm]![id]),0)+1

but it still doesn't work, it does it in a weird pattern...

It does this for my index:
1
1
2
2
3
3
4
4
5
5
6
6
....
I think it does this because the new record is created before I am done
with the one I am currently creating. :(

Try requerying the index textbox in Form_BeforeInsert()

--
This sig left intentionally blank