In a table I have defined a field as a lookup combo box and added some values
to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
value automatically gets added to the filed value list in the table.

Thanks

Tailor the following to suit your needs but basically this asks for the new
input, adds it to the table and refreshes the display of the combobox.

Dim strAnswer As String

strAnswer = InputBox("Enter new data for the list", "New Data")
If strAnswer <> "" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO YourTable (FieldName) VALUES ('" &
strAnswer & "')"
DoCmd.SetWarnings True
YourComboBox.Requery
End If

"Nigel" wrote:

> In a table I have defined a field as a lookup combo box and added some values
> to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
> value automatically gets added to the filed value list in the table.
>
> Thanks

How would I add the new value to the row source on the field properties, I
allready have Ship Complete and ship Direct the user adds Ship ASAP to the
How do I get that on the rowsource
"Dennis" wrote:

> Tailor the following to suit your needs but basically this asks for the new
> input, adds it to the table and refreshes the display of the combobox.
>
> Dim strAnswer As String
>
> strAnswer = InputBox("Enter new data for the list", "New Data")
> If strAnswer <> "" Then
> DoCmd.SetWarnings False
> DoCmd.RunSQL "INSERT INTO YourTable (FieldName) VALUES ('" &
> strAnswer & "')"
> DoCmd.SetWarnings True
> YourComboBox.Requery
> End If
>
> "Nigel" wrote:
>
> > In a table I have defined a field as a lookup combo box and added some values
> > to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
> > value automatically gets added to the filed value list in the table.
> >
> > Thanks

I have worked out how to add the new value to the rowsource, but the issue I
am having is whatever I enter adds it to the list even if it allready exists,
how can I check to see if the value is allready there and if it is do not add
it

THanks

"Nigel" wrote:

> In a table I have defined a field as a lookup combo box and added some values
> to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
> value automatically gets added to the filed value list in the table.
>
> Thanks

If you are allowing additions to the look up then the rowsource should be
based on a tables contents instead of hard coded values. Once you have added
the value to the look up table, the look up rowsource will be automatically
updated.

"Nigel" wrote:

> How would I add the new value to the row source on the field properties, I
> allready have Ship Complete and ship Direct the user adds Ship ASAP to the
> How do I get that on the rowsource
> "Dennis" wrote:
>
> > Tailor the following to suit your needs but basically this asks for the new
> > input, adds it to the table and refreshes the display of the combobox.
> >
> > Dim strAnswer As String
> >
> > strAnswer = InputBox("Enter new data for the list", "New Data")
> > If strAnswer <> "" Then
> > DoCmd.SetWarnings False
> > DoCmd.RunSQL "INSERT INTO YourTable (FieldName) VALUES ('" &
> > strAnswer & "')"
> > DoCmd.SetWarnings True
> > YourComboBox.Requery
> > End If
> >
> > "Nigel" wrote:
> >
> > > In a table I have defined a field as a lookup combo box and added some values
> > > to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
> > > value automatically gets added to the filed value list in the table.
> > >
> > > Thanks

The answer depends on whether your row source is hard coded or a lookup to a
tables values.

"Nigel" wrote:

> I have worked out how to add the new value to the rowsource, but the issue I
> am having is whatever I enter adds it to the list even if it allready exists,
> how can I check to see if the value is allready there and if it is do not add
> it
>
> THanks
>
> "Nigel" wrote:
>
> > In a table I have defined a field as a lookup combo box and added some values
> > to eg 1,2,3. If on a form the user needs to enter 4 how can I code it so that
> > value automatically gets added to the filed value list in the table.
> >
> > Thanks