Thanks for all the input there, everybody! I have read up on Allow Value List Edits and think I understand it better.
I did get the code to fire by taking out the error handler, which was simply doing an exit sub. I'll want to redo the error handling on that sub.
Going back to the Journal Quick Add form... the field above the one that was giving me trouble is the Company_ID field. Wanting to allow for adding a company on the fly as I have done with the Contact (OP). The code is as follows:
Code:
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
'On Error GoTo ErrorHandler
Dim intAnswer As Integer
Dim db As Database
Dim rstCompanies As Recordset
intAnswer = MsgBox("Do you want to add '" & NewData & "' to the companies list?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
Set db = CurrentDb
Set rstCompanies = db.OpenRecordset("tblCompanies", dbOpenDynaset)
With rstCompanies
.AddNew
.Fields("CompanyName") = NewData
.Update
.Close
End With
db.Close
Set rstCompanies = Nothing
Set db = Nothing
Response = acDataErrAdded
Dim Answer As Integer
Answer = MsgBox("Do you want to provide other details about " & NewData & "?", vbQuestion + vbYesNo)
If Answer = vbYes Then
Dim txtform
Dim CompanyID
txtform = "Company Quick Add"
CompanyID = DLookup("Company_ID", "tblCompanies", "CompanyName = '" & NewData & "'")
Debug.Print "New CompanyID " & CompanyID
DoCmd.OpenForm txtform, acNormal, "", "[Company_ID]=" & CompanyID, acFormEdit, acWindowNormal
End If
Else
Response = acDataErrContinue
End If
Exit_Handler:
'Exit Sub
ErrorHandler:
' Resume Exit_Handler
End Sub
It runs through the point of DoCmd.OpenForm "Company Quick Add", then I get the standard NotInList message from Access. I close the Company Quick Add dialog and the original form runs the 'Do you want to add NewData to the Company list' MsgBox. Like an endless loop, adding the same company over and over again.
What's missing in my code for this combo?
Thanks for all the exellent help! I've been using Access for quite a while but I still get so stumped on code.
MIB1019