Adding Data to a lookup column in a form
in [General]
|
Prev: cc
Next: Option Button Question
From: Alaska1 on 10 Apr 2010 16:05 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?
From: Tom Wickerath AOS168b AT comcast DOT on 10 Apr 2010 16:19 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" wrote: > 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?
From: Alaska1 on 11 Apr 2010 08:31 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" wrote: > 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" wrote: > > > 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?
From: KenSheridan via AccessMonster.com on 11 Apr 2010 11:49 You could try this alternative, which uses ADO: Private Sub CategoryID_NotInList(NewData As String, Response As Integer) Dim cmd As ADODB.Command Dim ctrl As Control Dim strSQL As String, strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add '" & NewData & "' as a new product category?" strSQL = "INSERT INTO Categories(CategoryName) VALUES(""" & _ NewData & """)" Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then cmd.CommandText = strSQL cmd.Execute Response = acDataErrAdded Else Response = acDataErrContinue ctrl.Undo End If End Sub Ken Sheridan Stafford, England Alaska1 wrote: >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. > >> Hi Alaska1, >> >[quoted text clipped - 15 lines] >> > 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? -- Message posted via http://www.accessmonster.com
From: Tom Wickerath AOS168b AT comcast DOT on 11 Apr 2010 13:46
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" wrote: > 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. |