Ok, I figured it out I think!!
I am sure this would be useful to others in the future.
I used the code below:
Code:
Private Sub MyCombo_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
' conenction and recordset object variables
Dim cn As Connection
Dim rs As ADODB.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 connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "MyTable", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("MyField") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
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 cn = Nothing
Exit Sub
Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
Code from:
http://www.access-programmers.co.uk/...ad.php?t=61414
What this code does different from the code I wrote is that it declares the ADODB connection that I need (cs) INSIDE the NotInList event along with the recordset I will use (rs). Then what does the trick is this:
Code:
With rs
.Open "MyTable", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("MyField") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
End With
What this does is open the actual table (MyTable) in the database, adds the NewData to the field MyField, Updates the table and closes the recordset connection.
In my code, what I did was use an INSERT query to input data in. This I assume does not work because vba cannot tell if the data went in or not since I am using the GetRecords function...which also cannot tell VBA that the data went in, and so Access assumes the worst and asks to insert new data repeatedly.
Someone please correct me if I am wrong, but I believe that was the problem in my code. Hope this helps someone.
Thank You
ÃaÃeVi£
