By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,450 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,450 IT Pros & Developers. It's quick & easy.

MS Access Question for Access Gurus

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"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
Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
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?
Nov 13 '05 #4

P: n/a
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?
Nov 13 '05 #5

P: n/a
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?
Nov 13 '05 #6

P: n/a
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
Nov 13 '05 #7

P: n/a
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. :(

Nov 13 '05 #8

P: n/a
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
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.