Add data programatically?

[Werner]

Hi

I need a bit of urgent assistance. I have a table with the following information :
Field 1 Field 2
Number of Guests Accommodation
======================================
3 Canyon Lodge
2 Canyon Camp Site

and it must end up like this :

1 Canyon Lodge
1 Canyon Lodge
1 Canyon Lodge
1 Canyon Camp Site
1 Canyon Camp Site

I will have to do a lookup in the number of guests and replicate the record by the number of guests.

Can anyone please assist?

Kind regards

Werner

 

[rich.barry]

Try this:


Sub Expand()

Dim db As ADODB.Connection
Dim Record As New ADODB.Recordset
Dim n, l As Integer
Dim f2 As String
Dim varBookmark As Variant

Set db = CurrentProject.Connection
Record.Open "SELECT Table1.field1, Table1.field2 FROM Table1;", db, adOpenKeyset, adLockOptimistic
If Record.RecordCount > 0 Then
Record.MoveFirst
Do Until Record.EOF
If Record!field1.Value > 1 Then
varBookmark = Record.Bookmark
n = Record!field1.Value
f2 = Record!field2.Value
With Record
!field1 = 1
For l = 2 To n
.AddNew
!field1 = 1
!field2 = f2
.Update
Next l
End With
Record.Bookmark = varBookmark
End If
Record.MoveNext
Loop
End If
Record.Close

End Sub

 

[kaspi]

Hi Werner
Why do you need to replicate the records? You replication seems to be a bit unusual.
Kaspi

 

[Werner]

Thanks you very much. The problem was that I did not use ADO. When I tested the module as you supplied it, I could not make sense of it until I realized that ADO must be added. Thank you very much again.

Werner