Loop Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a certain number of records - say 4 records. A field
called Yr (year) has the following values in these 4 records:
1
3
4
8

If I wanted 10 records in this table, each numbered 1 to 10, how can I with
vba create those 6 missing records and have the Yr field numbered
appropriately?!

Seems ridiculously easy but I am really stumped. Suspect it has something
to do with setting up two nested for/next loops. Is there SQL that can
handle this without having to resort to recordsets?

Anyone care to prove to me how easy this should be!
Many thanks in advance
JW/
 
try something like this (not tried myself!):

sub AddMissingrecords
dim rs as recordset
dim n as integer, i as integer

set rs = currentdb.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)
n = 1
rs.movefirst
while not rs.eof
select case rs.fields!Yr
case = n
' the record is existing, just increase counter
n = n+ 1
case < n
' do nothing, i.e. go to next record read
case > n
' we are missing records
for i = rs.fields!Yr to n
currentdb.execute("insert into MyTable (..., Yr, ...) values (...,
" & n & ", ...)")
next
n = rs.fields!Yr
case else
end select
rs.movenext
wend
end sub

hth
Balex
 
Here is a routine that will do it for your. Just pass it the highest ID
number you want in the table, and it will create a new record for each number
from 1 to the number of records you pass to it:

Public Sub FixTable(lngMaxRecs)
Dim rst As Recordset
Dim lngCtr As Long

Set rst = CurrentDb.OpenRecordset("table1", dbOpenDynaset)

With rst
For lngCtr = 1 To lngMaxRecs
.FindFirst "ID = " & lngCtr
If .NoMatch Then
.AddNew
!ID = lngCtr
!stuff = "This is " & Format(lngCtr, "000")
.Update
End If
Next lngCtr
.Close
End With
Set rst = Nothing
End Sub
 
J said:
I have a table with a certain number of records - say 4 records. A field
called Yr (year) has the following values in these 4 records:
1
3
4
8

If I wanted 10 records in this table, each numbered 1 to 10, how can I with
vba create those 6 missing records and have the Yr field numbered
appropriately?!

Seems ridiculously easy but I am really stumped. Suspect it has something
to do with setting up two nested for/next loops. Is there SQL that can
handle this without having to resort to recordsets?

Anyone care to prove to me how easy this should be!
Click to expand...


You don't need any loops. If you have a table (named
Numbers) with one field (named Num) that's populated with
the values 1 through say 100 or 1000 or whatever, then you
can do it all with a query.

INSERT INTO table (Yr)
SELECT Numbers.Num
FROM table RIGHT JOIN Numbers
ON table.Yr = Numbers.Num
WHERE Numbers.Num Between 1 And 10
AND table.Yr Is Null

If you are concerned about adding a new table to you
application, be assured that there are several other
situation where the Numbers table can be valuable. I have
such a table in everyone of my apps.
 
Thanks to everyone on this issue

I have implemented Marshall's query and works just fine

Marshall - yes, was trying to avoid having to have a 'master number table'
but I take your point about it being valuable in other areas of my db

Many thanks again
James
 
Back
Top