Adding Data to a lookup column in a form

[Alaska1]

I have a lookup column I am using in form pulling data from table. When I
add data into the lookup column it only appears in the table I am using in
the form. I also want it to be added to the table that the lookup column is
pulling the data from. How do I get it to be add to the lookup table in
addition to the main table that the form is using?

 

[Tom Wickerath]

Hi Alaska1,

Try one of the methods that Access MVP Allen Browne discusses here:

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

Say "hello" to Sarah for me!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Alaska1]

Hi Tom,

Thank you. Have you tried any of them. I am using

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new product category?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in
list") = vbYes Then

'Append the NewData as a record in the Categories table.
strTmp = "INSERT INTO Categories ( CategoryName ) " & _
"SELECT """ & NewData & """ AS CategoryName;"
DBEngine(0)(0).Execute strTmp, dbFailOnError

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded
End If
End Sub

But it does not like the code DBEngine(0)(0).Execute strTmp, dbFailOnError

keeps giving me an error.

I will say hi to Sarah.

 

[Tom Wickerath]

Yes, I use the combo box not-in-list procedures in lots of databases that I
work on. Access 2007 does have a nice feature that allows one to implement
this functionality without any code, but most of my development work is still
based on using Access 2003. I have an old sample posted on the Seattle Access
User's group site, here:

http://www.seattleaccess.org/downloads.htm

Scoll down to the bottom of the page. My sample is currently the fifth one
from the bottom:
Not In List - Detailed Instructions by Tom Wickerath

I just tried the code that you indicated does not work in the 2003 version
of Northwind, using the CategoryID combo box on the Products form. This code
works fine for me. What type of error are you getting (Error number and
description)? Could you be battling a MISSING reference error? Does your VBA
project compile without any errors?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Alaska1]

Thank you. I got the code to work. I did not like the fact the table name
and field name were the same. The feature that Access 2007 offers is nice
that you do not have to code. I am working with Access 2003. Thank you to
both for all your help.