Add Item to Table

[Roger Bell]

I have a relational database which includes forms to enter data. One field
is called "Item" which has a drop down list and linked to the respective
Table. If there is a new item typed into this field, is there a way that the
user can have the new Item added to the Table without having to open the
Table and type the Item in.
Thanks for any assistance

 

[Allen Browne]

See:
Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

 

[Nick ''The Database Guy'' McMillen]

Hi Roger,

Why don't you try pasting in the following code. As I am sure you will see
it asks for confirmation before adding the data.

Private Sub cboBookingName_NotInList(NewData As String, Response As Integer)
Dim rs As New ADODB.Recordset
Dim strSQL As String
If MsgBox "Are you sure that you want to add " & NewData & " to the
list?", vbYesNo + vbQuestion, "Please confirm") = vbYes then
strSQL = "SELECT Unit "
strSQL = strSQL & "FROM tblUnit"
With rs
.Open strSQL, CurrentProject.AccessConnection, adOpenDynamic,
adLockOptimistic
.AddNew
!Unit = NewData
.Update
End With
Response = acDataErrAdded
End If
End Sub

Good luck

Nick