By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,529 Members | 864 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,529 IT Pros & Developers. It's quick & easy.

Populating a Grid with dates

itchysf
P: 31
Hi Every one,
I have a problem with a DB I'm building, I've got it all finisherd except the front form. What I'm trying to do is populate a grid of 9 rows by 14 colums with either arrival dates or departure dates, these dates are in a qryDailyBookings.
The 9 row are txtboxes names Room1 thu Room9 they are unbound.
The 14 colums are txtboxes named Date1 thru Date14 and have a controlsource of :
Expand|Select|Wrap|Line Numbers
  1. (code***)
  2. =DateAdd("d",0-14,[Startdate]), 
  3. (code/)
  4. [Startdate] being an unbound txtbox with an popup calander for users to select a date from. 
  5. The grid is made up (about half so far) with onbound txtboxes. What I have done so far is put the Iff expression:
  6. (code***)
  7.  (=IIf([Room1]=[RoomNumber] And [Date2]=[ArrivalDate],[ArrivalDate],IIf([Room1]=[RoomNumber] And [Date2]=[DepartureDate],[DepartureDate],Null))) 
  8. (code/)
  9. in the controlsource of these txtboxes, but as you can see this is not very good, as you have to click through all records to get a result.
  10. What I'm asking is there anyway to use VBA to loop through the records of ArrivalDates and DepartureDates to populate the grid all at once when a date is picked with out clicking thru the records one at a time.
  11. I've also got VBA loop:
  12. (code***)
  13.  Private Sub Form_Current()
  14. On Error Resume Next
  15. For Each c In Me.Controls
  16.  If InStr(1, c.Name, "Text") <> 0 Then
  17.  If c.Value = DepartureDate.Value Then
  18.  c.ForeColor = vbRed
  19.  Else
  20.  c.ForeColor = vbBlack
  21.  End If
  22.  End If
  23.  Next
  24. On Error GoTo 0
  25. End Sub
  26. (code/)
which works to change the entered departure date to red so you can tell them apart.
Hope you can understand this and can help.
Thanks itchysf
Aug 31 '08 #1
Share this Question
Share on Google+
59 Replies


puppydogbuddy
Expert 100+
P: 1,923
I am not sure how you capture the different arrival dates, or why you are using the dateAdd instead of the dateDiff function. Irregardless, you could set up a loop similar to the following:

Expand|Select|Wrap|Line Numbers
  1. Dim j as Integer
  2. Dim k as Integer
  3.  
  4. For j = 1 To 9                                 'room array
  5.          RoomNumber = "Room" & (j)
  6. For k = 1 To 14                                     'date array
  7.         ArrivalDate = [Startdate]
  8.         DepartureDate = "Date" & (k)    
  9.        NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
  10.         k = k + 1 
  11. Next k
  12. j = j  + 1
  13. Next j
Sep 1 '08 #2

itchysf
P: 31
Thankyou for your answer puppydogbuddy,
What I'm trying to build is a front office DB for a small motel (9 rooms) my wife and I have taken over and instead of buying a very expensive BD with a lot of functions that are not needed, I am developing our own, If you have ever seen any of these, its the first form that shows in a grid form rooms booked and for how long, by selecting a date from a drop down calander, and this then fills the first row with ongoing dates covering a chosen time period, mine covers two weeks.
From what your answwer suggests is [startdate] is the date selected which is not so, it could be anywhere in the two week period. I'm using Access 2003,(forgot to mention this before).
The ArrivalDate is captured by another form FrmRegistration and held in tblRegistration, the qryDailyBookings is made on this tbl.
Thankyou again and please help.
Sep 1 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
If the StartDate selected from the calendar is not the ArrivalDate, exactly what is the StartDate? If I now assume StartDate is just used to designate the beginning date of the 14 day block for which you are tracking the arrivals and departures, then you appear to be using Date1 thru Date14 for individual days within the block for which you are tracking arrivals and departures…..something like this.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim StartDate as Date                   'beginning date of 14 day block
  3. Dim ArrivalDate as Date               'from registration table
  4. Dim DepartureDate As Date         'from registration table
  5. Dim j as Integer
  6. Dim k as Integer
  7. Dim numDays As Integer
  8.  
  9. For j = 1 To 9                                 'room array
  10.         RoomNumber = "Room" & (j)
  11. For k = 1 To 14                               'date array
  12.         'use date variable to capture dates being tracked
  13.         Me("Date" & (k)) = DateAdd("d",[StartDate] + k, [Startdate])   
  14.  
  15.         'fetch arrivals and departures falling in 14 day block
  16.        Select RoomNumber, ArrivalDate, DepartureDate
  17.        From tblRegistration
  18.        Where nz([ArrivalDate], "") = Me("Date" & (k)).Value & " And "    
  19.     nz([DepartureDate], "") = Me("Date" & (k)).Value
  20.         NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
  21.         k = k + 1 
  22. Next k
  23. j = j  + 1
  24. Next j
Sep 1 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
Ps: you need to change the " And " to " Or " in the above select statement because you want bookings returned if either the arrivak date or the departure date or both fall within the 14 day block.

Also see this link:

Room booking system Relational Data Model
http://www.databasedev.co.uk/hotel_r...ata_model.html
Sep 1 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
Here is the revised code:
Expand|Select|Wrap|Line Numbers
  1. Dim StartDate as Date                   'beginning date of 14 day block
  2. Dim ArrivalDate as Date               'from registration table
  3. Dim DepartureDate As Date         'from registration table
  4. Dim j as Integer
  5. Dim k as Integer
  6. Dim numDays As Integer
  7. Dim strSql As String
  8.  
  9.  
  10. For j = 1 To 9                         'room array;provides accountability for all rooms
  11.        RoomNumber = "Room" & (j)
  12. For k = 1 To 14                               'date array
  13.         'use date variable to capture dates being tracked in 14 day block
  14.         Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])   
  15.  
  16.         'fetch arrivals and departures falling in 14 day block
  17.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate "
  18.        strSql = strSql & "From qryRegistration "
  19.        strSql = strSql & "Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value        
  20.       strSql = strSql &  " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value;
  21.  
  22.        CurrentDb.Execute strSql, dbFailOnError
  23.  
  24.         NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
  25.         k = k + 1 
  26. Next k
  27. j = j  + 1
  28. Next j
Sep 2 '08 #6

itchysf
P: 31
Thanks once agiain for your trouble and time,
When I tried to run your suggested code I get an error message:
Run time error 2448
You can't assign a value to this object,
on line 14.
I changed line 18 to read qryDailyBookings as this is the qry for the form.
The [Startdate], is the txtbox that the dropdown calander is held in, The txtboxes that hold the Date1 to Date14 are named just that and hold a control source
(code)
=DateAdd("d",0,[Startdate])
(code/)
The 0 runs from 0 to 13 for the respective days.
Thanks for the link, I looked at it and it was helpful.
Thanks again for your help
itchysf
Sep 2 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
Thanks once agiain for your trouble and time,
When I tried to run your suggested code I get an error message:
Run time error 2448
You can't assign a value to this object,
on line 14.
I changed line 18 to read qryDailyBookings as this is the qry for the form.
The [Startdate], is the txtbox that the dropdown calander is held in, The txtboxes that hold the Date1 to Date14 are named just that and hold a control source
(code)
=DateAdd("d",0,[Startdate])
(code/)
The 0 runs from 0 to 13 for the respective days.
Thanks for the link, I looked at it and it was helpful.
Thanks again for your help
itchysf
Could you tell me which object on line 14 is highlighted for error 2448? Line14 is attempting to replicate thru code what you accomplished thru the control source, so you don't need both (line 14 and control source). Comment out line 14 and then run the code...what happens?
Sep 2 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
Have a hunch about line 14...[StartDate] is getting the 2448 error because it is a text string.

Expand|Select|Wrap|Line Numbers
  1. Try changing line 14 from this:
  2.  
  3.         Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])   
  4.  
  5. To this:
  6.  
  7.         Me("Date" & (k)) = DateAdd("d",(CDate([StartDate]) + k), CDate([Startdate]))   
Sep 2 '08 #9

P: 77
Hi Poppy,

A question here.. I see that StartDate has been declared as Date and not String.. so that should not be a problem.. Also, it has not been initialized or given any value at all ?

Given that StartDate has a value, I would suggest the following change to line 14

Expand|Select|Wrap|Line Numbers
  1. Me("Date" & (k)) = DateAdd("d", k, Startdate)
I think it should work... Let me know..

Have a hunch about line 14...[StartDate] is getting the 2448 error because it is a text string.

Expand|Select|Wrap|Line Numbers
  1. Try changing line 14 from this:
  2.  
  3.         Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])   
  4.  
  5. To this:
  6.  
  7.         Me("Date" & (k)) = DateAdd("d",(CDate([StartDate]) + k), CDate([Startdate]))   
Sep 2 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
Hi Yaaara,
Thanks for the info. We have to wait for itchysf to tell us whether it works or not.

pDog
Sep 2 '08 #11

itchysf
P: 31
Hi Helpful Friends,
Well the delema continues, the error 2448 highlights the whole line 14. I've tried commenting out the line as suggested and get another error message 3078:
The Microsoft Jet database engine cannot find the table or query "qryDailyBookings" make sure it exits (it does) and that its name is spelled correctly (It is).
I've also tried your other two code fixes and get the same error 2448 message.

Now I have an idea, I might be doing this wrong, I'm using the code under a Private Sub Form_Current, when I get the error messages. (I have commented out the other Form_Current expression that shows DepartureDates in red) I've tried it under different Private Subs namely. Form_Before Update, Form_After UpDate - get no results - but no error message.
Private Sub Date1 (the first date in date row)_Before UpDate, Date1_After UpDate - get no result - again no error message.
Privat Sub Text300 (First textbox in grid) Before Update, After UpDate - same, no result or error message.
I can't put it in StartDate textbox as this already has an expression in it, "startDate_mouseDown" to show the calender, any help with this problem is greatly appreciated and needed.
Thanks guys.
Sep 3 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
Hi Helpful Friends,
Privat Sub Text300 (First textbox in grid) Before Update, After UpDate - same, no result or error message. I can't put it in StartDate textbox as this already has an expression in it, "startDate_mouseDown" to show the calender, any help with this problem is greatly appreciated and needed.
Thanks guys.
My comments are as follows:

1. The expression that should be used to reference functions like the StartDate_mouseDown function in the StartDate textbox should be as follows:
= startDate_mouseDown()

2. The StartDate textbox control is an unbound control, used strictly to identify the start of the 14 day block. and any reference to it in the code should have been Forms!YourForm!StartDate to identify it as coming from an entry on the form, not from an underlying table.

3.The code I provided you should be part of the startDate_mouseDown () function which should be coded as a Public Function in a standard module. It has to be coded as a function procedure in order to reference it from the StartDate textbox control source like you are doing. All references to Me in the public function have to be changed to a fully qualified reference....Forms!yourForm

So the way it should work is that whenever the Calendar control is selected and a new date is displayed in the StartDate textbox, the startDate_mouseDown() function procedure should be executed.
Sep 3 '08 #13

itchysf
P: 31
Hi and thanks, so what I understand your saying is that I shouldn't be using this code for the startdate box.
[code]
Expand|Select|Wrap|Line Numbers
  1. Private Sub startdate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. ' Unhide the calendar and give it the focus
  3.    Calendar4.Visible = True
  4.    Calendar4.SetFocus
  5. ' Match calendar date to existing date if present or today's date
  6.     If Not IsNull(startdate) Then
  7.        Calendar4.Value = Date
  8.     Else
  9.        Calendar4.Value = startdate.Value
  10.     End If
  11.  
  12. End Sub
  13.  
  14. Private Sub Calendar4_Click()
  15. ' Copy chosen date from calendar to originating combo box
  16.    startdate.Value = Calendar4.Value
  17. ' Return the focus to the combo box and hide the calendar
  18.    startdate.SetFocus
  19.    Calendar4.Visible = False
  20. End Sub
  21. [Code/]
Your comments and help is appreciated
Sep 4 '08 #14

puppydogbuddy
Expert 100+
P: 1,923
itchysf,
You are confusing matters by releasing your code and related info piecemeal. The code you released today negates most, if not all of my post from yesterday. Nevertheless, you should have enough info now to put it all together. go back and try to do that, you may have to experiment a little. If you are still having difficulty, then post all of your code as it exists at the time you post. One thing to remember.....you can not reference a procedure as a control source for your textboxes, but you can reference a public function that returns a single value.....so you can not reference startDate_mouseDown as the control source of the startdate textbox as discussed yesterday. It now looks like you can reference just [startDate] if it is a bound field in your table.
Sep 4 '08 #15

itchysf
P: 31
Thanks for your time and trouble Puppydogbuddy sorry if I was confusing, I'll do as you suggest and will let you know how it goes.
Thanks
Sep 5 '08 #16

itchysf
P: 31
Hi Friends,
Well after much hair pulling, (what's left), I still can not get this thing to work,
I have change the code and put it in Calander4-Click as I think the grid should be prpulated when you pick a date and not show the calander.
I will do as puppydogbuddy asks and place my whole code for the form here, any help is appreciated, I have put uncommented comments in it to show what I mean.
[code]
Expand|Select|Wrap|Line Numbers
  1. Private Sub StartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. '(This Works)
  3. ' Unhide the calendar and give it the focus
  4.    Calendar4.Visible = True
  5.    Calendar4.SetFocus
  6. ' Match calendar date to existing date if present or today's date
  7.     If Not IsNull(StartDate) Then
  8.        Calendar4.Value = Date
  9.     Else
  10.        Calendar4.Value = StartDate.Value
  11.     End If
  12.  
  13. End Sub
  14.  
  15. Private Sub Calendar4_Click()
  16.  
  17.      Dim ArrivalDate As Date                    'from qryDailyBookings
  18.      Dim DepartureDate As Date               'from qryDailyBookings
  19.      Dim j As Integer
  20.      Dim k As Integer
  21.      Dim u As Integer
  22.      Dim strSql As String
  23.      Dim Day As Variant
  24.  
  25. 'room array, Unbound textboxes, lefthand side of grid
  26.        For j = 1 To 9
  27.        RoomNumber = "Room" & (j)
  28.        j = j + 1
  29.  
  30. 'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  31.      For k = 1 To 8
  32.        StartDate = "StartDate" & (k)
  33.        k = k + 1
  34.  
  35. 'Textbox array, Unbound textboxes, the grid
  36.      For u = 1 To 37
  37.        Day = "Day" & (u)
  38.        u = u + 1
  39.  
  40. '(This section Works)
  41. ' Copy chosen date from calendar to originating combo box
  42.    StartDate.Value = Calendar4.Value
  43. ' Return the focus to the combo box and hide the calendar
  44.    StartDate.SetFocus
  45.    Calendar4.Visible = False
  46.  
  47. '(This section does not work, you can see it looping but nothing is put in textboxes)
  48.  
  49. 'fetch arrivals and departures falling in 7 day block
  50.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
  51.        strSql = strSql & "From qryDailyBookings"
  52.        strSql = strSql & "Where nz([ArrivalDate], 0) = (StartDate & (k)).Value"
  53.        strSql = strSql & "Or nz([DepartureDate], 0) = (StartDate & (k)).Value"
  54.        strSql = strSql & "And nz([RoomNumber],0) = (Room & (j)).Value"
  55.  
  56. 'Place dates in textboxs
  57.         For Each Day In Form
  58.   If (Room & (j)) = [RoomNumber] And (StartDate & (k)) = [ArrivalDate] Then
  59.          nz(Day & (u)).Value = [ArrivalDate]
  60.   If (Room & (j)) = [RoomNumber] And (StartDate & (k)) = [DepartureDate] Then
  61.         nz(Day & (u)).Value = [DepartureDate]
  62.      Else
  63.         nz(Day & (u)).Value = Null
  64.      End If
  65.      End If
  66.    Next Day
  67.  
  68.   Next 'j error message - Compile error, Invalid Next control variable reference
  69.  
  70.   Next 'k error message - Compile error, Invalid Next control variable reference
  71.  
  72.   Next 'u error message - Compile error, Invalid Next control variable reference
  73.  
  74. End Sub
  75.  
  76. Private Sub Form_Current()
  77. '(This works)
  78. On Error Resume Next
  79. For Each c In Me.Controls
  80. If InStr(1, c.Name, "Day") <> 0 Then
  81. If c.Value = DepartureDate.Value Then
  82.  c.ForeColor = vbRed
  83.  Else
  84.  c.ForeColor = vbBlack
  85.  
  86. End If
  87. End If
  88.  
  89. Next
  90.  
  91. On Error GoTo 0
  92. End Sub
  93. [code/]
Any Idea please
Sep 9 '08 #17

puppydogbuddy
Expert 100+
P: 1,923
Some comments that may help:

1. Calendar4.Value = Date should be changed to>>>>Calendar4.Value = Date()

2. From your first post
The 14 colums are txtboxes named Date1 thru Date14 and have a controlsource of :
=DateAdd("d",0-14,[Startdate]),

Based on above, change the following code:
'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
For k = 1 To 8
StartDate = "StartDate" & (k)
k = k + 1

To this: in conjunction with this change, you need to delete the expression you had in the control source of the textboxes.
Expand|Select|Wrap|Line Numbers
  1. 'date array, Unbound textboxes, top row of grid
  2. For k = 1 To 8
  3.     "Date" & k = DateAdd("d", k, [StartDate])
  4. k = k + 1
3. comment deleted

4. Change the following code:
'fetch arrivals and departures falling in 7 day block
strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
strSql = strSql & "From qryDailyBookings"
strSql = strSql & "Where nz([ArrivalDate], 0) = (StartDate & (k)).Value"
strSql = strSql & "Or nz([DepartureDate], 0) = (StartDate & (k)).Value"
strSql = strSql & "And nz([RoomNumber],0) = (Room & (j)).Value"

to this:
Expand|Select|Wrap|Line Numbers
  1. 'fetch arrivals and departures falling in 7 day block
  2. strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
  3. strSql = strSql & "From qryDailyBookings"
  4. strSql = strSql & "Where nz([ArrivalDate], 0) = ('Date' & (k)).Value"
  5. strSql = strSql & "Or nz([DepartureDate], 0) = ('Date' & (k)).Value"
  6. strSql = strSql & "And nz([RoomNumber],0) = ('Room' & (j)).Value"
  7.  
  8. 'Place data in textboxes
  9. For j = 1 to 9
  10.     For k = 1 to 7
  11.            CurrentDb.Execute strSql, DbFailOnError
  12.     Next k
  13. Next j
5. Eliminate this code (not needed)

'Place dates in textboxs
For Each Day In Form
If (Room & (j)) = [RoomNumber] And (StartDate & (k)) = [ArrivalDate] Then
nz(Day & (u)).Value = [ArrivalDate]
If (Room & (j)) = [RoomNumber] And (StartDate & (k)) = [DepartureDate] Then
nz(Day & (u)).Value = [DepartureDate]
Else
nz(Day & (u)).Value = Null
End If
End If
Next Day

Next 'j error message - Compile error, Invalid Next control variable reference

Next 'k error message - Compile error, Invalid Next control variable reference

Next 'u error message - Compile error, Invalid Next control variable reference
Sep 9 '08 #18

itchysf
P: 31
Thanks again puppydogbuddy
I did as you suggested with these results:
1. Date() I was always putting these () in and everytime I move to new line they
disappear, the code works fine.

2. Your new code line 3, had to put "Me" to stop compile line error message.

4. Major trouble: after deleting and puting your suggested code in, get a run-time error '3075' on whole line 11 with message:
'syntax error (missing operator) on query or expression'.

Line 9 and 10 - For j = 1 to 9 and For k = 1 to 7, had to comment out as they are already in at top of code and was getting error message saying already in use.
this is the code as of now.
[code]

Expand|Select|Wrap|Line Numbers
  1. Private Sub Calendar4_Click()
  2.  
  3.      Dim ArrivalDate As Date                    'from qryDailyBookings
  4.      Dim DepartureDate As Date               'from qryDailyBookings
  5.      Dim j As Integer
  6.      Dim k As Integer
  7.      Dim numDays As Integer
  8.      Dim strSql As String
  9.  
  10. '(This section Works)
  11. ' Copy chosen date from calendar to originating combo box
  12.    StartDate.Value = Calendar4.Value
  13. ' Return the focus to the combo box and hide the calendar
  14.    StartDate.SetFocus
  15.    Calendar4.Visible = False
  16.  
  17.  
  18. 'room array, Unbound textboxes, lefthand side of grid
  19.        For j = 1 To 9
  20.        Me("Room" & (j)) = RoomNumber
  21.       j = j + 1
  22. 'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  23.       For k = 1 To 8
  24.        Me("Date" & (k)) = DateAdd("d", k, [StartDate])
  25.       k = k + 1
  26.  
  27. '(This section does not work)
  28.  
  29. 'fetch arrivals and departures falling in 7 day block
  30.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate "
  31.        strSql = strSql & "From qryDailyBookings "
  32.        strSql = strSql & "Where nz([ArrivalDate], 0) =  & Me('Date' & (k)).Value"
  33.        strSql = strSql & "Or nz([DepartureDate], 0) =  & Me('Date' & (k)).Value"
  34.        strSql = strSql & "And nz([RoomNumber],0) =  & Me('Room' & (j)).Value"
  35.  
  36. '(Error message: Run-Time error "3075" on whole line)
  37.       CurrentDb.Execute strSql, dbFailOnError
  38.  
  39.       numDays = DateDiff("d", [DepartureDate], [ArrivalDate])
  40.  
  41.  
  42. Next k
  43.  
  44. Next j
  45.  
  46. End Sub
[code/]
Thanks once again for your trouble
Sep 10 '08 #19

puppydogbuddy
Expert 100+
P: 1,923
For 3075 error comment #4, try the sql syntax below, and let me know what happens.

Expand|Select|Wrap|Line Numbers
  1. 'fetch arrivals and departures falling in 7 day block
  2. strSQL = "Select RoomNumber, ArrivalDate, DepartureDate"
  3. strSQL = strSQL & " From qryDailyBookings"
  4. strSQL = strSQL & " Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  5. strSQL = strSQL & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  6. strSQL = strSQL & " And nz([RoomNumber],0) = " & Me("Room" & (j)).Value
  7.  
  8. Debug.Print strSQL
'
Sep 10 '08 #20

puppydogbuddy
Expert 100+
P: 1,923
PS: Forgot to tell you to try the code with date delimiters as shown if code in previous post not working:

Expand|Select|Wrap|Line Numbers
  1. 'fetch arrivals and departures falling in 7 day block
  2. strSQL = "Select RoomNumber, ArrivalDate, DepartureDate"
  3. strSQL = strSQL & " From qryDailyBookings"
  4. strSQL = strSQL & " Where nz([ArrivalDate], 0) = " & "#" & Me("Date" & (k)).Value & "#"
  5. strSQL = strSQL & " Or nz([DepartureDate], 0) = " & "#" & Me("Date" & (k)).Value & "#"
  6. strSQL = strSQL & " And nz([RoomNumber],0) = " & Me("Room" & (j)).Value
  7.  
  8. Debug.Print strSQL
Sep 10 '08 #21

itchysf
P: 31
For 3075 error comment #4, try the sql syntax below, and let me know what happens.

Expand|Select|Wrap|Line Numbers
  1. 'fetch arrivals and departures falling in 7 day block
  2. strSQL = "Select RoomNumber, ArrivalDate, DepartureDate"
  3. strSQL = strSQL & " From qryDailyBookings"
  4. strSQL = strSQL & " Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  5. strSQL = strSQL & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  6. strSQL = strSQL & " And nz([RoomNumber],0) = " & Me("Room" & (j)).Value
  7.  
  8. Debug.Print strSQL
'
Hi'
With this every second date Textbox was populated:
Still no dates put in grid textboxes,only dates in top date row.
Chose 5Sep from calendar dates 6Sep filled first date textbox then missed next column date7 then filled next textbox with 8 missed next column date 9 then filled next date textbox with 10 missed next column date 11 then filled next with12.
The first date textbox should be the same as the date picked from calander i.e
If 5Sep2008 picked the first date in date row should be 5Sep2008.
Sep 11 '08 #22

itchysf
P: 31
PS: Forgot to tell you to try the code with date delimiters as shown if code in previous post not working:

Expand|Select|Wrap|Line Numbers
  1. 'fetch arrivals and departures falling in 7 day block
  2. strSQL = "Select RoomNumber, ArrivalDate, DepartureDate"
  3. strSQL = strSQL & " From qryDailyBookings"
  4. strSQL = strSQL & " Where nz([ArrivalDate], 0) = " & "#" & Me("Date" & (k)).Value & "#"
  5. strSQL = strSQL & " Or nz([DepartureDate], 0) = " & "#" & Me("Date" & (k)).Value & "#"
  6. strSQL = strSQL & " And nz([RoomNumber],0) = " & Me("Room" & (j)).Value
  7.  
  8. Debug.Print strSQL
The same happened with delimiters in code
P.S.
I just had another look at this, and I noticed that the date textboxes that were filled are the odd numbered ones i.e Date1, Date3, Date5 and Date 7, don't know if this means anything, seams strange tho.
Sep 11 '08 #23

itchysf
P: 31
The same happened with delimiters in code
P.S.
I just had another look at this, and I noticed that the date textboxes that were filled are the odd numbered ones i.e Date1, Date3, Date5 and Date 7, don't know if this means anything, seams strange tho.
P.P.S. The Room Numbers also change, but only the odd number Rooms, 1,3,5,7,9, all change to 1
Sep 11 '08 #24

puppydogbuddy
Expert 100+
P: 1,923
P.P.S. The Room Numbers also change, but only the odd number Rooms, 1,3,5,7,9, all change to 1
Progress! The array counters are wrong. Don't need the incrementers like j =j + 1, k = k + 1,etc. because the incrementing is done automatically by the "next" at the end of the loop.

Change this:
Expand|Select|Wrap|Line Numbers
  1. 'room array, Unbound textboxes, lefthand side of grid
  2. For j = 1 To 9
  3. Me("Room" & (j)) = RoomNumber
  4. j = j + 1
  5. 'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  6. For k = 1 To 8
  7. Me("Date" & (k)) = DateAdd("d", k, [StartDate])
  8. k = k + 1
To this:
Expand|Select|Wrap|Line Numbers
  1. 'room array, Unbound textboxes, lefthand side of grid
  2. For j = 0 To 8
  3. Me("Room" & (j)) = RoomNumber
  4.  
  5. 'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  6. For k = 0 To 7
  7. Me("Date" & (k)) = DateAdd("d", k, [StartDate])
Sep 11 '08 #25

itchysf
P: 31
Progress! The array counters are wrong. Don't need the incrementers like j =j + 1, k = k + 1,etc. because the incrementing is done automatically by the "next" at the end of the loop.

To this:
Expand|Select|Wrap|Line Numbers
  1. 'room array, Unbound textboxes, lefthand side of grid
  2. For j = 0 To 8
  3. Me("Room" & (j)) = RoomNumber
  4.  
  5. 'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  6. For k = 0 To 7
  7. Me("Date" & (k)) = DateAdd("d", k, [StartDate])
Slow but sure
Changed as suggested but left For k = 1 To 8 as was getting debug error as there is no date0 works fine with this - Thanks

For j=0 To 7 was also getting a bedug error as there is no room 0,so left as
For j= 1 To 9, it was then changing all rooms to 1, so I commented out Me("Room"&(j)) = RoomNumber, as me being a bright spark finally figured out that this was trying to give the rooms a number and this is not neccessary as they are already numbered by default value, the rooms donot run in order 1 thro 9, but by room type.
Now the room column and date row work correctly, only thing left is to get the bookin and bookout dates into the grid at there proper place. If I uncomment the line:
[code]
CurrentDb.Execute strSql, dbFailOnError
[code/]
I still get the run-time error 3075 on the whole line saying:
Syntax error(missing operator) in query expression (starting from nz[ArrivalDate] to the end of the expression). I'm sorry but I can't see it.
P.S. Think I fixed the missing operator problem, I changed the code to this:
[code]
strSql = "Select RoomNumber, ArrivalDate, DepartureDate "
strSql = strSql & "From qryDailyBookings "
strSql = strSql & "Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
strSql = strSql & "Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
strSql = strSql & "And ([RoomNumber], 0) = " & Me("Room" & (j)).Value
[code/]
but now getting a run-time error 3075 saying:
Syntax error(Comma) in query expression (starting from nz[ArrivalDate]).
only the first date goes in the date row.
Sep 12 '08 #26

puppydogbuddy
Expert 100+
P: 1,923
Slow but sure
Now the room column and date row work correctly, only thing left is to get the bookin and bookout dates into the grid at there proper place. If I uncomment the line:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute strSql, dbFailOnError
  2.  
  3. I still get the run-time error 3075 on the whole line saying:
  4. Syntax error(missing operator) in query expression (starting from nz[ArrivalDate] to the end of the expression). I'm sorry but I can't see it.
  5. P.S. Think I fixed the missing operator problem, I changed the code to this:
  6.  
  7.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate "
  8.        strSql = strSql & "From qryDailyBookings "
  9.        strSql = strSql & "Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  10.        strSql = strSql & "Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  11.        strSql = strSql & "And ([RoomNumber], 0) = " & Me("Room" & (j)).Value
  12.  
but now getting a run-time error 3075 saying:
Syntax error(Comma) in query expression (starting from nz[ArrivalDate]).
only the first date goes in the date row.
Try the sql below. I left a space on each line after the begining quote instead of leaving a space before the ending quote because the last 3 lines do not have an ending quote.

To aid in debugging, I put the code that says Debug.Print strSql. If you hit the CNTRL key + G after the code runs, Access will display in the Immediate Window, how it interprets the entire SQL string.
Expand|Select|Wrap|Line Numbers
  1.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
  2.        strSql = strSql & " From qryDailyBookings"
  3.        strSql = strSql & " Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  4.        strSql = strSql & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  5.        strSql = strSql & " And ([RoomNumber], 0) = " & Me("Room" & (j)).Value
  6.  
  7.       CurrentDb.Execute strSql, dbFailOnError
  8.  
  9.       Debug.Print strSql
Sep 12 '08 #27

itchysf
P: 31
Try the sql below. I left a space on each line after the begining quote instead of leaving a space before the ending quote because the last 3 lines do not have an ending quote.

To aid in debugging, I put the code that says Debug.Print strSql. If you hit the CNTRL key + G after the code runs, Access will display in the Immediate Window, how it interprets the entire SQL string.
Expand|Select|Wrap|Line Numbers
  1.        strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
  2.        strSql = strSql & " From qryDailyBookings"
  3.        strSql = strSql & " Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  4.        strSql = strSql & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  5.        strSql = strSql & " And ([RoomNumber], 0) = " & Me("Room" & (j)).Value
  6.  
  7.       CurrentDb.Execute strSql, dbFailOnError
  8.  
  9.       Debug.Print strSql
The code change didn't make any difference still getting run-time error 3075,
I had to move the Debug.Print strSql up to above the line: CurrentDb.Excute as the code is failing on this line and going no further. The Immediate window interprets as:
Select RoomNumber, ArrivalDate, DepartureDate From qryDailyBookings Where nz([ArrivalDate], 0) = 14/09/2008 Or nz([DepartureDate], 0) = 14/09/2008 And ([RoomNumber], 0) = 1

The error message says: Run-Time Error 3075
Syntax error(Comma) in query expression (the rest is the same as the immediate window).

Just a question, I'm not real bright on VBA coding, but I can'nt see how this code selects the grid textbox under the date required and inline with the room required to insert the Arrival or Departure date into it, please can you explain how this done.
Thanks again for your time and trouble.
Sep 13 '08 #28

puppydogbuddy
Expert 100+
P: 1,923
Ok, it should work now. I believe the 3075 error was due to the fact that I forgot to include the expression for numDays in the sql statement.

In answer to your question, the actual arrival and departure dates are manually entered into the booking table from a booking form (I'm assuming you have one), otherwise you would not be able to generate qryDailyBookings that you use as source for computing the number of days to be billed.

Further, if you want the actual dates of arrival and departure, and the numDays computation to be diplayed on your grid form(below) , I suggest you create a subform with three rows of text boxes(arrival , departure, numDays) with the same number of columns as you have on the date grid. The record source of your subform will be qryBookings.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Calendar4_Click()
  2.  
  3. Dim ArrivalDate As Date 'from qryDailyBookings
  4. Dim DepartureDate As Date 'from qryDailyBookings
  5. Dim j As Integer
  6. Dim k As Integer
  7. Dim numDays As Integer
  8. Dim strSql As String
  9.  
  10. '(This section Works)
  11. ' Copy chosen date from calendar to originating combo box
  12. StartDate.Value = Calendar4.Value
  13. ' Return the focus to the combo box and hide the calendar
  14. StartDate.SetFocus
  15. Calendar4.Visible = False
  16.  
  17. 'fill textbox  grid-----------------------------------------------------------------
  18.  
  19. 'room array, Unbound textboxes, lefthand side of grid
  20. For j = 1 To 9
  21. Me("Room" & (j)) = RoomNumber
  22.  
  23. 'date array, Unbound textboxes, top row of grid (Here you set up the grid for 7 days,  'which is different than the 14 days  you had in your first post. 
  24.  
  25. For k = 1 To 7
  26. Me("Date" & (k)) = DateAdd("d", k, [StartDate])
  27.  
  28. '----------------------------------------------------------------------------------------
  29. 'fetch  arrival, departure info to compute numDays for billing purposes
  30.  
  31. strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
  32. strSql = strSql & " DateDiff("d", [DepartureDate], [ArrivalDate]) As numDays"
  33. strSql = strSql & " From qryDailyBookings"
  34. strSql = strSql & " Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
  35. strSql = strSql & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value
  36. strSql = strSql & " And ([RoomNumber], 0) = " & Me("Room" & (j)).Value
  37.  
  38. Debug.Print strSql
  39.  
  40. CurrentDb.Execute strSql, dbFailOnError
  41.  
  42. Next k
  43.  
  44. Next j
  45.  
  46. End Sub
Sep 13 '08 #29

itchysf
P: 31
Hi,
Thanks for your time again, More troubles.
Firstly I decreased the date row from 14 to 7 only till I get the code working, the final grid will be 14days, it can be easily extended to any number of days.
With the new code I still get the 3075 error message on line 40, saying the same as before syntax error (comma).
I had to change the "d" to 'd' as it was creating a compile error "expected end of statement".

In reply to answer to my question, the arrival and departure dates are entered in the registration form (frmRegistration) and held in tblRegistration, this was used for qryDailyBookings. what I was asking is how they are transfered to the grid with this code into the proper grid boxs, say for argument:
04/07/08, 05/07/08, 06/07/08, 07/07/08
room (arrival departure) this is for yor info only not on form
1 05/07/08 06/07/08
6 arrival departure
5 06/07/08 07/07/08
7
hope you can understand this
Sep 15 '08 #30

puppydogbuddy
Expert 100+
P: 1,923
You are correct that the code so far does not provide for updating the actual arrival and departure dates to the grid……the sql statement needs to be reworked. Developing the code would be a lot easier if I could see the grid, or at least get the names of the textbox controls on the grid. The two lines of code below merely set up the variable calendar and room# "headers" for the grid.
Me("Room" & (j)) = RoomNumber
Me("Date" & (k)) = DateAdd("d", k, [StartDate])


I believe I can resolve everything if you could confirm or revise the layout below and provide the names of the textbox controls on your grid and the field name if the control is bound to a table field.

For example:
1. Date1 thru Date14; unbound (for grid header display purposes only)
2. aDate1 thru aDate14; unbound (for grid display purposes only)
3. dDate1 thru dDate14; unbound (for grid display purposes only)
4. Room1 thru Room9; (for grid display purposes only)

#1 above is pretty straight forward. #'s 2,3, and 4 are in question, not because unbound, but because we have used Date! Thru Date14 for arrivals and departures on the grid; we need to differentiate between control names for arrivals and departures. Also need explanation as to how Room # is assigned….you mentioned RoomType?

…………..………..Date1….Date2……Date3……………….Date14
Room1
…………Arrival…aDate1… aDate2…. aDate3……………… aDate14
…………Depart….dDate1…dDate2…. dDate3……………….dDate14
Room2
…………Arrival
…………Depart
Sep 15 '08 #31

itchysf
P: 31
You are correct that the code so far does not provide for updating the actual arrival and departure dates to the grid……the sql statement needs to be reworked. Developing the code would be a lot easier if I could see the grid, or at least get the names of the textbox controls on the grid. The two lines of code below merely set up the variable calendar and room# "headers" for the grid.
Me("Room" & (j)) = RoomNumber
Me("Date" & (k)) = DateAdd("d", k, [StartDate])


I believe I can resolve everything if you could confirm or revise the layout below and provide the names of the textbox controls on your grid and the field name if the control is bound to a table field.

For example:
1. Date1 thru Date14; unbound (for grid header display purposes only)
2. aDate1 thru aDate14; unbound (for grid display purposes only)
3. dDate1 thru dDate14; unbound (for grid display purposes only)
4. Room1 thru Room9; (for grid display purposes only)

#1 above is pretty straight forward. #'s 2,3, and 4 are in question, not because unbound, but because we have used Date! Thru Date14 for arrivals and departures on the grid; we need to differentiate between control names for arrivals and departures. Also need explanation as to how Room # is assigned….you mentioned RoomType?

…………..………..Date1….Date2……Date3……………….Date14
Room1
…………Arrival…aDate1… aDate2…. aDate3……………… aDate14
…………Depart….dDate1…dDate2…. dDate3……………….dDate14
Room2
…………Arrival
…………Depart

Is there any way I can send you the DB it's only 2.5mb approx and you can see FrmDailyBookings grid layout, I'm not asking you to do the work, as I'm trying to learn this myself, just so you can see what I'm trying to explain.
Thanks
itchysf
Sep 17 '08 #32

puppydogbuddy
Expert 100+
P: 1,923
Is there any way I can send you the DB it's only 2.5mb approx and you can see FrmDailyBookings grid layout, I'm not asking you to do the work, as I'm trying to learn this myself, just so you can see what I'm trying to explain.
Thanks
itchysf
itchysf,
If you have MS Access version 2000 and can attach your mdb as a "sanitized" zip file, that would make things so much easier. Sanitized, in this context, means that you have removed any "sensitive" data.

I don't mind working on your db at all because I think there are some nuances to the application that I want to experiment with. If you don't have Access 2000, it would still be helpful if you could just attach snapshots of your grid in design view and form view

Instructions for attaching a file to the post from site Help:
To attach a file to a new post, simply click the [Browse] button at the bottom of the post composition page, and locate the file that you want to attach from your local hard drive.

Only certain types of files may be attached: these are the valid file extensions for files to be attached to this forum: bmp doc gif jpe jpeg jpg pdf png psd txt zip.

After posting, the attachment will show up in the body of your message. To view the contents of the attachment (if it is not already displayed) simply click the filename link that appears next to the attachment icon .
Sep 17 '08 #33

itchysf
P: 31
The Db is done on Access 2003 in Access 2000 format.


I must say your time and trouble with this problem has been outstanding. thankyou
Sep 18 '08 #34

puppydogbuddy
Expert 100+
P: 1,923
The Db is done on Access 2003 in Access 2000 format.


I must say your time and trouble with this problem has been outstanding. thankyou
itchsf,
Your booking grid is now working in the dynamic fashion you wanted. It proved to be quite a challenge because of all the unbounded controls. In order to make it run smoothly without having to write a ton of code, I had to figure out an algorithm to simulate a control array that would map the data by room, arrival date, and departure date to the correct unbounded controls. things for you to note include:

1. For purposes of implementing the control array, your "Day" controls were renamed on the basis of this algorithm: "Day" + RoomNumber + value representing the day# <1 thru 7> . For example, the name of the control that would hold arrival or departure info for Room 3 on the second day of the 7 day horizontal grid is named "Day32". This enabled me to use the following expression in the control array:>>>>> strCtl = ("Day" & (j) & (k))

2. In for the grid to work correctly, you need to add the missing textboxes on the lower part of your grid, so that each room has 7 days on the grid. You may have to move your calendar control to a different location on the screen.

3. Note that code for the room array was not needed because the room numbers were placed in the default value property for each room related control.

4. How do you propose to display arrival and departure for a given room in the same day ? I think you might be able use a smaller font in conjunction with a line feed to display it on two line within one control Like this:
Date 9/22/2008
arr 2:00 PM
dpt 8:00 PM

Let me know how it works for you.
Attached Files
File Type: zip Front OfficeRevised.zip (185.4 KB, 139 views)
Sep 21 '08 #35

itchysf
P: 31
itchsf,
Your booking grid is now working in the dynamic fashion you wanted. It proved to be quite a challenge because of all the unbounded controls. In order to make it run smoothly without having to write a ton of code, I had to figure out an algorithm to simulate a control array that would map the data by room, arrival date, and departure date to the correct unbounded controls. things for you to note include:

1. For purposes of implementing the control array, your "Day" controls were renamed on the basis of this algorithm: "Day" + RoomNumber + value representing the day# <1 thru 7> . For example, the name of the control that would hold arrival or departure info for Room 3 on the second day of the 7 day horizontal grid is named "Day32". This enabled me to use the following expression in the control array:>>>>> strCtl = ("Day" & (j) & (k))

2. In for the grid to work correctly, you need to add the missing textboxes on the lower part of your grid, so that each room has 7 days on the grid. You may have to move your calendar control to a different location on the screen.

3. Note that code for the room array was not needed because the room numbers were placed in the default value property for each room related control.

4. How do you propose to display arrival and departure for a given room in the same day ? I think you might be able use a smaller font in conjunction with a line feed to display it on two line within one control Like this:
Date 9/22/2008
arr 2:00 PM
dpt 8:00 PM

Let me know how it works for you.
Puppydogbuddy'

To use old Aussie slang - "your a bloody genius", thanks mate.
As for arr and dpt dates I will see about changing the code I have got that makes DepartureDate appear in red and use small font as you suggest.

The one thing I have noticed is that at the moment (I have not yet completed the grid with the textbox layout) is all dates appear along side the same room.
i.e. room 3 arr18/8 dpt 21/8, room 2 arr19/8 dpt 20/8, room 4 arr20/8 dpt 21/8,
all appear along side room 2. This probably will not happen when I complete the textboxs.

The calendar lives under the startdate textbox I only moved it to it present postion for ease of use with the day textboxs.

Once again thanks mate and I will let you know how it works out.
Sep 22 '08 #36

itchysf
P: 31
Puppydogbuddy'

To use old Aussie slang - "your a bloody genius", thanks mate.
As for arr and dpt dates I will see about changing the code I have got that makes DepartureDate appear in red and use small font as you suggest.

The one thing I have noticed is that at the moment (I have not yet completed the grid with the textbox layout) is all dates appear along side the same room.
i.e. room 3 arr18/8 dpt 21/8, room 2 arr19/8 dpt 20/8, room 4 arr20/8 dpt 21/8,
all appear along side room 2. This probably will not happen when I complete the textboxs.

The calendar lives under the startdate textbox I only moved it to it present postion for ease of use with the day textboxs.

Once again thanks mate and I will let you know how it works out.
More troubles, I have now finally completed the grib with names as per your instructions and as i said in my last email all the dates appear alongside the same room(see above), the code seams to pick the dates OK but not the rooms.
Also, minor but annoying, the dates in the whole DB have changed to the American format, MM/DD/YYYY, is there any way to change them back to DD/MM/YYYY, I have tried the Date/Time Input Mask but this does not change it when you click off the date it goes to the American format.
Thanks
Oct 1 '08 #37

puppydogbuddy
Expert 100+
P: 1,923
More troubles, I have now finally completed the grib with names as per your instructions and as i said in my last email all the dates appear alongside the same room(see above), the code seams to pick the dates OK but not the rooms.
Also, minor but annoying, the dates in the whole DB have changed to the American format, MM/DD/YYYY, is there any way to change them back to DD/MM/YYYY, I have tried the Date/Time Input Mask but this does not change it when you click off the date it goes to the American format.
Thanks
itchysf,
You need to attach the latest copy of your program in Access 2000 format. My copy of the program is not having the problems with the room # that you describe above. Did you remember to comment out the code behind the frmDailyBookings that was related to the room grid and let the default value of each room related textbox became the active room #? Also, did you name your controls correctly in accordance with the algorithm I discussed with you?

Regarding the date format, the copy you sent me was in American Format. I did not do anything to change the date format. Irregardless, all you need to do is go to each table that you have a date/time field, go to the field's Format property and change the "Short Date" format you currently have by typing in dd/mm/yyyy in its place. However, if the date/time textbox controls on the form also have their Format property set to Short Date, you will also have to change the format of the textboxes involved because the format property will control how the date is displayed on the form.
Oct 1 '08 #38

itchysf
P: 31
itchysf,
You need to attach the latest copy of your program in Access 2000 format. My copy of the program is not having the problems with the room # that you describe above. Did you remember to comment out the code behind the frmDailyBookings that was related to the room grid and let the default value of each room related textbox became the active room #? Also, did you name your controls correctly in accordance with the algorithm I discussed with you?

Regarding the date format, the copy you sent me was in American Format. I did not do anything to change the date format. Irregardless, all you need to do is go to each table that you have a date/time field, go to the field's Format property and change the "Short Date" format you currently have by typing in dd/mm/yyyy in its place. However, if the date/time textbox controls on the form also have their Format property set to Short Date, you will also have to change the format of the textboxes involved because the format property will control how the date is displayed on the form.
Hi again puppydogbuddy,
Well it looks like I made a mistake,( a habit of mine ), I thought all the dates were going into room two textbox, that's wrong, what is happening is the loop is stopping when it makes the first match, I deleted all my phony customers and entered sume new ones, and if you change their room numbers the loop always stops at the first room it matches, it enters them in the right room textbox, well done. Now all I have to get right is for the loop to continue for the rest of the rooms and dates, any ideas.

I was in no way impling that you had change anything with the DB, I was asking how to change the date format from American to English, as this is the format we use here, I had already tried to change it to "ShortDate" in the fields format, The "Short Date" Field is in American format, I entered an "Edit" Short Date in my format but this does not change anything, when you click out of the Textbox it reverts to American format. Sorry if I did not explain it to clearly before.
Have a nice weekend, hoping to hear from you soon.
Oct 3 '08 #39

puppydogbuddy
Expert 100+
P: 1,923
Hi again puppydogbuddy,
if you change their room numbers the loop always stops at the first room it matches, it enters them in the right room textbox, well done. Now all I have to get right is for the loop to continue for the rest of the rooms and dates, any ideas.

I entered an "Edit" Short Date in my format but this does not change anything, when you click out of the Textbox it reverts to American format.
Have a nice weekend, hoping to hear from you soon.
Hi itchysf,
Did you verify that the sql output shows more than one room for the 7 day block you are looking at? Can you post the output of the sql that shows more than one room with an arrival or departure date? or better yet, can you attach a copy of the mdb like you did before so I can look at it.

The following code is looping fine on my end when than one room shows up in the output of the sql. I have no idea why it is not working on your end.
Expand|Select|Wrap|Line Numbers
  1. 'fetch arrival and departure data falling in 7 day block
  2. strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
  3. strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
  4. strSql = strSql & " From qryDailyBookings"
  5. strSql = strSql & " WHERE (((RoomNumber) Between '1' And '9')"
  6. strSql = strSql & " AND ((ArrivalDate) Between [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
  7. strSql = strSql & " OR (((RoomNumber) Between '1' And '9') AND ((DepartureDate) Between"
  8. strSql = strSql & " [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
  9. strSql = strSql & " Order By RoomNumber,ArrivalDate, DepartureDate;"
  10.                      ' Debug.Print strSql
  11.  
  12. 'set the above sql string as this form's recordSource
  13. Me.RecordSource = strSql
  14.  
  15.  
  16. 'dynamically assign the arrivals and departures to the appropriate control on the grid based the data returned by the record source.
  17.  
  18. For j = 1 To 9
  19.     For k = 1 To 8
  20.         If [RoomNumber] = Me("Room" & (j)) Then
  21.             If [ArrivalDate] = Me("Date" & (k)) Then
  22.                 strCtl = ("Day" & (j) & (k))
  23.                 Me.Controls(strCtl).Value = "Arr " & Format(TimeValue(ArrivalDate), "hh:mm AMPM")
  24.  
  25.             End If
  26.             If [DepartureDate] = Me("Date" & (k)) Then
  27.                 strCtl = ("Day" & (j) & (k))
  28.                 Me.Controls(strCtl).Value = "Dpt " & Format(TimeValue(DepartureDate), "hh:mm AMPM")
  29.             End If
  30.         End If
  31.     Next k
  32. Next j
In regards to the dat format, did you change the format property from short date to dd/mm/yyyy format on both the table and the form? Are your regional settings correct (the regional settings are accessible from the windows control panel.......Start> Windows Control Panel>Date/Time.
Oct 3 '08 #40

itchysf
P: 31
Hi Puppydogbuddy,
I've attached the mdb for you to look at I have been using the one you sent so the codes are the same. You will notice that I have deleted all the guests and inserted only 2 recent ones both are for the date arr. 2/10/2008 and departing 3/10/2008, one in room 1 and the other in room 9. when I open the daiybooking form and select 1/10/2008 only room 1 shows up. It doesn't matter if you change the room numbers, only the first room in the list will show up on the grid.

Thanks, I have fixed the date issue, except for the calendar, you will notice it, it is still in American format, cann't figure how to change that, if I'm not wrong it cann't be changed.
Attached Files
File Type: zip Front Office.zip (179.6 KB, 133 views)
Oct 6 '08 #41

puppydogbuddy
Expert 100+
P: 1,923
Hi Itchysf,

Figured out the problem. The processing loop for the room array (sequential order) incorrectly maps to the room number controls on the form grid (in order by room type). The reason it appeared to be working in my test version is because the rooms for the arrival dates in my test just happened to be rooms in which there is no difference between the array seqence and control grid sequence. Am busy today, but will tweak the code for you tomorrow.
Oct 6 '08 #42

itchysf
P: 31
Hi Itchysf,

Figured out the problem. The processing loop for the room array (sequential order) incorrectly maps to the room number controls on the form grid (in order by room type). The reason it appeared to be working in my test version is because the rooms for the arrival dates in my test just happened to be rooms in which there is no difference between the array seqence and control grid sequence. Am busy today, but will tweak the code for you tomorrow.

Great, Thanks, must be the time of year, we're flat out as well.
Oct 7 '08 #43

puppydogbuddy
Expert 100+
P: 1,923
Itchysf,
Just to give you an update. I was busy and did not get a chance to tweak the code until yesterday. I fixed the code so that it processes the room numbers in the same sequence as they appear on the control grid...and guess what? ....of the two arrivals and two departures in my test, only the arrival and departure assigned to Room 7 is showing up, while the one assigned to Room 9 does not show up. The weird thing is that when I turn on the Debug.Print to display what was processed by the program, it shows Room 9 was picked up, but does not show any arrival or departures for the room.

So, I have a little mystery on my hands, and have started an intensive debugging procedures where I trace the execution process from beginning to end. I will get to the bottom of it, but it may take a few days because I can't spend full time on it. Talk to you later.
Oct 10 '08 #44

itchysf
P: 31
Itchysf,
Just to give you an update. I was busy and did not get a chance to tweak the code until yesterday. I fixed the code so that it processes the room numbers in the same sequence as they appear on the control grid...and guess what? ....of the two arrivals and two departures in my test, only the arrival and departure assigned to Room 7 is showing up, while the one assigned to Room 9 does not show up. The weird thing is that when I turn on the Debug.Print to display what was processed by the program, it shows Room 9 was picked up, but does not show any arrival or departures for the room.

So, I have a little mystery on my hands, and have started an intensive debugging procedures where I trace the execution process from beginning to end. I will get to the bottom of it, but it may take a few days because I can't spend full time on it. Talk to you later.

Thats OK, I am just pleased that you have spent so much time on this for me, If you were any closer I'd buy you a beer. Thanks.
Oct 10 '08 #45

puppydogbuddy
Expert 100+
P: 1,923
I think I figured out the problem….apparently when you changed from mm/dd/yyyy to dd/mm/yyyy date format, you should have deleted all dates entered under the prior format and re-entered them under the new format. Otherwise , Access will interpret them as having been entered under most recent format setting.

As an example, if the current setting is dd/mm/yyyy, a date entered as mm/dd/yyyy …>>>>>nov 10, 2008…>>>11/10/2008 will be interpreted as 11/10/2008 under a dd/mm/yyyy format.>>>>as Oct 11, 2008, not Nov 10, 2008.

With the above in mind, I am posting the latest revised code behind your form grid. Before using this code, you need to erase all of the test data you entered to your booking system under the original format. Then re-enter the booking data and check to see if any arrivals/departures are missing from your grid. Let me know what happens.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Calendar4_Click()
  2.  
  3.     'declare loop counters to simulate arrays
  4.      Dim j As Integer, Room(9) As String
  5.      Dim k As Integer   'for date arrays
  6.      Dim numDays As Integer
  7.      Dim strSql As String
  8.      Dim strCtl As String
  9.  
  10.  
  11.  
  12. ' Copy chosen date from calendar to originating combo box
  13.    StartDate.Value = Calendar4.Value
  14. ' Return the focus to the combo box and hide the calendar
  15.    StartDate.SetFocus
  16.    Calendar4.Visible = False
  17.  
  18.  
  19.  
  20. '----------------------------------------------------------------------------------------------------
  21.  
  22.  
  23.  
  24. 'set up calendar date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  25.      For k = 1 To 8
  26.        Me("Date" & (k)) = DateAdd("d", k, [StartDate])
  27.      Next k
  28.  
  29.  
  30. 'fetch arrival and departure data falling in 8 day block on the calendar.
  31. strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
  32. strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
  33. strSql = strSql & " From qryDailyBookings"
  34. strSql = strSql & " WHERE (((RoomNumber) Between '1' And '9')"
  35. strSql = strSql & " AND ((ArrivalDate) Between [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
  36. strSql = strSql & " OR (((RoomNumber) Between '1' And '9') AND ((DepartureDate) Between"
  37. strSql = strSql & " [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
  38. strSql = strSql & " Order By RoomNumber, ArrivalDate, DepartureDate;"
  39.                       'Debug.Print strSql
  40.  
  41. 'set the above sql string as this form's recordSource
  42. Me.RecordSource = strSql
  43.  
  44.  
  45. 'dynamically assign the arrival dates and departure dates to the appropriate control on the grid based the data returned by the record source.
  46. '-----------------------------------------------------------------------------------------------
  47.  
  48. 'room(j) was declared as a true array, and is not a simulated array like was used for the arrival/depart dates
  49. 'this was done because room numbers were not in sequence on the grid, and had to be mapped to the applicable control as shown below.
  50.  
  51. 'initialize value for room numbers in j array
  52. Room(0) = "1"
  53. Room(1) = "6"
  54. Room(2) = "2"
  55. Room(3) = "3"
  56. Room(4) = "9"
  57. Room(5) = "8"
  58. Room(6) = "4"
  59. Room(7) = "5"
  60. Room(8) = "7"
  61.  
  62.  
  63.  
  64. For j = 0 To 8
  65.    For k = 1 To 8
  66.        If Room(j) = RoomNumber.Value Then
  67.             ' Create value array for unbound txtboxes used for room numbers because the rooms are not in sequential order.
  68.                                                    'array counter starts with 0
  69.             If Me("Date" & (k)) = [ArrivalDate] Or Me("Date" & (k)) = [DepartureDate] Then
  70.                 If Me("Date" & (k)) = [ArrivalDate] Then
  71.                     strCtl = ("Day" & Room(j) & (k))
  72.                     Me.Controls(strCtl).Value = "Arr " & Format(TimeValue(ArrivalDate), "hh:mm AMPM")
  73.                 ElseIf Me("Date" & (k)) = [DepartureDate] Then
  74.                     strCtl = ("Day" & Room(j) & (k))
  75.                     Me.Controls(strCtl).Value = "Dpt " & Format(TimeValue(DepartureDate), "hh:mm AMPM")
  76.                 End If
  77.             Else
  78.                 'there were no arrivals or departures for this calendar date k
  79.             End If
  80.         End If
  81.                   Debug.Print "Room(j) " & Room(j) & " k " & Me("Date" & (k)) & "  " & " strCtl " & strCtl
  82.    Next k
  83. Next j
  84.  
  85. End Sub
Oct 13 '08 #46

itchysf
P: 31
Hi'
Done what you said and entered two new entries in room 1 and 3 both for one night stay 13 - 14/10/08. Run code and only room 1 was entered into grid.
This is whot was printed in the immediate window:

Room(j) 1 k 10/13/2008 strCtl Day11
Room(j) 1 k 10/14/2008 strCtl Day12
Room(j) 1 k 10/15/2008 strCtl Day12
Room(j) 1 k 10/16/2008 strCtl Day12
Room(j) 1 k 10/17/2008 strCtl Day12
Room(j) 1 k 10/18/2008 strCtl Day12
Room(j) 1 k 10/19/2008 strCtl Day12
Room(j) 1 k 10/20/2008 strCtl Day12
Room(j) 6 k 10/13/2008 strCtl Day12
Room(j) 6 k 10/14/2008 strCtl Day12
Room(j) 6 k 10/15/2008 strCtl Day12
Room(j) 6 k 10/16/2008 strCtl Day12
Room(j) 6 k 10/17/2008 strCtl Day12
Room(j) 6 k 10/18/2008 strCtl Day12
Room(j) 6 k 10/19/2008 strCtl Day12
Room(j) 6 k 10/20/2008 strCtl Day12
Room(j) 2 k 10/13/2008 strCtl Day12
Room(j) 2 k 10/14/2008 strCtl Day12
Room(j) 2 k 10/15/2008 strCtl Day12
Room(j) 2 k 10/16/2008 strCtl Day12
Room(j) 2 k 10/17/2008 strCtl Day12
Room(j) 2 k 10/18/2008 strCtl Day12
Room(j) 2 k 10/19/2008 strCtl Day12
Room(j) 2 k 10/20/2008 strCtl Day12
Room(j) 3 k 10/13/2008 strCtl Day12
Room(j) 3 k 10/14/2008 strCtl Day12
Room(j) 3 k 10/15/2008 strCtl Day12
Room(j) 3 k 10/16/2008 strCtl Day12
Room(j) 3 k 10/17/2008 strCtl Day12
Room(j) 3 k 10/18/2008 strCtl Day12
Room(j) 3 k 10/19/2008 strCtl Day12
Room(j) 3 k 10/20/2008 strCtl Day12
Room(j) 9 k 10/13/2008 strCtl Day12
Room(j) 9 k 10/14/2008 strCtl Day12
Room(j) 9 k 10/15/2008 strCtl Day12
Room(j) 9 k 10/16/2008 strCtl Day12
Room(j) 9 k 10/17/2008 strCtl Day12
Room(j) 9 k 10/18/2008 strCtl Day12
Room(j) 9 k 10/19/2008 strCtl Day12
Room(j) 9 k 10/20/2008 strCtl Day12
Room(j) 8 k 10/13/2008 strCtl Day12
Room(j) 8 k 10/14/2008 strCtl Day12
Room(j) 8 k 10/15/2008 strCtl Day12
Room(j) 8 k 10/16/2008 strCtl Day12
Room(j) 8 k 10/17/2008 strCtl Day12
Room(j) 8 k 10/18/2008 strCtl Day12
Room(j) 8 k 10/19/2008 strCtl Day12
Room(j) 8 k 10/20/2008 strCtl Day12
Room(j) 4 k 10/13/2008 strCtl Day12
Room(j) 4 k 10/14/2008 strCtl Day12
Room(j) 4 k 10/15/2008 strCtl Day12
Room(j) 4 k 10/16/2008 strCtl Day12
Room(j) 4 k 10/17/2008 strCtl Day12
Room(j) 4 k 10/18/2008 strCtl Day12
Room(j) 4 k 10/19/2008 strCtl Day12
Room(j) 4 k 10/20/2008 strCtl Day12
Room(j) 5 k 10/13/2008 strCtl Day12
Room(j) 5 k 10/14/2008 strCtl Day12
Room(j) 5 k 10/15/2008 strCtl Day12
Room(j) 5 k 10/16/2008 strCtl Day12
Room(j) 5 k 10/17/2008 strCtl Day12
Room(j) 5 k 10/18/2008 strCtl Day12
Room(j) 5 k 10/19/2008 strCtl Day12
Room(j) 5 k 10/20/2008 strCtl Day12
Room(j) 7 k 10/13/2008 strCtl Day12
Room(j) 7 k 10/14/2008 strCtl Day12
Room(j) 7 k 10/15/2008 strCtl Day12
Room(j) 7 k 10/16/2008 strCtl Day12
Room(j) 7 k 10/17/2008 strCtl Day12
Room(j) 7 k 10/18/2008 strCtl Day12
Room(j) 7 k 10/19/2008 strCtl Day12
Room(j) 7 k 10/20/2008 strCtl Day12

Hope this helps
itchysf
Oct 13 '08 #47

puppydogbuddy
Expert 100+
P: 1,923
Hi Itchysf,

I am sending you this revision so that you can see what has been accomplished, and at the same time, provide another pair of eyes to test the application out. I won't do anything further until I hear from you after you have thoroughly tested the application with new data....just in case I still have some bad data in the system related to the change in the date format. Let me know how the testing comes out.

Despite intensive recoding from top to bottom, and intensive debugging, I have not been able to figure out why the second arrival and departure (for room 7 as per qryDailyBookings) is appearing on the grid as a vacancy instead of appearing as an actual arrival and departure. If you look at all of the underlying values of the grid variables, they appear to be correct. When you do a Cntrl + G, you will see that everything is accounted for, including the arrival and departure dates related to room 7, but room 7 shows up as a vacancy on the grid and in the backup. On this basis, I am sure it is something to do with the date format or possibly with the way the date was entered.

attached, please find your revised booking application. It has been revised significantly from the prior version.

1. The program code now factors in the non-consecutive sequence of the Rooms.
2. Vacancies are placed on the grid....and the grid is now dynamically color coded (blue for arrival,yellow for departure, no change in color for vacancy days).
Attached Files
File Type: zip Front OfficeR3.zip (202.1 KB, 85 views)
Oct 19 '08 #48

itchysf
P: 31
Hi Puppydogbuddy,
I have played around with your great effect and deleted all records and placed three new arrivals and departures in the DB, as you can see one in room 6, one in room 4 and one in room 7 all arriving on the 20/10/2008 and departing on different days.

The one thing I did with the whole DB was change the date format back to ShortDate, but this made no difference to the outcome so I changed them back to dd/mm/yyyy.

For some reason your code is placing the first arrival in room 1, this is the reason I changed the room number of the arrivals, so room 1 was not used. When I checked the immediate window only one arrival and departure is shown and as if they are in room 1. When I changed the room numbers on the grid so the first room was 5, it placed the arrival and departure in that room, so it looks like the code is entering the arrival and departure in the first line no matter what the number.

The other thing I noticed was although it looks good having the vacant rooms marked as vacant, if a guest stays longer than one night the days inbetween arriving and departing are marked as vacant, any ideas how to fix this, maybe having the blue carry on till departure date.

Thanks once again, this must be giving you some headaches.
Attached Files
File Type: zip Front OfficeR3.zip (204.4 KB, 76 views)
Oct 20 '08 #49

puppydogbuddy
Expert 100+
P: 1,923
Hi itchysf,
No heartaches, just a challenge. I think the attached is pretty close if not the final. I accomplished it using a minimal amount of code. I did some light testing, but you need to test thoroughly in your environment. Let me know.

pDog
Attached Files
File Type: zip Front OfficeR4X.zip (188.5 KB, 104 views)
Oct 26 '08 #50

59 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.