Combo Box to add items to a table

[Jon M.]

A good Friday to everyone,
I have a database, obviously, with a form using a combo box. The combo box
pulls from a table called CustDesc. What I am looking for a way to do is:
If the user can't find the custom description on the drop-down menu of the
combo box that they like, I want them to be able to enter their own
description in the combo box, that works fine. But what I really want is for
that new description to be added to the table so next time it's an option in
the drop down. I tired using this code I got from Dev Ashish at
http://www.mvps.org/access/forms/frm0015.htm and modified it a little. I'm
using Access 2002 and I keep getting an error where it says "Compile Eror:
User-defined type not defined", and the Dim db As DAO.Database line is
highlighted. I have no idea what that means.

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

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("CustDesc", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = 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

Am I going about this all wrong? Is there a better way to be doing this?
What I am focusing on is ease of use for the end user, the simpler I can make
for them the better off life will be in the long run. Thanks for any help
you can offer.

 

[Marshall Barton]

Jon M. wrote:,

I have a database, obviously, with a form using a combo box. The combo box
pulls from a table called CustDesc. What I am looking for a way to do is:
If the user can't find the custom description on the drop-down menu of the
combo box that they like, I want them to be able to enter their own
description in the combo box, that works fine. But what I really want is for
that new description to be added to the table so next time it's an option in
the drop down. I tired using this code I got from Dev Ashish at
http://www.mvps.org/access/forms/frm0015.htm and modified it a little. I'm
using Access 2002 and I keep getting an error where it says "Compile Eror:
User-defined type not defined", and the Dim db As DAO.Database line is
highlighted. I have no idea what that means.

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

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("CustDesc", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = 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

The code looks ok to me.

That error implies that you do not have the DAO library in
your References. While you are in any code module, use the
Visual Basic Editor - Tools - References menu item. Look
down the list for the Microsoft DAO 3.6 library and make
sure it is checked. Unless you have a real need for it, you
should also uncheck any Microsoft AvtiveX Data Objects
libraries that are checked.

 

[Douglas J. Steele]

While in the VB Editor, select Tools | References from the menu. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it, then back out of the dialog.

 

[Jon M.]

I can't believe it was that easy. Thank you for your help!

 

[Jon M.]

I have one more question. On this form I also have a field that is ItemNum.
This is a text box that is already completed. How can I modify this code so
that when I do add a new item to my combo box the item# also gets added to
the same new record as the combo box field which is CustDesc?