By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Can I change the rowsource bound column for existing data

P: n/a
nyt
I have a problem of number and text field.
I got the database file(mdb) that contains many combo boxes used and
its list values are created by "value list"
For eg field

Field name= 'furniture' , data type='Number' ,Display Control='Combo
Box', RowSource Type = 'Value List' and
Row Source = ' 0;"chair";1;"Table";2;"Bed" '

Therefore, in data sheet view of table, if we select (1 : Table ) ,
then the value 1 is stored in furniture field.

I want to change the data type of this field 'furniture' to 'Text' and
the data to be assoiated text 'Table' instead of '1' for all data
records entered.

Is there any way to transform all records? please!!!!!!!!!!! help me
i haeve thousands of records to change such fields from 'number' to
corresponding 'text'

Waiting ur reply as soon as possible ,
NYT

Nov 25 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Do you really want to do this?
You probably have a furniture table that looks like:
TblFurniture
FurnitureID
FurnitureItem
Each furniture item is identified by a unique FurnitureID.

Somewhere in your database you have another table where FurnitureID is a
foreign key Perhaps a house contents table that looks like:
TblHouseContents
HouseContentID
FurnitureID
etc

In this table, furniture items are recorded by their FurnitureID and not
their name. This is the correct way of doing it in a relational database. So
for data entry into this table, your form needs a combobox to select the
furniture item. Because of the table, the value of the combobox must be a
number (FurnitureID). Therefore your Value List is correct as is.

Let us know if your database is like this or is designed differently.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

"nyt" <na*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have a problem of number and text field.
I got the database file(mdb) that contains many combo boxes used and
its list values are created by "value list"
For eg field

Field name= 'furniture' , data type='Number' ,Display Control='Combo
Box', RowSource Type = 'Value List' and
Row Source = ' 0;"chair";1;"Table";2;"Bed" '

Therefore, in data sheet view of table, if we select (1 : Table ) ,
then the value 1 is stored in furniture field.

I want to change the data type of this field 'furniture' to 'Text' and
the data to be assoiated text 'Table' instead of '1' for all data
records entered.

Is there any way to transform all records? please!!!!!!!!!!! help me
i haeve thousands of records to change such fields from 'number' to
corresponding 'text'

Waiting ur reply as soon as possible ,
NYT

Nov 25 '05 #2

P: n/a
nyt
No my database has no separate tables for each combo.
all the combo select list values are control by just a string in Row
Source as i stated , ( Row Source = ' 0;"chair";1;"Table";2;"Bed" ' ) ,
bound column = 1 , Count Column = 2

i tried by changing the bound column to 1, but it is not OK for
existing data, and only OK for new entriy data.

i have about 70 such combos to change.

Therefore, if another way of solution, PLEASE!!!!!!!!!!!!!!!!!

with regards,
nyt

Nov 25 '05 #3

P: n/a
1. Add a new field to your existing table. Name it (say) FurnitureTypeID. It
can be a text field, and set its Field Size property to whatever is enough
(perhaps 24 or 40 characters.) Save the table.

2. To populate this new field, create a query into this table. Change it to
an Update query (Update on Query menu, in query design view.) Access adds an
Update row to the grid.

3. Drag the Furniture field into the grid.
In the Criteria row under this field enter:
0

4. Drag the FurnitureTypeID field into the grid. In the Criteria row under
this field, enter:
Is Null
In the Update row under this field, enter:
"chair"

5. Run the query, by clicking the exclamation icon on the toolbar.

6. Back to design view, change the Criteria under Furniture to:
1
and the Update row under FurnitureTypeID to:
"table"
Run the query again.

7. Repeat step 6 for the other values as well.

8. After verifying that all the entries are correct, you can delete the
original Furniture field.

To make the task much easier later, it would be a good idea to create a
table to hold all the valid values, and use that as the source for the
combo. To do that as well:
1. Create a new table named (say) FurnitureType, with one Text field named
FurnitureTypeID.

2. Create a query into your original table, selecting just the
FurnitureTypeID field. In the Criteria row under this field, enter:
Is Not Null

3. In the Properties box (View menu), set the query's Unique Values property
to Yes.

4. Change it to an Append query (Append on Query menu).
Tell Access you want to append to the FurnitureType table.

5. Run the query. The table now contains the valid choices.

6. Choose Relationships on the Tools menu, and add both tables to the
screen. Then drag FurnitureTypeID from the FurnitureType table onto the
matching FurnitureTypeID field in the other table. When you let go, Access
pops up the Create Relation dialog. Check the box for Relational Integrity,
and Ok the dialog.

You are now assured that only valid values can appear in the list, but it is
much easier to maintain than the value list.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nyt" <na*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have a problem of number and text field.
I got the database file(mdb) that contains many combo boxes used and
its list values are created by "value list"
For eg field

Field name= 'furniture' , data type='Number' ,Display Control='Combo
Box', RowSource Type = 'Value List' and
Row Source = ' 0;"chair";1;"Table";2;"Bed" '

Therefore, in data sheet view of table, if we select (1 : Table ) ,
then the value 1 is stored in furniture field.

I want to change the data type of this field 'furniture' to 'Text' and
the data to be assoiated text 'Table' instead of '1' for all data
records entered.

Is there any way to transform all records? please!!!!!!!!!!! help me
i haeve thousands of records to change such fields from 'number' to
corresponding 'text'

Waiting ur reply as soon as possible ,
NYT

Nov 25 '05 #4

P: n/a
nyt
Thanks for ur solution, it work well
but i have to take 6 times (6 items )on each combo.
So it will take so much time for over 70 combos and about 6 items in
each combos ( 70 x 6 ) times will need

is there another way to make by programming coding in vba or in vb

with regards,
nyt

Nov 25 '05 #5

P: n/a
If you are comfortable writing VBA code, you could OpenRecordset using the
SELECT DISTINCT to get the unqiue values. Loop through that recordset,
creating the UPDATE query SQL string for each of the values, and Execute
it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nyt" <na*****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Thanks for ur solution, it work well
but i have to take 6 times (6 items )on each combo.
So it will take so much time for over 70 combos and about 6 items in
each combos ( 70 x 6 ) times will need

is there another way to make by programming coding in vba or in vb

with regards,
nyt

Nov 25 '05 #6

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message
news:D9****************@newsread1.news.atl.earthli nk.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.


I don't know about anyone else but I don't have a problem with this at all.
Have we finally reached a state of harmony?
Nov 25 '05 #7

P: n/a
Still blatantly jobhunting IMO.
==> I would like him to go back to his old sigline.
That sig is 'inviting' enough. We *did* have enough discussion about that ...

Also I don't believe a thing of this 1000 Access users.
1000 users is 1000 databases / problems to work on in 4-5 years??
Or does he mean 1000 emails?

Arno R

"Keith W" <he**@there.com> schreef in bericht news:43**********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:D9****************@newsread1.news.atl.earthli nk.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.


I don't know about anyone else but I don't have a problem with this at all.
Have we finally reached a state of harmony?


Nov 25 '05 #8

P: n/a
"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...
Still blatantly jobhunting IMO.
==> I would like him to go back to his old sigline.
That sig is 'inviting' enough. We *did* have enough discussion about that
....

Also I don't believe a thing of this 1000 Access users.
1000 users is 1000 databases / problems to work on in 4-5 years??
Or does he mean 1000 emails?

I don't believe it either Arno, but I don't care whether or not it's true
and I think it's up to the individual to make that judgement. It still
isn't 100% by the book and I too would like to see the job hunting aspect
completely removed but at least now it's relatively brief and understated
and doesn't form the bulk of his post. :o)
Nov 25 '05 #9

P: n/a
nyt
i am not very good at vba
so please show me some hints

regards,
nyt

Nov 25 '05 #10

P: n/a

"Keith W" <he**@there.com> wrote in message
news:43**********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:D9****************@newsread1.news.atl.earthli nk.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

I don't know about anyone else but I don't have a problem with this at

all. Have we finally reached a state of harmony?


Personally, I think it's shameful. Hundreds of consultants post help to
users in CDMA, many frequently. Steve is the only one of them who feels he
is above the charter prohibition on advertising. If no one tells Steve he
can't advertise, there is no reason for all of the others to not do so. But,
like I said before, if others prefer that I not pursue it, I won't.

Randy

Nov 25 '05 #11

P: n/a

"Randy Harris" <ra***@SpamFree.com> schreef in bericht news:pt*****************@newssvr22.news.prodigy.ne t...
> Over 1000 Access users have come to me for help. My fees are very
> reasonable.

Where I live, we would call this sheer jobhunting.
Personally, I think it's shameful. Hundreds of consultants post help to
users in CDMA, many frequently. Steve is the only one of them who feels he
is above the charter prohibition on advertising. If no one tells Steve he
can't advertise, there is no reason for all of the others to not do so. But,
like I said before, if others prefer that I not pursue it, I won't.


Steve is just a disgrace for newsgroups.

Arno R
Nov 25 '05 #12

P: n/a
Why don't you take Salad's advise and grow up and get a life and quit
sending emails to my office.

"If you have anything to contribute to the group, contribute. Your war
with Datasheet is simply pissing and moaning in the wind for the sake of
nothing. Grow up and get a life."

Salad

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...
Still blatantly jobhunting IMO.
==> I would like him to go back to his old sigline.
That sig is 'inviting' enough. We *did* have enough discussion about that
....

Also I don't believe a thing of this 1000 Access users.
1000 users is 1000 databases / problems to work on in 4-5 years??
Or does he mean 1000 emails?

Arno R

"Keith W" <he**@there.com> schreef in bericht
news:43**********@glkas0286.greenlnk.net...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:D9****************@newsread1.news.atl.earthli nk.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.


I don't know about anyone else but I don't have a problem with this at
all.
Have we finally reached a state of harmony?

Nov 25 '05 #13

P: n/a
Why don't you take Salad's advise and grow up and get a life and quit
sending emails to my office.

"If you have anything to contribute to the group, contribute. Your war
with Datasheet is simply pissing and moaning in the wind for the sake of
nothing. Grow up and get a life."

Salad

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.


"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...

"Randy Harris" <ra***@SpamFree.com> schreef in bericht
news:pt*****************@newssvr22.news.prodigy.ne t...
> Over 1000 Access users have come to me for help. My fees are very
> reasonable.

Where I live, we would call this sheer jobhunting.
Personally, I think it's shameful. Hundreds of consultants post help to
users in CDMA, many frequently. Steve is the only one of them who feels he
is above the charter prohibition on advertising. If no one tells Steve he
can't advertise, there is no reason for all of the others to not do so.
But,
like I said before, if others prefer that I not pursue it, I won't.


Steve is just a disgrace for newsgroups.

Arno R
Nov 25 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.