473,699 Members | 2,165 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can I change the rowsource bound column for existing data

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;"Ta ble";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
13 4175
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:
TblHouseContent s
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******@pcdata sheet.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.goo glegroups.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;"Ta ble";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
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;"Ta ble";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
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.goo glegroups.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;"Ta ble";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
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
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.goo glegroups.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
"PC Datasheet" <no****@nospam. spam> wrote in message
news:D9******** ********@newsre ad1.news.atl.ea rthlink.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.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
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.gr eenlnk.net...
"PC Datasheet" <no****@nospam. spam> wrote in message
news:D9******** ********@newsre ad1.news.atl.ea rthlink.net...

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.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
"Arno R" <ar***********@ tiscali.nl> wrote in message
news:43******** ************@dr eader2.news.tis cali.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
nyt
i am not very good at vba
so please show me some hints

regards,
nyt

Nov 25 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
50650
by: Robert Stearns | last post by:
Either I missed something, or ALTER TABLE does not have this capability. Is there any way of doing it except DROPping all constraints which mention this table, EXPORTing the data, DROPping the table, reCREATEing the table without the 'NOT NULL property, reCREATEing the INDEXes, reloading the data, redefining all of DROPped constraints reCREATE the view which were marked inactive by the above.
3
9365
by: B | last post by:
I know there are several ways to speed up combo boxes and form loading. Most of the solutions leave rowsource of the combo box blank and set the rowsource to a saved query or an SQL with a where clause after users typed in one or several letters/digits. My problem is as follows Most of the time I need to display form in continuous format, that means the combo box will appear in each record. For example I have a form to let users view...
4
2603
by: DebbieG | last post by:
I have a form based on this query: SELECT Students.LastSerDT, OtherInfo.Served, OtherInfo.HSGradYr, OtherInfo.ActivePart, OtherInfo.Served, Students.SSN, & ", " & & " " & AS Name, Students.LastNM, Students.FirstNM, Students.MI, Students.DOB, Students.GenderCD, Students.EthnicityCD, Students.EligibilityCD, Students.UBInitiative, Students.NCESSchID, Students.ProjEntryDT, Students.ProjReEntDT, Students.LastSerDT, Students.Reason,...
8
6155
by: bbdata | last post by:
ok i have a problem here and not much time to play round. have a form bound to a table. one of the combos is bound to a field Agents. i have active and retired agents. thing is, i want to be able to retrieve all agents from the table, but to see only active in dropdown list of the combo, to asure accurate data entry. if i filter the agents table for a rowsource to get back only active in the dropdown, i cant retreive names of the rest when...
3
2436
by: Richard Hollenbeck | last post by:
I have a column in a table that currently only has one record. That column is set to combo box. I want the user to be able to lookup values from the column itself--the very same column--rather than a long list of values in the rowsource. Then, if the value doesn't exist, the user could type it in, and the next time that value is needed again it will be there in the list without having to manually add that value to the rowsource. Is this...
0
2015
by: Frnak McKenney | last post by:
Can I use a bound ComboBox for both browsing and editing? I'm working on a small, standalone database application using Visual C#.NET 2003 and an Access data file. In order to keep the number of different screens down to a minimum, I'm trying to use the same Windows Forms for both browsing and for updating. This works fine for TextBoxes, but I'm running into problems with my DropDownLists (ComboBoxes).
5
2592
by: njb35 | last post by:
Hi all I'm beginning my foray from VBA into VB 2005 Express, and enjoying some of the efficiencies it provides! I'm stuck with some dataset handling however that I _think_ can be automated but I can probably code what I want to do the hard way. I've searching around online but can't find an answer to this specific question. Here's the situation: I have a dataset table with 3 fields: one indexed as a primary key and the other two...
11
7451
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RowSource of a Master Report is: Me.RowSource = "TableOrQueryName"
5
1981
by: agarwasa2008 | last post by:
Hi, I have a linked table called tbltest and some bounded forms (which add, update, delete records) that were created using that linked table. For some necessary reasons I had to create another linked table called tblComponents and then I bounded the original forms to this new table. I did change the control source: Description (Field name) RowSource Type: Table/Query (which remains the same)
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8929
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7759
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4380
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3061
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2357
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2013
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.