VBA Problem

[Anthony Viscomi]

I have a ComboBox on a form that's values are contained with another table
(Dealer_Name); I would like to be able to have the following code open a
form on the "not in list event" in order to allow the user to enter a value
that isn't contain in the list. The code that I am currently using dies at
the "Set rs = db.OpenRecordset("frm_Dealer_Info", dbOpenDynaset)" line. I
pretty sure that its due to the fact that this line was only meant to open a
table not a form. Is there a way of opening a form?

Thanks in advance!
Anthony

Private Sub Dealer_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim srtMsg As String

srtMsg = "" & NewData & "'is not an avaialable Dealer Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add the new Dealer Name to the list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to
re-select."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Dealer Name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("frm_Dealer_Info", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Dealer_Name = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub

 

[Ken Snell [MVP]]

Replace the form name with the name of the table or query to which the form
is bound.

 

[Anthony Viscomi]

What I am try to do is allow the user to enter a new Dealer Name & contact
info via a form when the Dealer Name does not appear within the combo box.
The current combo box's data resides within the dealer name table.

 

[Ken Snell [MVP]]

So, replace this line

Set rs = db.OpenRecordset("frm_Dealer_Info", dbOpenDynaset)


with this line (change TableName to the dealer table):

Set rs = db.OpenRecordset("TableName", dbOpenDynaset)