Change a Value List of a combo box

[lwells]

Can the Value List in a combo box rowsource be changed with code. I have a
table that is used to sync with another program that reads the value list of
a combo box. I would like to be able to use code to change this value list
without opening the table properties directly to type in the new list. Is
this possible?
Any ideas would greatly be appreciated.
Thanks,
Les

 

[Jeff Boyce]

Les

If the values you wish to list in a combo box change, perhaps you need to
consider using a table as a source, rather than a value list.

While the value list provides an easy way to create a limited number of
"lookup" values, a longer list or one that can change is better managed via
a table.

Good luck

Jeff Boyce
<Access MVP>

 

[lwells]

Thank you for the reply Jeff,

I do have the combo box values stored in another table that is used for the
main database. The table that has to be sync with the other non-access
program, can not read a rowsource from a table. Thus I had to enter those
items into a separate table as a value list. The second problem was that
this other non-access program will only read a text value from just one of
the columns in the combo box list. So what happens, as the combobox list
changes in the main database, the user then has to go to the specific table
that sync's with this non-access program and change the text values directly
there for each combo box. There are ten total. What I was hoping for was to
change those text values with code whenever the list changed in the main
access table. Basically, the non-access program stores new data off-site,
and then is synchronized to the main database at a later date and after
uploading the new data, it then checks and downloads any changes to the combo
box value lists. Everything works fine, except I can foresee problems if the
Value List entered was mispelled by the end user, because then it will not
sync properly to the main table. Right now I am using code to compare the
two tables to establish what the primary key would be for the combo box text
values prior to uploading and then converting the text value back to the
original primary key. If I can accomplish this one last task, then I can
lock down the database from any unwanted or inadvertent changes to the tables
or layout.

Thanks in advance for any additional insight you might have,
Les

 

[david epsom dot com dot au]

Yes, you can use code to change the value list of a combo box.
What RowSourceType value and RowSource value do you see if
you look at the Data Properties of that combo box?

(david)

 

[lwells]

Hi David and thank you for the reply.

The combo box Value List that is stored in the "table" is where I need to
make the changes not on a form. This is so this "Value List" can be passed
to another non-access program. All I want to do is to edit or add to this
list, in this table only with out having to open the table in properties
view. In my previous post, mispellings when compared to the maindata base
when uploading data, creates sync problems. If I can code in the changes
when the user has made a change in the main database, then this table will
have the exact spelling as it was entered in the main database. Simply put,
they sometimes forget to make the changes in this table manually, other times
in haste they incorrectly type in the values, or add additional characters
like comma's or colons that weren't in the main table. Then it is only
discovered when things don't sync properly. Hope this explains better what I
am after.

Any ideas are greatly appreciated.
Les

 

[Tim Ferguson]

All I want to do is to edit or add to this
list, in this table only with out having to open the table in properties
view.

The Rowsource of a listbox is a read-write property. You can update it
easily using vba. This asumes that the RowSourceType is already set to
ValueList:

strSource = lisValues.RowSource
strSource = strSource & ";" & "Another option"
lisValues.RowSource = strSource

' can't remember if this line is needed, but it's
' unlikely to do any harm: try running it with and
' without.
lisValues.Requery



More details in help: look up properties for the ListBox Control.

HTH


Tim F

 

[david epsom dot com dot au]

You have a table, and you wish to change the data in that table?
Also, you have another table, and you wish it's data to be
synchronised with the data in the first table?

What is the name of the tables, and show us an example of the
data that is in a records in those tables?

(david)

 

[lwells]

Hi Tim and thanks for the reply,

But unfortunately, I am trying to write to the table, not to a control on a
form.

I appreciate everyone that responded in regards to my question, but I
really want , is to write to the table, not to controls on a form. I know
the user can open the table properties and enter the Value List there, but I
was wanting to prevent opening the tables in design mode if possible. This
particular table is not associated with any forms or queries, it is just for
read only to another non-access program.

Thanks,
Les

 

[lwells]

Hi David,

I will make it as simple as I can.
TableA has a value list for example "Good";"Fair";"Poor"
TableB has the rowsource from a table that includes the primary key, the
text shown above as well as several other columns that hold numeric values
for calculation based on Good Fair and Poor. The tableA is used to upload
this Value List into a hand held device for remote inspection where only
those three text items are required. When the remote data is uploaded back
into the tableA, a procedure was written to retrieve this text value, convert
it back to the primary key value so calculations can be performed and then
save into the main tables. The problem comes in when the text descriptions
are changed or added.

Example: A new list was made to show Excellent, Good, Fair and Poor. While
in the main access program, that is done with the standard forms and updating
procedures. But you have to actually go into the table properties of TableA
in design mode to add the word Excellent to the Value List.

Problem is: When someone adds to the main database the word Excellent, and
then types Excellant into the Value List. That simple mispelling causes the
procedure to not read it correctly and therefore ignores changing the text
value back into the primary key value to work with. And you already know
what happens when trying to insert text values into numeric fields. Data
Type mismatch is the keyword among other errors. To add to this scenerio,
there are 10 combo boxes to contend with, each with different text
descriptions.

The hand held device can store numbers or text but not both in the same
combo field. The combo box field can only have one column. So the user has
decided to use the text description for the hand held and not a primary key
number. Which makes sense after all the user has no idea what 21 or 42 would
be.

So what I was wanting to do, was when they made a change in the main
database was to write a procedure that would add to the Value List in tableA
the new description exactly as it was spelled in the main table. I have
written some code to find and trap this error, but would just like to prevent
it from happening in the first place. If I can write this type of procedure,
I would be able to keep the tables more secured and less tempting to
"experiment" for those less familiar with databases.

If you have any ideas, I am all ears.

Thanks again for the reply and interest in seeing if it was possible to do
something like this.
Les

 

[david epsom dot com dot au]

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[lwells]

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet, but I
will keep working at it. Most of the variations seem to add a record and not
change the value list of the drop down box in the table that I was looking
for. Was this code to add a record or to actually change the rowsource List
of text descriptions for the field that has a combo box with a row source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure it
out, or just scrape the idea totally.

Much appreciated,
Les

 

[Brendan Reynolds]

This is a lookup field we're talking about, right?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet, but
I
will keep working at it. Most of the variations seem to add a record and
not
change the value list of the drop down box in the table that I was looking
for. Was this code to add a record or to actually change the rowsource
List
of text descriptions for the field that has a combo box with a row source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure it
out, or just scrape the idea totally.

Much appreciated,
Les

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[lwells]

Hi Brendan,

The combobox is not a lookup in the table that has the rowsource type as
Value List...it is just used as a selection dropdown to choose
"Good";"Fair";"Poor" and place that text into a text field. Once the data is
obtained it is then uploaded into a table and from there the rest of the
procedures are run.

What I would like to do is to change this List using code rather than
opening the table in design view and going to the properties of the combobox
to make any changes.

Will that help clarify?

Much appreciate all those trying to answer my questions.
Les

This is a lookup field we're talking about, right?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet, but
I
will keep working at it. Most of the variations seem to add a record and
not
change the value list of the drop down box in the table that I was looking
for. Was this code to add a record or to actually change the rowsource
List
of text descriptions for the field that has a combo box with a row source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure it
out, or just scrape the idea totally.

Much appreciated,
Les

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[david epsom dot com dot au]

Tim Ferguson has already shown you how to change a combo box
rowsource where the type is set to Value List.

I asked you this question:

What RowSourceType value and RowSource value do you see if
you look at the Data Properties of that combo box?

You didn't answer.

(david)

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet, but I
will keep working at it. Most of the variations seem to add a record and not
change the value list of the drop down box in the table that I was looking
for. Was this code to add a record or to actually change the rowsource List
of text descriptions for the field that has a combo box with a row source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure it
out, or just scrape the idea totally.

Much appreciated,
Les

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[lwells]

Hi David,

Forgive me for not answering the question you asked orginally. If I have a
combobox on a form, yes the rowsource type would be Value List, and the
rowsource would be the list of the various items. Tim's method would add the
additional item to that list, but once the form has closed, the newly added
item isn't retained. In order to retain that item, it must be enter
directly into the table.

Perhaps, it would be better to say, that the Field in this table has the
Display Control set to Combo Box the Row Source Type is set to Value List
and the Row Source has the list entered like this "Good";"Fair";"Poor". This
table is not used in the database for any forms or queries or reports. This
table is uploaded. or read by a hand-held device. Basically the hand held
device is a replicate of this table, so as it synchronizes it matches each
field in the hand-held with what is in this table. So to change what the
hand-held will display after synchronization, I have to go to this table and
manually enter the changes to the row source of each field in design view of
the table.

I realize that my question has steered many people to confusion by my using
the term combo box incorrectly. A combo box is a control used on a form, and
I was talking about the field in a table. I greatly apologize for my
incorrect terminology.

However my question still is the same, is it possible to change the
rowsource of a Field in a table that has the display control set to combo box
by using code, rather than opening the table in design view. Typically I
would never ask this kind of question, except the end user has requested that
when changes are made to the fields on this table, that it be done by not
opening the table in design mode. My initial response was No, it wasn't
possible... but there are many people such as yourself, with a vast amount of
knowledge and experience at a level far above myself, so I owed it to the end
user to search further and ask the question and for myself to learn more in
the process.

Again, I truly apologize for causing so much confusion amoungst the group
here. So if there is an answer whether it be a yes or no, I would greatly
appreciate it.

Many, many thanks to all.
Les

Tim Ferguson has already shown you how to change a combo box
rowsource where the type is set to Value List.

I asked you this question:

What RowSourceType value and RowSource value do you see if
you look at the Data Properties of that combo box?

You didn't answer.

(david)

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet, but I
will keep working at it. Most of the variations seem to add a record and not
change the value list of the drop down box in the table that I was looking
for. Was this code to add a record or to actually change the rowsource List
of text descriptions for the field that has a combo box with a row source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure it
out, or just scrape the idea totally.

Much appreciated,
Les

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[Brendan Reynolds]

We are having some terminology problems, Les, as what you are describing is,
in fact, a lookup field.

It can be done. For the record, for the benefit of others who may read this
thread in the archives, I should mention that it is not an approach that I
would recommend. Value lists are best used for lists that are not expected
to change, and see the comments on lookup fields at the following URL ...

http://www.mvps.org/access/lookupfields.htm

That said, here's how to do it ...

Public Sub ModifyLookupRowSource(ByVal strNewValue As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strRowSource As String

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
Set fld = tdf.Fields("TestText")
strRowSource = fld.Properties("RowSource")
If Right$(strRowSource, 1) <> ";" Then
strRowSource = strRowSource & ";"
End If
strRowSource = strRowSource & strNewValue
fld.Properties("RowSource") = strRowSource

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi David,

Forgive me for not answering the question you asked orginally. If I have
a
combobox on a form, yes the rowsource type would be Value List, and the
rowsource would be the list of the various items. Tim's method would add
the
additional item to that list, but once the form has closed, the newly
added
item isn't retained. In order to retain that item, it must be enter
directly into the table.

Perhaps, it would be better to say, that the Field in this table has the
Display Control set to Combo Box the Row Source Type is set to Value
List
and the Row Source has the list entered like this "Good";"Fair";"Poor".
This
table is not used in the database for any forms or queries or reports.
This
table is uploaded. or read by a hand-held device. Basically the hand held
device is a replicate of this table, so as it synchronizes it matches each
field in the hand-held with what is in this table. So to change what the
hand-held will display after synchronization, I have to go to this table
and
manually enter the changes to the row source of each field in design view
of
the table.

I realize that my question has steered many people to confusion by my
using
the term combo box incorrectly. A combo box is a control used on a form,
and
I was talking about the field in a table. I greatly apologize for my
incorrect terminology.

However my question still is the same, is it possible to change the
rowsource of a Field in a table that has the display control set to combo
box
by using code, rather than opening the table in design view. Typically I
would never ask this kind of question, except the end user has requested
that
when changes are made to the fields on this table, that it be done by not
opening the table in design mode. My initial response was No, it wasn't
possible... but there are many people such as yourself, with a vast amount
of
knowledge and experience at a level far above myself, so I owed it to the
end
user to search further and ask the question and for myself to learn more
in
the process.

Again, I truly apologize for causing so much confusion amoungst the group
here. So if there is an answer whether it be a yes or no, I would greatly
appreciate it.

Many, many thanks to all.
Les

Tim Ferguson has already shown you how to change a combo box
rowsource where the type is set to Value List.

I asked you this question:

What RowSourceType value and RowSource value do you see if
you look at the Data Properties of that combo box?

You didn't answer.

(david)

Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet,
but I
will keep working at it. Most of the variations seem to add a record
and not
change the value list of the drop down box in the table that I was
looking
for. Was this code to add a record or to actually change the rowsource List
of text descriptions for the field that has a combo box with a row
source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure
it
out, or just scrape the idea totally.

Much appreciated,
Les
:

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)

 

[lwells]

Hi Brendan,

Thank you so much for the reply, and your points are well taken. Your
advice and the article explains exactly why I shouldn't think about this
approach.

For those that read this post later, Brendan's advice is right on the money.

Again thanks to all for assisting me. Great forum with great answers

Much appreciated,
Les

We are having some terminology problems, Les, as what you are describing is,
in fact, a lookup field.

It can be done. For the record, for the benefit of others who may read this
thread in the archives, I should mention that it is not an approach that I
would recommend. Value lists are best used for lists that are not expected
to change, and see the comments on lookup fields at the following URL ...

http://www.mvps.org/access/lookupfields.htm

That said, here's how to do it ...

Public Sub ModifyLookupRowSource(ByVal strNewValue As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strRowSource As String

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
Set fld = tdf.Fields("TestText")
strRowSource = fld.Properties("RowSource")
If Right$(strRowSource, 1) <> ";" Then
strRowSource = strRowSource & ";"
End If
strRowSource = strRowSource & strNewValue
fld.Properties("RowSource") = strRowSource

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi David,

Forgive me for not answering the question you asked orginally. If I have
a
combobox on a form, yes the rowsource type would be Value List, and the
rowsource would be the list of the various items. Tim's method would add
the
additional item to that list, but once the form has closed, the newly
added
item isn't retained. In order to retain that item, it must be enter
directly into the table.

Perhaps, it would be better to say, that the Field in this table has the
Display Control set to Combo Box the Row Source Type is set to Value
List
and the Row Source has the list entered like this "Good";"Fair";"Poor".
This
table is not used in the database for any forms or queries or reports.
This
table is uploaded. or read by a hand-held device. Basically the hand held
device is a replicate of this table, so as it synchronizes it matches each
field in the hand-held with what is in this table. So to change what the
hand-held will display after synchronization, I have to go to this table
and
manually enter the changes to the row source of each field in design view
of
the table.

I realize that my question has steered many people to confusion by my
using
the term combo box incorrectly. A combo box is a control used on a form,
and
I was talking about the field in a table. I greatly apologize for my
incorrect terminology.

However my question still is the same, is it possible to change the
rowsource of a Field in a table that has the display control set to combo
box
by using code, rather than opening the table in design view. Typically I
would never ask this kind of question, except the end user has requested
that
when changes are made to the fields on this table, that it be done by not
opening the table in design mode. My initial response was No, it wasn't
possible... but there are many people such as yourself, with a vast amount
of
knowledge and experience at a level far above myself, so I owed it to the
end
user to search further and ask the question and for myself to learn more
in
the process.

Again, I truly apologize for causing so much confusion amoungst the group
here. So if there is an answer whether it be a yes or no, I would greatly
appreciate it.

Many, many thanks to all.
Les

Tim Ferguson has already shown you how to change a combo box
rowsource where the type is set to Value List.

I asked you this question:

What RowSourceType value and RowSource value do you see if
you look at the Data Properties of that combo box?

You didn't answer.

(david)


Thank you David,
Haven't been able to get the INSERT INTO syntax correctly written yet,
but
I
will keep working at it. Most of the variations seem to add a record
and
not
change the value list of the drop down box in the table that I was
looking
for. Was this code to add a record or to actually change the rowsource
List
of text descriptions for the field that has a combo box with a row
source
type that is set to Value List?

Just this last question if you can answer and then eiher I will figure
it
out, or just scrape the idea totally.

Much appreciated,
Les
:

This is how you add a value to a table:

Codedb.execute "INSERT INTO TableA ( [field1] ) SELECT "Excellent";


This is how you add missing values to a table:

ssql = "INSERT INTO tableA ( [field1] ) " & _
"SELECT tableB.[field1] " & _
"FROM tableB LEFT JOIN tableA " & _
"ON tableB.[field1] = tableA.[field1] " & _
"WHERE (((tableA.[field1]) Is Null));"

Codedb.Execute ssql



(david)