Duplication of rows in Access

[GarlingBeard]

I am using the following VBA code to scan my table, locate where room number is > 1, count how many rooms, duplicate the rows. What I also need is when the rows are duplicated, I need a 1 placed in the field Room # for the first room , a 2 placed on the second room, etc. PLease look at the code and point me in the right direction, if you can:

Sub add_rooms()
Dim db As DAO.Database
Dim rst_input As DAO.Recordset
Dim counter As Integer
Dim rooms_array() As Variant
Dim row_cnt As Integer
Dim rooms_counter As Integer
Dim room_number As Integer

Set db = CurrentDb()


ReDim rooms_array(20000, 31)


Set rst_input = db.OpenRecordset("Hotel List", dbOpenDynaset)

room_number = 1
row_cnt = 0
With rst_input
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF

rooms_array(row_cnt, 0) = ![ID]
rooms_array(row_cnt, 1) = ![Account #]
rooms_array(row_cnt, 2) = ![Account Name]
rooms_array(row_cnt, 3) = ![Email Address]
rooms_array(row_cnt, 4) = ![Fax Number]
rooms_array(row_cnt, 5) = ![# Rooms]
rooms_array(row_cnt, 6) = ![1st Choice]
rooms_array(row_cnt, 7) = ![2nd Choice]
rooms_array(row_cnt, 8) = ![3rd Choice]
rooms_array(row_cnt, 9) = ![Room #1 Type]
rooms_array(row_cnt, 10) = ![#1 Smoking]
rooms_array(row_cnt, 11) = ![Arrival #1]
rooms_array(row_cnt, 12) = ![Departure #1]
rooms_array(row_cnt, 13) = ![Needs #1]
rooms_array(row_cnt, 14) = ![Room #2 Type]
rooms_array(row_cnt, 15) = ![Smoking #2]
rooms_array(row_cnt, 16) = ![Arrival #2]
rooms_array(row_cnt, 17) = ![Departure #2]
rooms_array(row_cnt, 18) = ![Needs #2]
rooms_array(row_cnt, 19) = ![Room #3 Type]
rooms_array(row_cnt, 20) = ![Smoking #3]
rooms_array(row_cnt, 21) = ![Arrival #3]
rooms_array(row_cnt, 22) = ![Departure #3]
rooms_array(row_cnt, 23) = ![Needs #3]
rooms_array(row_cnt, 24) = ![Room #4 Type]
rooms_array(row_cnt, 25) = ![Smoking #4]
rooms_array(row_cnt, 26) = ![Arrival #4]
rooms_array(row_cnt, 27) = ![Departure #4]
rooms_array(row_cnt, 28) = ![Needs #4]
rooms_array(row_cnt, 29) = ![Request]
rooms_array(row_cnt, 30) = ![Notified]
rooms_array(row_cnt, 31) = ![Room #]

.MoveNext
row_cnt = row_cnt + 1
Loop
End If ' record count
End With

'create table from assembled array

counter = 0
Do Until counter > row_cnt - 1 ' deduct 1 from row_cnt as the above code has added 1.
' This will prevent a blank record added as a last record

If rooms_array(counter, 5) > 1 Then

rooms_counter = 2 ' 1st record already added
Debug.Print rooms_array(counter, 5)
Do Until rooms_counter > rooms_array(counter, 5)

rst_input.AddNew

rst_input("ID") = rooms_array(counter, 0)
rst_input("Account #") = rooms_array(counter, 1)
rst_input("Account Name") = rooms_array(counter, 2)
rst_input("Email Address") = rooms_array(counter, 3)
rst_input("Fax Number") = rooms_array(counter, 4)
rst_input("# Rooms") = rooms_array(counter, 5)
rst_input("1st Choice") = rooms_array(counter, 6)
rst_input("2nd Choice") = rooms_array(counter, 7)
rst_input("3rd Choice") = rooms_array(counter, 8)
rst_input("Room #1 Type") = rooms_array(counter, 9)
rst_input("#1 Smoking") = rooms_array(counter, 10)
rst_input("Arrival #1") = rooms_array(counter, 11)
rst_input("Departure #1") = rooms_array(counter, 12)
rst_input("Needs #1") = rooms_array(counter, 13)
rst_input("Room #2 Type") = rooms_array(counter, 14)
rst_input("Smoking #2") = rooms_array(counter, 15)
rst_input("Arrival #2") = rooms_array(counter, 16)
rst_input("Departure #2") = rooms_array(counter, 17)
rst_input("Needs #2") = rooms_array(counter, 18)
rst_input("Room #3 Type") = rooms_array(counter, 19)
rst_input("Smoking #3") = rooms_array(counter, 20)
rst_input("Arrival #3") = rooms_array(counter, 21)
rst_input("Departure #3") = rooms_array(counter, 22)
rst_input("Needs #3") = rooms_array(counter, 23)
rst_input("Room #4 Type") = rooms_array(counter, 24)
rst_input("Smoking #4") = rooms_array(counter, 25)
rst_input("Arrival #4") = rooms_array(counter, 26)
rst_input("Departure #4") = rooms_array(counter, 27)
rst_input("Needs #4") = rooms_array(counter, 28)
rst_input("Request") = rooms_array(counter, 29)
rst_input("Notified") = rooms_array(counter, 30)
rst_input("Room #") = room_number

rst_input.Update

rooms_counter = rooms_counter + 1
room_number = room_number + 1
Loop
End If ' number of rooms

counter = counter + 1
Loop
End Sub

 

[dhookom]

It seems to me you could do this all with a simple append query with no code.

Duane
Hook'D on Access
MS Access MVP

 

[GarlingBeard]

I cant find a way to use conditional statements and get them to work correctly while using queries. If rooms are > 1 then duplicate columns from row.

 

[dhookom]

I think you can use a table of numbers in your query. I still don't know exactly what your specs might be. We can only see your code and not what you are attempting to accomplish with it.

I would rather start with a clear definition than try to reverse engineer code.

Duane
Hook'D on Access
MS Access MVP

 

[GarlingBeard]

Ok here is what I am trying to do:

I have a web form that collects some info, it contains other data including number of hotel rooms and type of rooms. What I need is to duplicate the rows where number of rooms > 1 by the number of rooms needed. I only need specific data within those rows copied, for example: Room 1 Type - King, Room 2 Type - Queen with 2 rooms. First row will contain Customer info and Room 1 Type - King, second row contains customer info and Room 2 Type - Queen. Each row also will contain duplicate data, but only will have one room type for each row.

 

[dhookom]

I can't force myself to create table and field names with symbols and spaces. I created a table with a few records and less fields ;-) with better names ;-).

You need a table named [tblNums] with a single numeric field [Num] and values 1, 2, 3, 4, ...

Your append query might look something like:

INSERT INTO hotellist ( ID, AccountNum, AccountName, EmailAddress, FaxNumber, NumOfRooms, 1stChoice, 2ndChoice, 3rdChoice )
SELECT ID, AccountNum, AccountName, EmailAddress, FaxNumber, NumOfRooms, [1stChoice], [2ndChoice], [3rdChoice]
FROM HotelList, tblNums
WHERE (((tblNums.Num) Between 2 And [NumOfRooms]));

Duane
Hook'D on Access
MS Access MVP