G
Guest
Greetings,
I have been struggling with this error for several days and would appreciate
any insight that the members of this group can offer.
Background: I am working on a classical music library database.
Database structure (abriged)
Table: Tracks
Field: TrackID - Type AutoNumber (PK)
Field: TrackName - Type Text
Field: ComposerID - Type Integer (FK)
etc.
Table: Composers
Field: ComposerID - Type AutoNumber (PK)
Field: ComposerName - Type Text
(Similar tables for Genre, Artist and Composition)
The problem occurs in the form used to add new records to the database.
Basic flow:
1) The user enters the location (directory) of the .wav files that he/she
wishes to add to the database.
2) The application reads the filenames and parses the directory path and
filenames to get most of the information required. (Each Track must have an
Artist, Genre, Composer and Composition associated with it.)
3) This unvalidated data is stored in an array of user-defined-type.
4) The information for the first track (first record in the array) is
initially displayed to the user and the user can navigate forward and
backward through the records to validate the information.
5) Once the user is satisfied with the information, he/she clicks a command
button to encode the wav files in .ogg format and add records to the database
for each Track.
The problem: I have a combo box on the form for each of Genre, Artist,
Composer and Composition.
The combo boxes are unbound.
The RowSource property is = SELECT [GenreName] FROM GENRE ORDER BY
[GenreName] (Similar for Artist, etc.)
LimitToList = True
I am using the "NotInList" Event to ensure that a record exists in the
Database for each of these. The code is...
Private Sub cboGenre_NotInList(NewData As String, Response As Integer)
Dim ctl As control
Set ctl = Me.cboGenre
If MsgBox("Genre " & NewData & " not in Database. Add now?", vbOKCancel)
= vbOK Then
Response = acDataErrAdded
DoCmd.RunSQL ("Insert Into Genre (GenreName) Values (" & strQuote &
NewData & strQuote & ")")
Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub
This works perfectly well when the combo box text property us updated by
user action on the form. If the user chooses to add the data to the database,
it gets added, if he/she chooses not to, the action is cancelled and focus
remains on the combo box.
However, to load the data from the "Tracks" data structure onto the form
(for initial display of the first Track info an subsequent navigation) I have
a routine that simply sets the Text property of the controls on the form...
Sub InitForm(intTrackIndex As Integer) As Boolean
With Forms.frmAdd
.cboGenre.SetFocus
.cboGenre.Text = Tracks.strGenreName(intTrackIndex)
.cboArtist.SetFocus
.cboArtist.Text = Tracks.strArtistName(intTrackIndex)
etc.
End With
End If
End sub
The difficulty is that the above subroutine triggers the same sequence of
events that updating the combo box from the form triggers. In particular, it
triggers a "NotInList" event when the Genre, Composer, etc. is not in the
database (a common occurance when loading a new album.)
If the user chooses to add the data to the database, it works fine. However,
if the user chooses NOT to add the data to the database, then the code fails
with...
"Run-time error '2101:
The setting you enterred isn't valid for this property."
It has something to do setting of the "Response" variable in the "NotInList"
event procedure. If I comment out the line "Response = acDataErrContinue"
then I get the error...
"RUn-time error '2237':
The text you entered isn't an item in the list"
Is there any way around this? It seems to me that setting the Text
properties should behave like tabbing through the controls and typing text.
Do I have a major design flaw here? This is driving me nuts!
Thanks,
Tom Craig
I have been struggling with this error for several days and would appreciate
any insight that the members of this group can offer.
Background: I am working on a classical music library database.
Database structure (abriged)
Table: Tracks
Field: TrackID - Type AutoNumber (PK)
Field: TrackName - Type Text
Field: ComposerID - Type Integer (FK)
etc.
Table: Composers
Field: ComposerID - Type AutoNumber (PK)
Field: ComposerName - Type Text
(Similar tables for Genre, Artist and Composition)
The problem occurs in the form used to add new records to the database.
Basic flow:
1) The user enters the location (directory) of the .wav files that he/she
wishes to add to the database.
2) The application reads the filenames and parses the directory path and
filenames to get most of the information required. (Each Track must have an
Artist, Genre, Composer and Composition associated with it.)
3) This unvalidated data is stored in an array of user-defined-type.
4) The information for the first track (first record in the array) is
initially displayed to the user and the user can navigate forward and
backward through the records to validate the information.
5) Once the user is satisfied with the information, he/she clicks a command
button to encode the wav files in .ogg format and add records to the database
for each Track.
The problem: I have a combo box on the form for each of Genre, Artist,
Composer and Composition.
The combo boxes are unbound.
The RowSource property is = SELECT [GenreName] FROM GENRE ORDER BY
[GenreName] (Similar for Artist, etc.)
LimitToList = True
I am using the "NotInList" Event to ensure that a record exists in the
Database for each of these. The code is...
Private Sub cboGenre_NotInList(NewData As String, Response As Integer)
Dim ctl As control
Set ctl = Me.cboGenre
If MsgBox("Genre " & NewData & " not in Database. Add now?", vbOKCancel)
= vbOK Then
Response = acDataErrAdded
DoCmd.RunSQL ("Insert Into Genre (GenreName) Values (" & strQuote &
NewData & strQuote & ")")
Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub
This works perfectly well when the combo box text property us updated by
user action on the form. If the user chooses to add the data to the database,
it gets added, if he/she chooses not to, the action is cancelled and focus
remains on the combo box.
However, to load the data from the "Tracks" data structure onto the form
(for initial display of the first Track info an subsequent navigation) I have
a routine that simply sets the Text property of the controls on the form...
Sub InitForm(intTrackIndex As Integer) As Boolean
With Forms.frmAdd
.cboGenre.SetFocus
.cboGenre.Text = Tracks.strGenreName(intTrackIndex)
.cboArtist.SetFocus
.cboArtist.Text = Tracks.strArtistName(intTrackIndex)
etc.
End With
End If
End sub
The difficulty is that the above subroutine triggers the same sequence of
events that updating the combo box from the form triggers. In particular, it
triggers a "NotInList" event when the Genre, Composer, etc. is not in the
database (a common occurance when loading a new album.)
If the user chooses to add the data to the database, it works fine. However,
if the user chooses NOT to add the data to the database, then the code fails
with...
"Run-time error '2101:
The setting you enterred isn't valid for this property."
It has something to do setting of the "Response" variable in the "NotInList"
event procedure. If I comment out the line "Response = acDataErrContinue"
then I get the error...
"RUn-time error '2237':
The text you entered isn't an item in the list"
Is there any way around this? It seems to me that setting the Text
properties should behave like tabbing through the controls and typing text.
Do I have a major design flaw here? This is driving me nuts!
Thanks,
Tom Craig