Going thru fields to create new table 1

[Blondie96]

I need to use a subform so a user can request the use of a room and connect it back to another table that contains other information about the request.

Unfortunately the user can enter multiple dates & time periods (non contiguous) so the form would look like

DateStart TimeStart Date End Time End
3/1/2004 0500 3/7/2004 1000
(user has room from 0500 to 1000 from 3/1 to 3/7

3/10/2004 1800 3/11/2004 0300
(user has room from 1800 on 3/10 to 0300 on 03/11)

and there would be 5-10 iterations of these fields.
I need help to look at each iteration to determine if there is a value entered in the dateStart field. Then I will have to look at the start & end times & parse them to create a new record which will just contain each single hour used:
0500, 0600, 0700, 0800, 0900, 100 (first example) this part I think I can do once I get how to reference the start & enddate fields.

Any help will be appreciated.

Thanks

 

[PaulF]

I think this is along the lines of what you want to do.
I created 2 tables , one for maintaing the data hour by hour and the other for maintaining the requested info. Fields used are UserId (numeric), ClassRoom (Text), StartDate (ShortDate), EndDate (ShortDate), StartHour (ShortDate), EndHour (ShortDate), DateInUse (ShortDate), HourInUse (ShortDate). Then I created a form to enter up to 3 different requests, and a command button that would first check to see if the classroom was already booked for that date and hour requested, then to add the necessary hourly records to the table tblClassroomReservations and then add the request data to table tblClassroomReservationsRequests. Here is the code behind the form that uses the following controls
txtUserID to capture one UserID
txtClassroom1, txtClassroom2, txtClassroom3 for classroom data
txtStartDate1, txtStartDate2, txtStartDate3 for starting date
txtEndDate1, txtEndDate2, txtDate3 for end date
cboStartHour1, cboStartHour2, cboStartHour3 for starting time
cboEndHour1, cboEndHour2, cboEndHour3 for ending time
cmdRequest - command button to start the process

Private Sub cmdRequest_Click()
On Error GoTo errHandler
Dim x As Integer
Dim dtEndDay As Date, dtEndTime As Date
Dim dtHourVar As Date, dtDayVar As Date
Dim db As DAO.Database, rstData As DAO.Recordset, rstReq As DAO.Recordset
Dim strFind As String
Set db = CurrentDb
Set rstData = db.OpenRecordset("tblClassroomReservations", dbOpenDynaset)
Set rstReq = db.OpenRecordset("tblClassroomReservationsRequests")
If Len(Nz(txtUserID, "")) = 0 Then
MsgBox "User ID Required", vbOKOnly, "Missing Information"
Exit Sub
End If
For x = 1 To 3
If Len(Nz(Me("txtClassroom" & CStr(x)), "")) > 0 Then
If Len(Nz(Me("txtStartDate" & CStr(x)), "")) <> 0 And Len(Nz(Me("txtEndDate" & CStr(x)), "")) > 0 And _
Len(Nz(Me("cboStartTime" & CStr(x)), "")) <> 0 And Len(Nz(Me("cboEndTime" & CStr(x)), "")) <> 0 Then
dtDayVar = Me("txtStartDate" & CStr(x))
dtEndDate = Me("txtEndDate" & CStr(x))
dtHourVar = Me("cboStartTime" & CStr(x))
dtEndTime = Me("cboEndTime" & CStr(x))
Do While dtDayVar < DateAdd("d", 1, dtEndDate)
Do While dtHourVar < dtEndTime
strFind = "Classroom = '" & Me("txtClassroom" & CStr(x)) & "' And DateInUse = #" _
& dtDayVar & "# And HourInUse = #" & dtHourVar & "#"
Debug.Print strFind
With rstData
.FindFirst strFind
If Not .NoMatch Then
Me("txtClassroom" & CStr(x)).BackColor = vbRed
Me.Repaint
MsgBox "Classroom Is already reserved for Timeframe Requested"
Exit Sub
End If
End With
dtHourVar = DateAdd("h", 1, dtHourVar)
Loop
dtDayVar = DateAdd("d", 1, dtDayVar)
dtHourVar = Me("cboStartTime" & CStr(x))
Loop
End If
End If
Next x
x = 1
For x = 1 To 3
If Len(Nz(Me("txtClassroom" & CStr(x)), "")) > 0 Then
If Len(Nz(Me("txtStartDate" & CStr(x)), "")) <> 0 And Len(Nz(Me("txtEndDate" & CStr(x)), "")) > 0 And _
Len(Nz(Me("cboStartTime" & CStr(x)), "")) <> 0 And Len(Nz(Me("cboEndTime" & CStr(x)), "")) <> 0 Then
dtDayVar = Me("txtStartDate" & CStr(x))
dtEndDate = Me("txtEndDate" & CStr(x))
dtHourVar = Me("cboStartTime" & CStr(x))
dtEndTime = Me("cboEndTime" & CStr(x))
Do While dtDayVar < DateAdd("d", 1, dtEndDate)
Do While dtHourVar < dtEndTime
With rstData
.AddNew
!UserId = txtUserID
!ClassRoom = Me("txtclassroom" & CStr(x))
!DateInUse = dtDayVar
!HourInUse = dtHourVar
.UPDATE
End With
dtHourVar = DateAdd("h", 1, dtHourVar)
Loop
dtDayVar = DateAdd("d", 1, dtDayVar)
dtHourVar = Me("cboStartTime" & CStr(x))
Loop
With rstReq
.AddNew
!UserId = Me.txtUserID
!ClassRoom = Me("txtClassroom" & CStr(x))
!StartDate = CDate(Me("txtStartDate" & CStr(x)))
!EndDate = CDate(Me("txtEndDate" & CStr(x)))
!StartHour = CDate(Me("cboStartTime" & CStr(x)))
!EndHour = CDate(Me("cboEndTime" & CStr(x)))
.UPDATE
End With
Else
MsgBox "Start and End Dates and Hours are Required", vbOKOnly, "Missing Information"
Exit Sub
End If
Else
MsgBox "Classroom Number Required", vbCritical, "Missing Data"
Exit Sub
End If
Next x
MsgBox "Completed Add Process"
errExit:
Set db = Nothing
Set rstData = Nothing
Set rstReq = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error Entering Classroom Reservations"
Resume errExit
End Sub

HTH
PaulF

 

[Blondie96]

PaulF,

Thank you very much for the reply. I am going to print & study it. I will let you know how well I did implementing it.

Thanks again,

Tamra

 

[Blondie96]

PaulF,

I have been studying the code. I a question on how to broaden it for my current needs. I have 2 other tables,
1. Room type:
Type Id
Type Description
2. Room Name
Rm ID
Rm Type (links to type ID of Room Type table)
Rm Name

How could I add/modify the code so that it will query available rooms giving only the room names in a particular Rm Type, so I can look at the reservations in only those rooms? Would this be creating another recordset with those criteria? And if so, how?

Thanks again,

Tamr

 

[Blondie96]

PaulF,

For more clarification,

When a user makes a request, he asks for a "type" of room, and can only request one room type per request. I assign the particular room based on availability.

Tamra

 

[PaulF]

Probably using a crosstab query, where you'd query the table with the hourly records and set the criteria to between the start date/end date and between the start hour/end hour.
Check into that and see if you can accomplish what you need, it may require adding one more field to your table of hourly records.

PaulF

 

[Blondie96]

I am confused, with your last post. I was trying to get just the room names of a particular type, so I could look at all the rooms of that type & check their schedules.

 

[PaulF]

I was thinking that you wanted a query that would take the table with the hourly data and put it together based on the date and hours to check for availability. It's getting late, the ACCESS brain is shutting down, perhaps someone else can jump in here for a while.. if not then maybe we can try again tomorrow.

Sorry
PaulF

 

[Blondie96]

I understand,thanks for the help. I'll check back tomorrow.

 

[Blondie96]

A little more on what I would like to do.
I have:
table listing the types of rooms.
Table of room names with room type being foreign key to type of room table
a single table with all the room schedules in it.
a form where a user requests use of a "type of room"

I want to look thru the room schedule based on type of room, find the first room with the requested dates/times open. Then schedule that requested date/time in that room.

I have created a make table query which will give me the names of the pertinent rooms (based on room types), so I can open that as a recordset to go thru it. (if I can determine how to incorporate it into the code you already provided.)

I will probably need to sort the schedule Table on room type & room name before starting this logic.


differences between what you gave & what I have:
typeID is a number and only occurs one time on the form.
cboStartTime & cboEndTime would be integer < 2400 (military time)

The logic I was working on earlier was like this (I haven't converted it into VBA yet)

Dim HRCounter as Int (to go thru hours)
Dim RoomCounter as Int (to go to each room)
Dim Conflict as Yes/No (to determine when there is a
conflict on a room for a particular hour

For RoomCounter = first to last
conflict = no
For each record in sched table
For HrCounter = starttime to endtime
If HRCounter = 24 then
HrCounter = 0
DtCounter = DtCounter +1
end if
If DtCounter >= SchedTable.StartDate AND
DtCounter <= SchedTable.EndDate
IF HrCounter >= SchedTable.StartTime AND
HRCounter <= SchedTable.EndTime
Write Conflict report Record
Conflict = YES
End IF
End If
IF HrCounter = EndTime AND DtCounter = EndDate AND
Conflict = NO Then
write schedule record to temporary table using:
RoomID, startdate, enddate, startTime,
endtime
roomCount = last
end if
next hrcounter
next dtcounter
If roomcounter = last then
print conflict report (prints each hour/each room a
schedule conflict occured)
end if
next SchedTable record
next roomcounter

 

[PaulF]

Tamra
Sorry to take this long to reply again.. I have a small sample database that might give you some ideas to work with (or not)... if you want to see it let me know where to send it to. Posting all of the data in the forum would probably be a lot more confusing than seeing it all put together.

PaulF

 

[Blondie96]

Thanks PaulF,

Attach @cox.net to my handle & send it there. Appreciate it.