How to write a new entry in a combo box to its underlying table

[venajoe]

Dear Access 2007 VBA Gurus,

I have a assets database (rather uncreatively named "Assets"). I use a form
(named "Asset Acquisition Input Form New") to enter new assets. The
"Manufacturer" field (combo box name "Manufacturers_ID) on this form is a
lookup to a Manufacturers table. What I want to happen is when I enter an
item that is not in the lookup list, I want a message box to prompt me to add
the new entry to the underlying table, or to cancel and select an item from
the list. I have no trouble with the MsgBox command itself. What I don't
know how to do is to read the new data I've typed into the combo box (that
isn't on the list), then write it to the underlying Manufacturers table. I
think then I can use the appropriate MsgBox Response argument to requery and
add that new entry to the list.

At any rate, as you can tell, I'm a total VBA newbie. I have already spent
hours trying to peruse the Access 2007 VBA help files to figure this
out--unsuccessfully. Last night I ordered a copy of "Access 2007 VBA for
Dummies", which is apropo. In the meantime, can one of you help a VBA
ignoramus out?

Thanks!

 

[Marshall Barton]

Dear Access 2007 VBA Gurus,

I have a assets database (rather uncreatively named "Assets"). I use a form
(named "Asset Acquisition Input Form New") to enter new assets. The
"Manufacturer" field (combo box name "Manufacturers_ID) on this form is a
lookup to a Manufacturers table. What I want to happen is when I enter an
item that is not in the lookup list, I want a message box to prompt me to add
the new entry to the underlying table, or to cancel and select an item from
the list. I have no trouble with the MsgBox command itself. What I don't
know how to do is to read the new data I've typed into the combo box (that
isn't on the list), then write it to the underlying Manufacturers table. I
think then I can use the appropriate MsgBox Response argument to requery and
add that new entry to the list.

Lots of ways to do that. If the manufactures table only has
the Manufacturer field and an autonumber ID field, then you
can use an Append query to add the new record.

Use the combo box's NotInList event:

Dim SQL as String
If MsgBox("Add new entry?", ...) = vbYes Then
SQL = "INSERT INTO Manufacturers (Manufacturer) " _
& "VALUES(""" & NewData & """)"
CurrentDb.Execute SQL
Response = acDataErrAdded
Else
Me.Manufacturers_ID.Undo
Response = acDataErrContinue
End If

If there are other fields in the table that the user should
supply, then you can open a form for entry of the additional
data:

If MsgBox("Add new entry?", ...) = vbYes Then
DoCmd.OpenForm "manufacturersform", _
DataMode:= acFormAdd, _
WindowMode:= acDialog, _
OpenArgs:= NewData
Response = acDataErrAdded
Else
Me.Manufacturers_ID.Undo
Response = acDataErrContinue
End If

and in the manufacturers form's Load event:

If Not IsNull(Me.OpenArgs) Then
Me.Manufacturer = Me.OpenArgs
Me.Manufacturer.Locked = True
Me.Manufacturer.Enabled = False
End If

Be sure to check VBA Help for anything you are not familiar
with.