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

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

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.