473,324 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

copy the value of a field in one record to rest of record, automtically

Hi, everyone,

I have a table like this, I need to fill the field [Location].
Like in the first record, [Scan_Type_ID]='LO' , [Location]=167
I need to fill out rest of [Location] field with the value 167 until I
find another [Scan_Type_ID]='LO' then do the same thing for rest of
table.
Scan_Type_ID Location Cage_ID
LO 167
CC 26678
CC 3669
CC 235689
LO 770
CC 77788
CC 23
LO 17
CC 23123
CC 256
CC 14
CC 56

How can I do this automaticlly, because I have a huge table to update.

Thanks,

Steph
Nov 12 '05 #1
1 1588
yi*******@yahoo.com wrote:
Hi, everyone,

I have a table like this, I need to fill the field [Location].
Like in the first record, [Scan_Type_ID]='LO' , [Location]=167
I need to fill out rest of [Location] field with the value 167 until I
find another [Scan_Type_ID]='LO' then do the same thing for rest of
table.
Scan_Type_ID Location Cage_ID
LO 167
CC 26678
CC 3669
CC 235689
LO 770
CC 77788
CC 23
LO 17
CC 23123
CC 256
CC 14
CC 56

How can I do this automaticlly, because I have a huge table to update.

Thanks,

Steph


If you can ensure that the table will be in the same order as above.
Let's say the table is called Table1. You could put this code in a
Module and run it....change Table1 to your table name

Make a copy of the table before you run this routine to fill non-LO records.

Sub LocationFill()
Dim rst As DAO.Recordset
Dim varLocation As Variant
Set rst = Currentdb.openrecordset("Table1",dbopendynaset)
rst.movefirst

'bypass any top records not LO
Do while rst!Scan_Type_ID <> "LO" And Not rst.EOF
rst.movenext
Loop

Do while not rst.Eof
'get the location of the LO Record
varLocation = rst!Location
rst.MoveNext

'verify not at EOF
If not rst.EOF then
Do while rst!Scan_Type_ID <> "LO" _
And Not rst.EOF

'update the non-lo recs
rst.Edit
rst.Location = varLocation
rst.Update
rst.MoveNext
Loop
Endif
Loop
rst.close
set rst = Nothing

msgbox "Done updating."
End Sub
Nov 12 '05 #2

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

Similar topics

4
by: Gene | last post by:
When entering a record in a form, I would like a value in a field of the previous record to be entered automatically into a different field of the current record. Which way should I go? Is it also...
1
by: yili_tian | last post by:
Hi, everyone, I have a table like this, I need to fill the field . Like in the first record, ='LO' , =167 I need to fill out rest of field with the value 167 until I find another ='LO' then do...
1
by: PalJoey | last post by:
I am having a problem copying and pasting records into the same table. When I copy a record that has a zero length string for a field Access seems to convert it to NULL when pasted. One of the...
19
by: davidgordon | last post by:
Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products
8
by: james | last post by:
I am trying to use Filestream to read a file ( .DAT) that contains values in HEX that I want to convert to text. I know the different offset addresses for each portion of the data I am trying to...
0
by: igendreau | last post by:
I have a database with a Header table. Each record in tblHeader has two One-to-Many Relationships: with tblLines and tblKeys. The HeaderID field ties tblHeader to the other two tables. The data...
2
by: MLH | last post by:
Fields in MyTable: PostID PostDate RollQtyXfer RollDenomination RollCount37 RollCount23
2
by: Swinky | last post by:
I hope someone can help...I feel like I'm walking in the dark without a flashlight (I'm NOT a programmer but have been called to task to do some work in Access that is above my head). I have...
3
by: Richnep | last post by:
Hi all, I have tabbed subforms where I need to copy one field value from one subform over to another subform. Although I can run an update query to accomplish this I would like to do it through...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.