How to make string = field

[keving99]

Thought i was being clever here but hit a brick wall i cannot get around.

This is a party database for a Playcentre.
The table has a field for every 15 minutes starting at 10am to 6pm
And there a 3 differant areas, so when a party is booked and a start time is entered i want the form to automaticly work out where the party will be at 15 minutes intervals to stop double booking, ie F = Frame, P = PArty room and A = activity Room

10:00 10:15 10:30 10:45.......etc
Party1 F F F P
Party2 A A P

The 15 minute fields for the time slots are T1000 T1015, T1030 etc.

The code below starts at the part start time and creates fields for every 15 mins upto 2 hours (party duration), giving them the same names as the fields in my table.
What i cannot do is relate these created fileds to my table fields and set to either F, P or A.

Hope this makes sence, any help apprechiated, or abetter way to acheive this
TIA

Code..

Private Sub StartDate_AfterUpdate()

Dim strFieldName As String
Dim strFieldName1 As String
Dim strFieldName2 As String
Dim strFieldName3 As String
Dim strFieldName4 As String
Dim strFieldName5 As String
Dim strFieldName6 As String
Dim strFieldName7 As String
Dim strT1 As String
Dim strT2 As String
Dim strT3 As String
Dim strT4 As String
Dim strT5 As String
Dim strT6 As String
Dim strT7 As String


strFieldName = StartTime
strFieldName1 = StartTime + #12:15:00 AM# ' this adds 15 mins
strFieldName2 = StartTime + #12:30:00 AM# ' this adds 30 mins
strFieldName3 = StartTime + #12:45:00 AM#
strFieldName4 = StartTime + #1:00:00 AM#
strFieldName5 = StartTime + #1:15:00 AM#
strFieldName6 = StartTime + #1:30:00 AM#
strFieldName7 = StartTime + #1:45:00 AM#

strT1 = "T" & Left(StartTime, 2) & Mid(StartTime, 4, 2) this = T1000
strT2 = "T" & Left(strFieldName1, 2) & Mid(strFieldName1, 4, 2) this = T1015
strT3 = "T" & Left(strFieldName2, 2) & Mid(strFieldName2, 4, 2) this = T1030
strT4 = "T" & Left(strFieldName3, 2) & Mid(strFieldName3, 4, 2)
strT5 = "T" & Left(strFieldName4, 2) & Mid(strFieldName4, 4, 2)
strT6 = "T" & Left(strFieldName5, 2) & Mid(strFieldName5, 4, 2)
strT7 = "T" & Left(strFieldName6, 2) & Mid(strFieldName6, 4, 2)
strT8 = "T" & Left(strFieldName7, 2) & Mid(strFieldName7, 4, 2)


Select Case PartyType.Value

'Playframe
Case 1
FinishTime = StartTime + #2:00:00 AM#
PartDuration = DLookup("[Duration]", "PartyTheme", "[Themeid] = 1")

strT1 = "F" this is the bit i am stuck on T1000 need to = "F"
strT2 = "F"

 

[KeithG]

I don't understand the issue. I would like to help but am confused. Also I would read up on normalization becuase I see some normalization issues in your table. One more thing look into the dateadd function, you can use it to add the the 15 min intervals

 

[keving99]

I don't understand the issue. I would like to help but am confused. Also I would read up on normalization becuase I see some normalization issues in your table. One more thing look into the dateadd function, you can use it to add the the 15 min intervals

Keith, thanks for reply.

i started with
if startdate = 10:00 then
T1000 = "F"
T1015 = "F"
T1030 = "F"
T1045 = "P"......etc
these would be written back to my table

but it would have been loads of code as i would have had to do above for each possible start time every 15 mins from 10am to 6pm


So i took the starttime and created strings (T1000, T1015) which have the same names as fields in my table, (T10000, T1015 etc)
I nedd these to = a letter, ie F, P or A and be written back to there respective fields T1000, T1015 etc.

if i do [T1000] = "F" i loose the link to field T1000

Hope this explains a little better, probably not!

 

[The_Doc_Man]

Search the forum for BOOKING and SCHEDULING and RESERVATIONS as topics. Many articles have been started on this subject.

Incidentally, you are trying to do it the SPREADSHEET way, which is totally not normalized. DON'T do it this way. It is miserable and MAKES TOO MUCH WORK for you.

Instead, the ACCESS way would be to store all reservations to include these items:

tblReservations
ResID, an autonumber (probably) as prime key (PK)
Room #, which can be a raw number or a foreign key to a Rooms table as you wish.
CustID, which can be a raw name or a foreign key to a Customers table as you wish.
ResStart and ResEnd, two date/time fields for the start and end of the reservation.

Now, to find out whether you can make THIS reservation, build a tentative reservation record in a form but don't store it yet. Have a command button that runs a couple of DCount queries. I'll say this in English, it is up to you to do it in VBA/SQL...

You want to find this situation: The DCount of several conditional queries are ALL zero.

The conditions:

A. The start time of the tentative reservation must not be between the start and end time of any existing reservation for the selected room.

B. The end time of the tentative reservation must not be between the start and end time of any existing reservation for the selected room.

C. The start time of any reservation cannot be between the start time and end time of your tentative reservation for the selected room. (It is not necessary to test the END time of any reservation being between this reservation's start and end times.)

Tests A and B check for partial overlap of the new reservation with any existing one. Test C checks for any existing reservation fully containing the new reservation. I.e. the new reservation is shorter than any other reservation set up for that time.

It is not necessary to test for the new reservation fully containing an earlier reservation because tests A and B already imply that case.

Once the tentative reservation has been checked and no conflicts are found, you can store it. Then it becomes permanent.

Using the "grid" method such as you described, you have to pre-generate the grids and the resulting tables are SO badly denormalized that you will hate HATE HATE yourself every time you have to work the grid. Your queries will be miserable because of having to generate all those field names. You will have 32 for an 8-hour day at 15-minute intervals.

Now: Holidays and Off Days? Pre-reserve all the rooms on holidays. Pre-reserve all the rooms on "non-regular" off days. For "regular" off days, like - say you do not accept reservations on Sundays - have a pre-filter on the NEW reservation that blocks that selection before it even does the DCount scans. DatePart() function can tell you a weekday from a weekend day easily.

Really, READ up on normalization. Don't assume your way is right. (It isn't.) Google search for DATABASE NORMALIZATION. Look at wikipedia (.org). Look in Access Help.

You are describing something called a "repeating group" when you have time-tagged field names. This will eat your socks in dealling with the details.