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 : - (code***)
-
=DateAdd("d",0-14,[Startdate]),
-
(code/)
-
[Startdate] being an unbound txtbox with an popup calander for users to select a date from.
-
The grid is made up (about half so far) with onbound txtboxes. What I have done so far is put the Iff expression:
-
(code***)
-
(=IIf([Room1]=[RoomNumber] And [Date2]=[ArrivalDate],[ArrivalDate],IIf([Room1]=[RoomNumber] And [Date2]=[DepartureDate],[DepartureDate],Null)))
-
(code/)
-
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.
-
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.
-
I've also got VBA loop:
-
(code***)
-
Private Sub Form_Current()
-
On Error Resume Next
-
For Each c In Me.Controls
-
If InStr(1, c.Name, "Text") <> 0 Then
-
If c.Value = DepartureDate.Value Then
-
c.ForeColor = vbRed
-
Else
-
c.ForeColor = vbBlack
-
End If
-
End If
-
Next
-
On Error GoTo 0
-
End Sub
-
(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
59 6289
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: - Dim j as Integer
-
Dim k as Integer
-
-
For j = 1 To 9 'room array
-
RoomNumber = "Room" & (j)
-
For k = 1 To 14 'date array
-
ArrivalDate = [Startdate]
-
DepartureDate = "Date" & (k)
-
NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
-
k = k + 1
-
Next k
-
j = j + 1
-
Next j
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.
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. -
-
Dim StartDate as Date 'beginning date of 14 day block
-
Dim ArrivalDate as Date 'from registration table
-
Dim DepartureDate As Date 'from registration table
-
Dim j as Integer
-
Dim k as Integer
-
Dim numDays As Integer
-
-
For j = 1 To 9 'room array
-
RoomNumber = "Room" & (j)
-
For k = 1 To 14 'date array
-
'use date variable to capture dates being tracked
-
Me("Date" & (k)) = DateAdd("d",[StartDate] + k, [Startdate])
-
-
'fetch arrivals and departures falling in 14 day block
-
Select RoomNumber, ArrivalDate, DepartureDate
-
From tblRegistration
-
Where nz([ArrivalDate], "") = Me("Date" & (k)).Value & " And "
-
nz([DepartureDate], "") = Me("Date" & (k)).Value
-
NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
-
k = k + 1
-
Next k
-
j = j + 1
-
Next j
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
Here is the revised code: -
Dim StartDate as Date 'beginning date of 14 day block
-
Dim ArrivalDate as Date 'from registration table
-
Dim DepartureDate As Date 'from registration table
-
Dim j as Integer
-
Dim k as Integer
-
Dim numDays As Integer
-
Dim strSql As String
-
-
-
For j = 1 To 9 'room array;provides accountability for all rooms
-
RoomNumber = "Room" & (j)
-
For k = 1 To 14 'date array
-
'use date variable to capture dates being tracked in 14 day block
-
Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])
-
-
'fetch arrivals and departures falling in 14 day block
-
strSql = "Select RoomNumber, ArrivalDate, DepartureDate "
-
strSql = strSql & "From qryRegistration "
-
strSql = strSql & "Where nz([ArrivalDate], 0) = " & Me("Date" & (k)).Value
-
strSql = strSql & " Or nz([DepartureDate], 0) = " & Me("Date" & (k)).Value;
-
-
CurrentDb.Execute strSql, dbFailOnError
-
-
NumDays =DateDiff("d",[DepartureDate],[ArrivalDate])
-
k = k + 1
-
Next k
-
j = j + 1
-
Next j
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
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?
Have a hunch about line 14...[StartDate] is getting the 2448 error because it is a text string. - Try changing line 14 from this:
-
-
Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])
-
-
To this:
-
-
Me("Date" & (k)) = DateAdd("d",(CDate([StartDate]) + k), CDate([Startdate]))
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 - 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. - Try changing line 14 from this:
-
-
Me("Date" & (k)) = DateAdd("d",([StartDate] + k), [Startdate])
-
-
To this:
-
-
Me("Date" & (k)) = DateAdd("d",(CDate([StartDate]) + k), CDate([Startdate]))
Hi Yaaara,
Thanks for the info. We have to wait for itchysf to tell us whether it works or not.
pDog
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.
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.
Hi and thanks, so what I understand your saying is that I shouldn't be using this code for the startdate box.
[code] -
Private Sub startdate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
' Unhide the calendar and give it the focus
-
Calendar4.Visible = True
-
Calendar4.SetFocus
-
' Match calendar date to existing date if present or today's date
-
If Not IsNull(startdate) Then
-
Calendar4.Value = Date
-
Else
-
Calendar4.Value = startdate.Value
-
End If
-
-
End Sub
-
-
Private Sub Calendar4_Click()
-
' Copy chosen date from calendar to originating combo box
-
startdate.Value = Calendar4.Value
-
' Return the focus to the combo box and hide the calendar
-
startdate.SetFocus
-
Calendar4.Visible = False
-
End Sub
-
[Code/]
Your comments and help is appreciated
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.
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
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] -
Private Sub StartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
'(This Works)
-
' Unhide the calendar and give it the focus
-
Calendar4.Visible = True
-
Calendar4.SetFocus
-
' Match calendar date to existing date if present or today's date
-
If Not IsNull(StartDate) Then
-
Calendar4.Value = Date
-
Else
-
Calendar4.Value = StartDate.Value
-
End If
-
-
End Sub
-
-
Private Sub Calendar4_Click()
-
-
Dim ArrivalDate As Date 'from qryDailyBookings
-
Dim DepartureDate As Date 'from qryDailyBookings
-
Dim j As Integer
-
Dim k As Integer
-
Dim u As Integer
-
Dim strSql As String
-
Dim Day As Variant
-
-
'room array, Unbound textboxes, lefthand side of grid
-
For j = 1 To 9
-
RoomNumber = "Room" & (j)
-
j = j + 1
-
-
'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
-
-
'Textbox array, Unbound textboxes, the grid
-
For u = 1 To 37
-
Day = "Day" & (u)
-
u = u + 1
-
-
'(This section Works)
-
' Copy chosen date from calendar to originating combo box
-
StartDate.Value = Calendar4.Value
-
' Return the focus to the combo box and hide the calendar
-
StartDate.SetFocus
-
Calendar4.Visible = False
-
-
'(This section does not work, you can see it looping but nothing is put in textboxes)
-
-
'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"
-
-
'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
-
-
End Sub
-
-
Private Sub Form_Current()
-
'(This works)
-
On Error Resume Next
-
For Each c In Me.Controls
-
If InStr(1, c.Name, "Day") <> 0 Then
-
If c.Value = DepartureDate.Value Then
-
c.ForeColor = vbRed
-
Else
-
c.ForeColor = vbBlack
-
-
End If
-
End If
-
-
Next
-
-
On Error GoTo 0
-
End Sub
-
[code/]
Any Idea please
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. -
'date array, Unbound textboxes, top row of grid
-
For k = 1 To 8
-
"Date" & k = DateAdd("d", k, [StartDate])
-
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: - 'fetch arrivals and departures falling in 7 day block
-
strSql = "Select RoomNumber, ArrivalDate, DepartureDate"
-
strSql = strSql & "From qryDailyBookings"
-
strSql = strSql & "Where nz([ArrivalDate], 0) = ('Date' & (k)).Value"
-
strSql = strSql & "Or nz([DepartureDate], 0) = ('Date' & (k)).Value"
-
strSql = strSql & "And nz([RoomNumber],0) = ('Room' & (j)).Value"
-
-
'Place data in textboxes
-
For j = 1 to 9
-
For k = 1 to 7
-
CurrentDb.Execute strSql, DbFailOnError
-
Next k
-
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
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] - Private Sub Calendar4_Click()
-
-
Dim ArrivalDate As Date 'from qryDailyBookings
-
Dim DepartureDate As Date 'from qryDailyBookings
-
Dim j As Integer
-
Dim k As Integer
-
Dim numDays As Integer
-
Dim strSql As String
-
-
'(This section Works)
-
' Copy chosen date from calendar to originating combo box
-
StartDate.Value = Calendar4.Value
-
' Return the focus to the combo box and hide the calendar
-
StartDate.SetFocus
-
Calendar4.Visible = False
-
-
-
'room array, Unbound textboxes, lefthand side of grid
-
For j = 1 To 9
-
Me("Room" & (j)) = RoomNumber
-
j = j + 1
-
'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
-
For k = 1 To 8
-
Me("Date" & (k)) = DateAdd("d", k, [StartDate])
-
k = k + 1
-
-
'(This section does not work)
-
-
'fetch arrivals and departures falling in 7 day block
-
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 nz([RoomNumber],0) = & Me('Room' & (j)).Value"
-
-
'(Error message: Run-Time error "3075" on whole line)
-
CurrentDb.Execute strSql, dbFailOnError
-
-
numDays = DateDiff("d", [DepartureDate], [ArrivalDate])
-
-
-
Next k
-
-
Next j
-
-
End Sub
[code/]
Thanks once again for your trouble
For 3075 error comment #4, try the sql syntax below, and let me know what happens. -
'fetch arrivals and departures falling in 7 day block
-
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 nz([RoomNumber],0) = " & Me("Room" & (j)).Value
-
-
Debug.Print strSQL
'
PS: Forgot to tell you to try the code with date delimiters as shown if code in previous post not working: -
'fetch arrivals and departures falling in 7 day block
-
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 nz([RoomNumber],0) = " & Me("Room" & (j)).Value
-
-
Debug.Print strSQL
For 3075 error comment #4, try the sql syntax below, and let me know what happens. -
'fetch arrivals and departures falling in 7 day block
-
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 nz([RoomNumber],0) = " & Me("Room" & (j)).Value
-
-
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.
PS: Forgot to tell you to try the code with date delimiters as shown if code in previous post not working: -
'fetch arrivals and departures falling in 7 day block
-
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 nz([RoomNumber],0) = " & Me("Room" & (j)).Value
-
-
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.
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
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: -
'room array, Unbound textboxes, lefthand side of grid
-
For j = 1 To 9
-
Me("Room" & (j)) = RoomNumber
-
j = j + 1
-
'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
-
For k = 1 To 8
-
Me("Date" & (k)) = DateAdd("d", k, [StartDate])
-
k = k + 1
To this: -
'room array, Unbound textboxes, lefthand side of grid
-
For j = 0 To 8
-
Me("Room" & (j)) = RoomNumber
-
-
'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
-
For k = 0 To 7
-
Me("Date" & (k)) = DateAdd("d", k, [StartDate])
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: -
'room array, Unbound textboxes, lefthand side of grid
-
For j = 0 To 8
-
Me("Room" & (j)) = RoomNumber
-
-
'date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
-
For k = 0 To 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.
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: -
CurrentDb.Execute strSql, dbFailOnError
-
-
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:
-
-
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
-
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. -
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
-
-
CurrentDb.Execute strSql, dbFailOnError
-
-
Debug.Print strSql
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. -
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
-
-
CurrentDb.Execute strSql, dbFailOnError
-
-
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.
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. -
Private Sub Calendar4_Click()
-
-
Dim ArrivalDate As Date 'from qryDailyBookings
-
Dim DepartureDate As Date 'from qryDailyBookings
-
Dim j As Integer
-
Dim k As Integer
-
Dim numDays As Integer
-
Dim strSql As String
-
-
'(This section Works)
-
' Copy chosen date from calendar to originating combo box
-
StartDate.Value = Calendar4.Value
-
' Return the focus to the combo box and hide the calendar
-
StartDate.SetFocus
-
Calendar4.Visible = False
-
-
'fill textbox grid-----------------------------------------------------------------
-
-
'room array, Unbound textboxes, lefthand side of grid
-
For j = 1 To 9
-
Me("Room" & (j)) = RoomNumber
-
-
'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.
-
-
For k = 1 To 7
-
Me("Date" & (k)) = DateAdd("d", k, [StartDate])
-
-
'----------------------------------------------------------------------------------------
-
'fetch arrival, departure info to compute numDays for billing purposes
-
-
strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
-
strSql = strSql & " DateDiff("d", [DepartureDate], [ArrivalDate]) As numDays"
-
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
-
-
Debug.Print strSql
-
-
CurrentDb.Execute strSql, dbFailOnError
-
-
Next k
-
-
Next j
-
-
End Sub
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
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
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
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 .
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
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.
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.
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
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.
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.
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. -
'fetch arrival and departure data falling in 7 day block
-
strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
-
strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
-
strSql = strSql & " From qryDailyBookings"
-
strSql = strSql & " WHERE (((RoomNumber) Between '1' And '9')"
-
strSql = strSql & " AND ((ArrivalDate) Between [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
-
strSql = strSql & " OR (((RoomNumber) Between '1' And '9') AND ((DepartureDate) Between"
-
strSql = strSql & " [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
-
strSql = strSql & " Order By RoomNumber,ArrivalDate, DepartureDate;"
-
' Debug.Print strSql
-
-
'set the above sql string as this form's recordSource
-
Me.RecordSource = strSql
-
-
-
'dynamically assign the arrivals and departures to the appropriate control on the grid based the data returned by the record source.
-
-
For j = 1 To 9
-
For k = 1 To 8
-
If [RoomNumber] = Me("Room" & (j)) Then
-
If [ArrivalDate] = Me("Date" & (k)) Then
-
strCtl = ("Day" & (j) & (k))
-
Me.Controls(strCtl).Value = "Arr " & Format(TimeValue(ArrivalDate), "hh:mm AMPM")
-
-
End If
-
If [DepartureDate] = Me("Date" & (k)) Then
-
strCtl = ("Day" & (j) & (k))
-
Me.Controls(strCtl).Value = "Dpt " & Format(TimeValue(DepartureDate), "hh:mm AMPM")
-
End If
-
End If
-
Next k
-
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.
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.
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.
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.
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.
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.
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. -
Private Sub Calendar4_Click()
-
-
'declare loop counters to simulate arrays
-
Dim j As Integer, Room(9) As String
-
Dim k As Integer 'for date arrays
-
Dim numDays As Integer
-
Dim strSql As String
-
Dim strCtl As String
-
-
-
-
' Copy chosen date from calendar to originating combo box
-
StartDate.Value = Calendar4.Value
-
' Return the focus to the combo box and hide the calendar
-
StartDate.SetFocus
-
Calendar4.Visible = False
-
-
-
-
'----------------------------------------------------------------------------------------------------
-
-
-
-
'set up calendar date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
-
For k = 1 To 8
-
Me("Date" & (k)) = DateAdd("d", k, [StartDate])
-
Next k
-
-
-
'fetch arrival and departure data falling in 8 day block on the calendar.
-
strSql = "Select RoomNumber, ArrivalDate, DepartureDate,"
-
strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
-
strSql = strSql & " From qryDailyBookings"
-
strSql = strSql & " WHERE (((RoomNumber) Between '1' And '9')"
-
strSql = strSql & " AND ((ArrivalDate) Between [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
-
strSql = strSql & " OR (((RoomNumber) Between '1' And '9') AND ((DepartureDate) Between"
-
strSql = strSql & " [Forms]![frmDailyBookings]![Date1] And [Forms]![frmDailyBookings]![Date8]))"
-
strSql = strSql & " Order By RoomNumber, ArrivalDate, DepartureDate;"
-
'Debug.Print strSql
-
-
'set the above sql string as this form's recordSource
-
Me.RecordSource = strSql
-
-
-
'dynamically assign the arrival dates and departure dates to the appropriate control on the grid based the data returned by the record source.
-
'-----------------------------------------------------------------------------------------------
-
-
'room(j) was declared as a true array, and is not a simulated array like was used for the arrival/depart dates
-
'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.
-
-
'initialize value for room numbers in j array
-
Room(0) = "1"
-
Room(1) = "6"
-
Room(2) = "2"
-
Room(3) = "3"
-
Room(4) = "9"
-
Room(5) = "8"
-
Room(6) = "4"
-
Room(7) = "5"
-
Room(8) = "7"
-
-
-
-
For j = 0 To 8
-
For k = 1 To 8
-
If Room(j) = RoomNumber.Value Then
-
' Create value array for unbound txtboxes used for room numbers because the rooms are not in sequential order.
-
'array counter starts with 0
-
If Me("Date" & (k)) = [ArrivalDate] Or Me("Date" & (k)) = [DepartureDate] Then
-
If Me("Date" & (k)) = [ArrivalDate] Then
-
strCtl = ("Day" & Room(j) & (k))
-
Me.Controls(strCtl).Value = "Arr " & Format(TimeValue(ArrivalDate), "hh:mm AMPM")
-
ElseIf Me("Date" & (k)) = [DepartureDate] Then
-
strCtl = ("Day" & Room(j) & (k))
-
Me.Controls(strCtl).Value = "Dpt " & Format(TimeValue(DepartureDate), "hh:mm AMPM")
-
End If
-
Else
-
'there were no arrivals or departures for this calendar date k
-
End If
-
End If
-
Debug.Print "Room(j) " & Room(j) & " k " & Me("Date" & (k)) & " " & " strCtl " & strCtl
-
Next k
-
Next j
-
-
End Sub
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
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).
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Peter Bailey |
last post by:
I have a vba string taht dynamically creates the query which has two dates
in it that it grabs off an open form as a string from the textbox.
What I generate in vba is:
SELECT DOSMBK.Date,...
|
by: Serious_Practitioner |
last post by:
Good day, and thank you in advance for any assistance you can provide.
I have a table in an Access 2000 .mdb file, and I've run into something odd
and insolvable, at least for me. The database is...
|
by: P K |
last post by:
I have a listbox which I am populating on the client (it contains a list of
dates selected from calender). The listbox is a server control.
When I get to the server after postback by selecting an...
|
by: hlam |
last post by:
Help - Calculating the total of a column in a data grid --
when data grid is part of Master-Detail set-up
I have setup a Master-Detail form using Visual
Studio.Net. A ListBox is the (Master)...
|
by: Unforgiven |
last post by:
Hello All,
I have the following query...
SELECT Demographics.Full_Name,
Demographics.Year_of_birth,
Status.Status_OK,
SUM(2004 - Demographics.Year_of_birth) AS Age
FROM Demographics
INNER...
|
by: Dave Hutchings |
last post by:
Hi,
My problem is this, I have a search screen which creates a query that
performs a search on a large database. The results of this search could, if
the user requires, return many many rows...
|
by: el_83 |
last post by:
Hi I was wondering if someone could help me. I have not used
activewidgets before and i wanted to know if it is possible to use it
to populate a grid that exsists on another's webpage. That is,...
|
by: Eric B. |
last post by:
I am populating a DataGrid with many rows.
As it is populating I see the scrollbar flying but no rows pop up until it
is finished.
Is there a way to make each row draw as soon as it is...
|
by: Mike P |
last post by:
I need to create a grid which along the y axis has a user name and along
the x axis has a date. Somehow I need to populate the x axis with 6
months worth of dates from the current date, and then...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |