Autoadd data to a table that is used as a value list for dropdown

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created tables for my drop down box value lists as they will need
continuous additions added as time goes on. When I begin to enter data in my
form and the data is not in my drop down box, what is the easiest way to type
in the data in the form (allow not on list) and have it automatically added
to the underlying table? For instance, if my form field has a drop down box
with 5 items in it and I need to enter a 6th item, can I enter it right there
and have it added to the underlying table, or do I have to go into the table
itself, add the item and then return to the form?

Also, I have noticed that when I do add data to these tables, they are not
in alphabetical order when I go to my form and use my dropdown boxes. How
can I make the data display alphabetically in the dropdowns?
 
hi\
well i tried to understand the problem u r facing. couldnt sort out
completly what u want. but as much as what i understood u wanna update ur
dropdown list box each time a new value goes to the table it is related to.
take another form field that will add the value to the table and try
requery the form's dropdown box after the data is added to the table.
goto the row source query builder of the dropdown list and in the sort part
choose ascending
hope this works for u
 
To begin with, you *must* set the Limit to list property to Yes, so the
Not In List event will fire, and execute the rest of the code. Here's
some sample code for the Not In List vent of the combo:

Private Sub Combo1_NotInList(NewData As String, Response As Integer)
Response = False
Me.Combo1 = Null
msg = "The value: " & Chr(13) & Chr(13) & NewData & Chr(13) & Chr(13)
msg = msg & "is not in the list. Do you want it added?"
typ = vbExclamation + vbYesNo
ttl = "New Item"
resp = MsgBox(msg, typ, ttl)
If resp = vbNo Then
Exit Sub
End If
strSQL = "INSERT INTO tblLookup1 ( Field1 ) SELECT '" & NewData & "'"
CurrentDb.Execute strSQL, dbFailOnError
Me.Combo1.Requery
Me.Combo1 = NewData
Me.Text2.SetFocus
End Sub

which assumes that the combo's name is Combo1, its values being stored
in table tblLookup1, field Field1, and than the next control on the form
to move to is called Text2. Replace with the actual object names.
What the code does is, it cancels the default Access message, produces a
custom message with a choice to add the item to the list or not. and if
Yes it adds it to the list and selects it in the combo and moves focus
to the next control.

To get the items in the combo display alphabetically, simply make the
field in the lookup table the primary key of the table.

HTH,
Nikos
 
SVE, I had the same problem, and i found that i could specify an update form
that is called when you enter an item not on your list. If you select "yes"
to the "do you want to update the list" box, it opens the form that allows
you to add the new entry. When you then close the second form, it has placed
the new value in the original form. The property you need to use (I'm using
the Beta 2007 version, so I'm not sure if this will work in 2003) is Data
Tab; "list Items Edit Form". Once you select this, it shows you a list of
all your other forms - so you need to have the add to list form already built.

-Adam
 
Back
Top