Not in List Error

[scouser]

Hi guys. I have tried to implement a good pice of code I found at:

http://www.microsoft-accesssolutions.co.uk/not_in_list.htm

However I receive an error after when I choose to add the Vehicle Make that does not appear in the list?

See attcahed DB to claify things. Form of interest is frmVehicles

Thanks in advance for any help.
Phil.

 

[cstahlberg]

I think you just need to change the vb to read

strSQL = "Insert Into tblMakes ([Make]) values ('" & NewData & "')"

instead of

strSQL = "Insert Into tblMakes ([strMake]) values ('" & NewData & "')"

 

[Mile-O]

Scouser, have a look at this.

 

[scouser]

Had a look

Cheers for replying.

I think you just need to change the vb to read

strSQL = "Insert Into tblMakes ([Make]) values ('" & NewData & "')"

instead of

strSQL = "Insert Into tblMakes ([strMake]) values ('" & NewData & "')"

No I had already tried this and it did not work, but thanks for looking.

Mile-O, I read your thread and pasted in the code but I receive the following error:
Compile Error
User defined Type Not defined

It then highlights the following line:

Private Sub cboMakeList_NotInList(NewData As String, Response As Integer)

    On Error GoTo Err_ErrorHandler
    
    ' provide text constants to reduce text later and allow for faster execution
    ' due to added speed from the compilation of constants
    Const Message1 = "The data you have entered is not in the current selection."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    
    ' database and recordset object variables
    [COLOR=DarkRed]Dim db As DAO.Database[/COLOR]
    Dim rs As DAO.Recordset
    
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
        ' open a connection to the current database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblMakes")
        ' using the recordset object
        With rs
            .AddNew ' prepare to add a new record
            .Fields("Make") = NewData ' add unfound data into field
            .Update ' update the table
            .Close ' close the recordset object
        End With
        Response = acDataErrAdded ' confirm record added
    Else
        Me.MyCombo.Undo ' clear the entry in the combobox
        Response = acDataErrContinue ' confirm the record is not allowed
    End If
    
Exit_ErrorHandler:
    ' de-initialise our object variables
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
Err_ErrorHandler:
    ' display error message and error number
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler
    
    End Sub

Any thoughts?
Cheers,
Phil.

 

[Rich]

http://www.access-programmers.co.uk/forums/showthread.php?t=61060

 

[Mile-O]

Cheers, Rich.

 

[scouser]

Nice One

Many thanks guys, all working now!!

 

[Rich]

Cheers, Rich.

That's the second time within the last few days, you need to add a flashing label that says look here FIRST