edit a field using VBA...

Hi! I have a table like this...

ID      NUM      NGROUP      
1                   187      
2      187            
3      2689            
4      4901            
5      2521            
6      2433
7                   210      
8      210            
9      934            
10      1333            
11      736            
12                   218               
13      218            
14      2450            
15      653            

the problem is that i have to assign the value for NGROUP (second column) for all the records.

e.g. records from 2 to 6 should receive group 187
      records from 8 to 11 should receive group 210
      records from 13 to 15 should receive group 218

and so on on the whole table...

any clues?

the vba should actually read the NGROUP field of every record and copy it in the following ones until it finds a new value that is not null....

what is the best way to put some order in this table???

thanks in advance!!!


     
cataliniAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jmantha709Connect With a Mentor Commented:
Something like this (Put this sub in a new module, adjust it for your needs, change the table name and try it... Make a backup first) :

Public sub ArrangeTable()

dim rs as dao.recordset
dim strGroup as string

strGroup = ""
set rs = currentdb.openrecordset("YourTableName")

do while not rs.eof
  If rs("NGROUP") <> "" Then
    strGroup = rs("NGROUP")
  Else
    rs.edit
      rs("NGROUP") = strGroup
    rs.Update
  End if
  rs.movenext
loop
rs.close

End Sub
0
 
pradapkumarCommented:
Hi post some more details about your problem as detailed example.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How do you determine what NGROUP a particular record belongs to? For example, why does record #6 belong to NGROUP 187 and not 210? Without some form of logical grouping, you'll not be able to determine which records belong to what NGROUP.
0
 
jmantha709Commented:
Replace this line :

set rs = currentdb.openrecordset("YourTableName")

With :

set rs = currentdb.openrecordset("SELECT * FROM YourTableName ORDER BY ID")
0
 
cataliniAuthor Commented:
LSMconsulting... the group goes in ascending order... following the ID structure...

0
All Courses

From novice to tech pro — start learning today.