Still Struggling with NotInList

[Knowbody]

Using Access 2000, I am making a database for my record collection. I want to enter Album titles, Artist names and several other items. I have everything figured out except for the artist names. I'm using a combo box because I want to be able to choose the name from a drop down list but if it's not there, I want to be able to add it right in the Recordings form and not have to go the the artist table. The combo box currently works, I just can't add new entries. I get the following warning:

"The text you entered is not an item on the list. Select an item from the list, or enter text that matches one of the listed items."

According to the help file, I need to use the OnNotInList property and the NotInList event. I'm not sure how to integrate these into my database. Can someone help me? I have the following tables:

Music Categories
Recording Artists
Recordings
Switchboard Items
Tracks

I am using table Recording Artists as a subform in the main form Recordings.

I tried adding the property and event into the visual basic module and I get a "compile error, User-defined Type not Defined" error. Here are my AddToList and NotInList functions.
==========================================
Private Function AddToList(strTable As String, strField As String, _
strData As String) As Integer

' Add item to table
' Returns acDataErrAdded if successful,
' acDataErrDisplay on any error

Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "Insert into " & strTable & " (" & strField & ") Values " _
& "(" & conQuote & strData & conQuote & ")"
Set db = CurrentDb
db.Execute strSQL

ExitHere:
AddToList = acDataErrAdded
Exit Function

HandleErr:
AddToList = acDataErrDisplay
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Form_Recordings.AddToList()"
End Select
Resume ExitHere
End Function
---------------------------------------------------------
Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
' Add category item to list
On Error GoTo HandleErr

If MsgBox("Would you like to add this item to the list?", _
vbYesNo + vbQuestion, "Item not in list") = vbYes Then
Response = AddToList("Recording Artists", "RecordingArtistID", NewData)
Else
Response = acDataErrDisplay
End If

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Form_Recordings.RecordingArtistID_NotInList"
End Select
Resume ExitHere
Resume
End Sub

 

[dgm]

You have to add a reference to "Microsoft DAO object Library" in order to use "Dim db as database"
Then make a few changes, mainly your SQL statment,

Private Function AddToList(strTable As String, strField As String, _
strData As String) As Integer

' Add item to table
' Returns acDataErrAdded if successful,
' acDataErrDisplay on any error

    Dim db As Database
    Dim strSQL As String
    
    Set db = CurrentDb
    
    strSQL = "INSERT INTO [" & strTable & "] ( " & strField & " ) " & _
        "SELECT '" & strData & "' AS myname"

    Debug.Print strSQL
    db.Execute strSQL

    AddToList = acDataErrAdded
    
ExitHere:
    Exit Function

HandleErr:
    AddToList = acDataErrDisplay
    MsgBox Err & ": " & Err.Description, , "Form_Recordings.AddToList()"
    Resume ExitHere
End Function

Private Sub RecordingArtistID_NotInList(NewData As String, Response As Integer)
' Add category item to list
On Error GoTo HandleErr
    Dim strMsg As String
    
    strMsg = "Would you like to add this item to the list?"
    If MsgBox(strMsg, vbYesNo + vbQuestion, "Item not in list") = vbYes Then
        Response = AddToList("Recording Artists", "RecordingArtistID", NewData)
    Else
        Response = acDataErrDisplay
    End If

ExitHere:
    Exit Sub

HandleErr:
    MsgBox Err & ": " & Err.Description, , "Form_Recordings.RecordingArtistID_NotInList"
    Resume ExitHere

End Sub

 

[Knowbody]

I'm still stuck. Please bear with me, this is my first time doing this and so I hope I'm explaining things the way they need to be. I've looked at all the other notinlist posts on here and seen so many different responses, it's hard to troubleshoot what I've done.

I should also state that the code I'm using originally came from the Inventory sample database. I tried to go over it and make sure that I changed anything that needed to be changed.

Would it be better to discard their code? It's a simple form (looks like it should be anyway) that I want to add the artist's name if it's not already in the list without having to open up another form or anything. Once the name is typed, it's either found to be there already or it's addition to the list is confirmed with a simple Yes/No.

I appreciate the help so far. I'm actually learning from what was posted verse what I did, but I've still got a ways to go.

Erich

 

[Rich]

Access comes with a music collection db, why not use that, the wizards will build all the code for you.

 

[Knowbody]

Rich, a few reasons. One, I think I messed mine up. I have a database called musicnvideo and it has the tables in it, but no forms. I don't recall if I did that or not. Then, it is not in the database template section, so I can't just regenerate another one (unless someone here has it for Access 2000).

Second reason is it doesn't have all of the information I want it to store anyway. I've created a form that will allow me to use a barcode scanner to scan a record or cd and pull up its info. Also, scanning the barcode to input a new record ensures that it is unique, even though I could use the primary key. I also need to keep track of publishing information for the album and all of the little details in liner notes that most people don't care too much about.

And lastly, I'm trying to learn what's going on. I could pay someone (okay, not really, I'm broke) but then I still wouldn't know what's going on. I want to know why these functions work or don't work because I'll probably need to use something just like it on another project later.

I hate reading threads on messageboards and feeling clueless. Even though I'm not trying to become a programmer, (forgive me if anyone takes offense at that title) but I do wanna be bale to follow along. I can read Spanish fairly well enough to understand then general conversation although I can't really speak or write it. I'm trying to get to at least that same proficiency with Access and other software and doing it for myself (with a little/lot of coaching from you great folks online) is helping me tremendously.

I wish I could use a template. I would actually be getting some sleep then.

 

[Rich]

I'm not sure where Spanish comes into it, but I accept that db's built by the wizards are basic, I've added notes, track author's/length etc to mine, but most of the original work was done for me.
I'm certain that the music db in97 ver can still be downloaded from the MS Knowledge base, there are many here who will help with any problems you have

 

[Knowbody]

You might be right on that template thing. I just DLed it from the office update site. Theirs looks a lot better than mine. don't like the double-click and add to form set up the way they use it. I'd really like to do away with having to type the info only to find it's not there and then double-clicking the list, filling in whatever form pops up and then going back to the main form and having to select what you just did.

I am going to play around with it some and compare it to what I've got. I just hate giving up when I know I've got one problem with an otherwise perfect (for my uses) database.

I'll play with these side by side for a while and do a little more reading on building databases and then I may be checking back in with you all. It's good to know that there are a lot of helpful people online.

 

[Rich]

I use this method which opens another form to allow me to add the supplier and other details without using the dbl click
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim Msg, Style, Title, MyString

Beep
Msg = "You have entered a supplier that has not been recorded do you wish to enter this supplier or remove the entry. Select yes to add supplier, No to cancel entry. "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "No supplier details listed"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Suppliers", acNormal, , , acAdd, acDialog, NewData

Response = acDataErrAdded
End If
End Sub

 

[3699wat]

Here's what I have on one of my forms. It warns the user when an item is not in the list and allows the user to add a new item or go out and clear the box.

Set Limit to List property to Yes

and the following code to the NotInList property

Private Sub FieldName_NotInList(NewData As String, Response As Integer)
Dim ans As Long

ans = MsgBox("You entered a value that is not in the list. Would you like to continue and add it?", vbYesNo + vbDefaultButton1 + vbExclamation, "Add New Value?")
If ans = 1 Then
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblYourTable")
With rs
.AddNew
!LocIncident = NewData
.Update
.Close
End With
Response = acDataErrAdded
Else
MsgBox "Clear your entry or select a value from the list.", vbOKOnly, "Addition cancelled"
Response = acDataErrContinue
End If
Set rs = Nothing
End Sub

Good luck