So you are trying to normalise your data from an excel spreadsheet? So currently you have say
LOT....PLOT1....PLOT2....PLOT3 etc
1.......Harry.....Mary.......George etc?
and what you want is
LOT...PLOT...OccupiedBy
1.......1.......Harry
1.......2.......Mary
1.......3.......George
etc
If so, why do you want to use VBA? Much easier and faster to use SQL.
In SQL it would be
Code:
INSERT INTO newTable (LOT,PLOT, OccupiedBy)
SELECT LOT, 1, PLOT1 FROM sheet1
UNION SELECT LOT, 2, PLOT2 FROM sheet1
UNION SELECT LOT, 3, PLOT3 FROM sheet1
etc
in VBA it would be
Code:
dim sqlstr as string
dim db as dao.database
set db=currentdb
sqlstr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) SELECT LOT, 1, PLOT1 FROM sheet1"
db.execute sqlstr
sqlstr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) SELECT LOT, 2, PLOT2 FROM sheet1"
db.execute sqlstr
etc
both methods will create records for all LOTS - you can remove them afterwards or prevent them from being added by excluding them with criteria
You want to do it the painfully slow way? (known as RBAR)
Code:
dim rs as dao.recordset
dim db as dao.database
dim sqlStr as string
sqlStr="SELECT * FROM sheet1"
set rs=db.openrecordset(sqlstr)
while not rs.eof
sqlStr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) Values("& rs!LOT & ", 1, '" & rs!PLOT1 & "')"
db.execute sqlstr
sqlStr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) Values("& rs!LOT & ", 2, '" & rs!PLOT2 & "')"
db.execute sqlstr
etc
rs.movenext
wend