add extra item to a combo box?

[Geoff Cox]

Hello,

I have a form with a combo box on it and wish to add 1 item to the
list.

How do I do this?

Thanks

Geoff

PS I can create a new combo box with an extra item but the table does
not reflect this extra item ... I can however open the form and move
to any record and use the drop down list to add the new item ...

 

[default105]

if the cbo is a bound object you can use the Not in List event and program it
to add the text using ado or doa to the table and then I use a macro to
requery the cbo. If it is unbound I am unsure, I have never done an unbound
before.

 

[John W. Vinson]

Hello,

I have a form with a combo box on it and wish to add 1 item to the
list.

How do I do this?

Thanks

Geoff

PS I can create a new combo box with an extra item but the table does
not reflect this extra item ... I can however open the form and move
to any record and use the drop down list to add the new item ...

What's the Row Source and Row Source Type of this combo box? If it's a Value
List you need to open the form in design view and just edit the new value into
the row source. If (better and certainly more common) it's a Query based on a
table of valid values; you just need to enter the new value into that table
(and, if necessary, requery the combo box). If you want to do it on the fly
you can use the Combo Box's Not In List event.

 

[Geoff Cox]

if the cbo is a bound object you can use the Not in List event and program it
to add the text using ado or doa to the table and then I use a macro to
requery the cbo. If it is unbound I am unsure, I have never done an unbound
before.

I assume it is bound in the sense that an item in the combox box list
is added to a field (column?) in a record in a table?

I am not clear what comes next?!

Cheers

Geoff

 

[Geoff Cox]

thanks for your reply ..

I have just tried this and can add the single item and this works fine
for the form but the field in original table is not changed. The combo
box item in the form is added to a field in a record in the table si I
guess this means it is bound?

what next?! If I use the form to add a new record the new item is
there in the field in the new record added to the table but

how do I change the field in the table so that the extra item is there
for that field in all previous records - without damaging thetable!!??

Cheers

Geoff

 

[Geoff Cox]

I seem to have solved this?!

I had missed the the fact that the options are listed under Lookup
when in the desgn view of the table.

I added the extra item, ;"extra item" and saved the new table design.

I them added the extra item into the combo box for the form.

Does above seem OK? It shoudn't damage any of the records in table
before making these changes?!!

Cheers

Geoff

 

[John W. Vinson]

thanks for your reply ..

I have just tried this and can add the single item and this works fine
for the form but the field in original table is not changed. The combo
box item in the form is added to a field in a record in the table si I
guess this means it is bound?

what next?! If I use the form to add a new record the new item is
there in the field in the new record added to the table but

how do I change the field in the table so that the extra item is there
for that field in all previous records - without damaging thetable!!??

I'm sorry, this is making no sense to me.

Perhaps you're using a Combo Box *in the Table*? if so, the data you see from
the combo box is NOT in the table, for any of the records old or new! It just
APPEARS to be, thanks to Microsoft's "Lookup Field" misfeature.

What is the actual structure of your table?
What is the RowSource of the combo box on the form?
If the table does indeed have a combo box lookup field, what is the lookup
field's RowSource?

 

[Geoff Cox]

I'm sorry, this is making no sense to me.

Perhaps you're using a Combo Box *in the Table*? if so, the data you see from
the combo box is NOT in the table, for any of the records old or new! It just
APPEARS to be, thanks to Microsoft's "Lookup Field" misfeature.

John,

I may be using the wrong terms?

All my data must be in the table surely? Where else would it be?

In the form which I now use to add new records to the table I have a
drop down list of items for one of the fields (called type) in each
record in the table - I have been calling this a combo box.

When I added an extra item to this drop down list in the form the
extra item could be used to add this extra item to the type field in a
new record in the table but the older records in the table did not
have this extra item in the drop down list for the type field.

By using design view for the table and using the Look up tab for the
type field I was able to add the extra item and it did then show up in
all the older records.

I may still be going round in a circle and confusing you further?!

Cheers

Geoff

 

[John W. Vinson]

When I added an extra item to this drop down list in the form the
extra item could be used to add this extra item to the type field in a
new record in the table but the older records in the table did not
have this extra item in the drop down list for the type field.

By using design view for the table and using the Look up tab for the
type field I was able to add the extra item and it did then show up in
all the older records.

I may still be going round in a circle and confusing you further?!

It sounds like you have TWO different combo boxes: one on the Form, the other
in the Table.

It would appear - though I might be mistaken - that these two combo boxes are
different, and get their data from different places.

The way a Combo Box works is that it "gets" information from one place, its
RowSource, and "puts" data into a different place, its Control Source.

Most serious developers would argue that a Table is simply not the right place
for a Combo Box. See
http://www.mvps.org/access/lookupfields.htm
for a critique. In a nutshell, the Lookup Field in the Table *conceals from
your view* the actual contents of the table. Your table APPEARS to have a text
value, *but it doesn't* - that's not what is actually there at all, what's
there is a number, but you can't see it!

AGAIN:

What is the RowSource for your form combo box?
What is the RowSource for your Lookup Field (on the lookup tab)?

Betcha they are different, and that is why you are having the trouble.

 

[Geoff Cox]

It would appear - though I might be mistaken - that these two combo boxes are
different, and get their data from different places.

John,

I have taken a step back ...

I have a drop down list for the field called type - in the table. The
Row source under the Lookup section is the list of type options.

I have created a new form which picks up the combo box from the table
design.

This seems OK - my question is - does the fact that I added an extra
item to the Row source in the Look up section for the field called
type in the table have any possibility of changing any of the records
which previously had 1 less item in the Row source??!!

I cannot see any changes but there are lots of records and I feel a
little nervous!

Cheers

Geoff

 

[John W. Vinson]

John,

I have taken a step back ...

I have a drop down list for the field called type - in the table. The
Row source under the Lookup section is the list of type options.

I have created a new form which picks up the combo box from the table
design.

For consistancy, make sure that the two combo boxes have the same source. If
the data in this lookup changes often (more than once or twice a year let's
say) then I'd certainly change it from "List of Values" to "Table or Query",
and create a small table of the legitimate choices which you can use as the
source of a combo box on your form. My preference would be to use the lookup
tab on the table design window to change the field from Combo Box to Textbox,
so that you can see *what the table actually contains*.

This seems OK - my question is - does the fact that I added an extra
item to the Row source in the Look up section for the field called
type in the table have any possibility of changing any of the records
which previously had 1 less item in the Row source??!!

What the table actually contains is *A NUMBER*. Changing the combo box to
display different text, or a new line of text for a new number, has absolutely
no effect on the NUMBER that has been stored in your table all along.

I cannot see any changes but there are lots of records and I feel a
little nervous!

You'll be fine. If you get rid (permanently and globally) of all table
lookups, you'll be even better, since there won't be any mysterious mismatches
between what you see one place with what you see in another.

I rarely use the "List of Values" combo choice; it's really only appropriate
for very short and totally static lists such as "M";"F" or
"Mr.";"Mrs.";"Miss";"Ms." (and even that last list has changed in my
lifetime).

 

[Geoff Cox]

For consistancy, make sure that the two combo boxes have the same source. If
the data in this lookup changes often (more than once or twice a year let's
say) then I'd certainly change it from "List of Values" to "Table or Query",
and create a small table of the legitimate choices which you can use as the
source of a combo box on your form. My preference would be to use the lookup
tab on the table design window to change the field from Combo Box to Textbox,
so that you can see *what the table actually contains*.


What the table actually contains is *A NUMBER*. Changing the combo box to
display different text, or a new line of text for a new number, has absolutely
no effect on the NUMBER that has been stored in your table all along.


You'll be fine. If you get rid (permanently and globally) of all table
lookups, you'll be even better, since there won't be any mysterious mismatches
between what you see one place with what you see in another.

I rarely use the "List of Values" combo choice; it's really only appropriate
for very short and totally static lists such as "M";"F" or
"Mr.";"Mrs.";"Miss";"Ms." (and even that last list has changed in my
lifetime).

Thanks for the explanations John. Will give them some thought
tomorrow!

Cheers

Geoff