I’m working on a fairly straightforward database tool that will store song data.
The kinds of data I am storing fall into some basic groupings so I have a number of related tables.
Each Song Title breaks down in to Sections by Name (as in Intro, Verse and Chorus etc.).
Each SectionName has a Rehearsal Mark (as in A, B, C etc.).
Each RehearsalMark can have a number of distinct Patterns (as in a, b, c etc.).
Each Pattern has a length in Bars (as in 4, 8, 12, etc.).
And so on and so forth.
Example: Name=Introduction. Section=A. Pattern=a. BarsLength=8. Bar= 1,2,3,4 etc.
At this point, I want to save the number of keystrokes by adding each Bar Number (of each PatternLength) both incrementally and programmatically and thus far have been using a dmax function on a button click event to do the job.
Here is the crux of my problem (and the source of my brain constipation).
I want the db to hold multiple songs (by songID) and whilst songID 1 doesn’t present any issues, I want to be able to reset the BarNumber to 1 for every new songID.
All the work I have done so far specifying Criteria within the DMAX syntax fails with Run-Time error '2001': You cancelled the previous operation.
Clearly, all I am seeking is some code that will respond to the value of my forms lngSongID field and that will increment by a value of 1 whatever the lngSongID value happens to be? !!
Here is my basic function code after having removed the stuff that simply wasn't working :
Public Function nxtBarUp() As Integer
On Error GoTo nextBar_Err
Dim nextBar As Integer
'Find highest bar number in tblBarNumber and add 1 WHERE lngSongID is equal to the form field
'of the same name
nextBar = Nz(DMax("lngBar", "tblBarNumber")) + 1
'Assign function the value of the Next Bar
nxtBarUp = nextBar
Exit_nxtBarUp:
Exit Function
nextBar_Err:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_nxtBarUp
End Function
The form button them simply calls the function after moving to a new record.
Can somebody help me with this, much appreciated!
The kinds of data I am storing fall into some basic groupings so I have a number of related tables.
Each Song Title breaks down in to Sections by Name (as in Intro, Verse and Chorus etc.).
Each SectionName has a Rehearsal Mark (as in A, B, C etc.).
Each RehearsalMark can have a number of distinct Patterns (as in a, b, c etc.).
Each Pattern has a length in Bars (as in 4, 8, 12, etc.).
And so on and so forth.
Example: Name=Introduction. Section=A. Pattern=a. BarsLength=8. Bar= 1,2,3,4 etc.
At this point, I want to save the number of keystrokes by adding each Bar Number (of each PatternLength) both incrementally and programmatically and thus far have been using a dmax function on a button click event to do the job.
Here is the crux of my problem (and the source of my brain constipation).
I want the db to hold multiple songs (by songID) and whilst songID 1 doesn’t present any issues, I want to be able to reset the BarNumber to 1 for every new songID.
All the work I have done so far specifying Criteria within the DMAX syntax fails with Run-Time error '2001': You cancelled the previous operation.
Clearly, all I am seeking is some code that will respond to the value of my forms lngSongID field and that will increment by a value of 1 whatever the lngSongID value happens to be? !!
Here is my basic function code after having removed the stuff that simply wasn't working :
Public Function nxtBarUp() As Integer
On Error GoTo nextBar_Err
Dim nextBar As Integer
'Find highest bar number in tblBarNumber and add 1 WHERE lngSongID is equal to the form field
'of the same name
nextBar = Nz(DMax("lngBar", "tblBarNumber")) + 1
'Assign function the value of the Next Bar
nxtBarUp = nextBar
Exit_nxtBarUp:
Exit Function
nextBar_Err:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_nxtBarUp
End Function
The form button them simply calls the function after moving to a new record.
Can somebody help me with this, much appreciated!
Last edited:
