Populating a Grid with dates

[itchysf]

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

[itchysf]

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

THANKS, you are a great mate, after testing for two days, it works great, cann't find anything wrong, again thanks a lot.

[itchysf]

Looks like I spoke too soon, what is happening is I have put another guest in arriving 3/11/2008 and departing on the 5/11/2008. When you select a date (19/10) so that the guest who booked in on the 20/10/2008 are shown on the grid, it works fine in all aspects. Then when you reselect a date (2/11) so the guest who arrives on the 3/11 is to be shown, all works well but the guest for the 20/10 do not get deleted from the grid and the rooms for those guests are shown as occupied, is there someway that the grid can be cleared of guests each time a new date is selected from the calander so only the guests for the chosen week are shown.
Thanks itchysf.

[puppydogbuddy]

Looks like I spoke too soon, what is happening is I have put another guest in arriving 3/11/2008 and departing on the 5/11/2008. When you select a date (19/10) so that the guest who booked in on the 20/10/2008 are shown on the grid, it works fine in all aspects. Then when you reselect a date (2/11) so the guest who arrives on the 3/11 is to be shown, all works well but the guest for the 20/10 do not get deleted from the grid and the rooms for those guests are shown as occupied, is there someway that the grid can be cleared of guests each time a new date is selected from the calander so only the guests for the chosen week are shown.
Thanks itchysf.

Hi itchsf,

That is a constraint that you should put in the criteria row of the query (qryDailyBookings) that is the source for the grid.

[puppydogbuddy]

Itchysf,
If you don't know how to do that, hear is the sql of qryDailyBookings, modified to compute the number of days between arrival and departure.

SELECT tblReservation.RoomNumber, tblReservation.ArrivalDate, tblReservation.DepartureDate, tblReservation.CustomerID, DateDiff("d",DateValue([ArrivalDate]),DateValue([DepartureDate])) AS NumDays
FROM tblReservation
WHERE (((tblReservation.ArrivalDate)>=DateDiff("d",Date()-Month(Date()),Date())) AND ((tblReservation.DepartureDate)>=DateDiff("d",Date()-Month(Date()),Date())))
ORDER BY tblReservation.RoomNumber;

You already have a constraint on the arrival and departure dates to display only arrivals in the current month. I added a column to calculate the number of days between Arrival and Departure that would be used for billing purposes.

Maybe you can add an additional constraint in the criteria row that the NumDays must be : nz([NumDays]) <= 8 because the grid basically displays only 8 days of activity from the starting date.

If you need further help, let me know.

[itchysf]

Hi itchsf,

That is a constraint that you should put in the criteria row of the query (qryDailyBookings) that is the source for the grid.

Dumb me, yes I do know how to do that, I had a lot on yesterday and not thinking, I was trying to work it out through your code, which I must admit I don't understand much off, (more schooling needed), also thanks for the extra coding.

I am going to expand the grid to cover 2 weeks so the nz([NumDays])<=8 will be changed to <=15.
Thanks again puppydogbuddy.

[puppydogbuddy]

You are welcome. Glad it works to your satisfaction. I don't normally spend this much pro bono time on one project, but learned a lot by doing it....so it was a win win situation.

[itchysf]

Hi All,
I am still having problems with the Grid population on my Db, I have extended the number of days covered to 14 and have the days being filled in for the whole form. The problem is that all rooms are not showing up for the whole 14 days, If 17/11/2008 is picked from the calendar all booked rooms 1,6,3,9,8,4,& 5 show as required, but not rm 7, pick 18/11 and only rm 9 & 5 show, 19/11 only rm 3,9,4,& 5 show, 20/11 only rm 1,3,9,4,& 5 show, 21/11 only rm 1,6,3,9,8,4, & 5 show. Room 2 has a guest booked in on the 1/12 this does not show up on the grid until you pick 29/11. Room 7 has a guest booked in on the 29/11 and this does not show up until you pick the 29/11. Any ideas why this is happening.

Another problem i am having is entering a new guests details. On entering thier details in the Reservation form a going to the next form(button Tariff) and picking there Customer ID number from the drop down list you get a message "Field cannot be updated", on opening the help file it says" you tried to change the value of a control whose locked value is set to yes" this not so, all locked propeerties are set to "No". I am the only one using the DB. This happens three times and then the form can be used, the text boxes it happens on are CustomerID, Name and Reservation ID.
Again any ideas on why this is happening, any help will be greatly appreciated.
Itchysf

[puppydogbuddy]

Hi All,
I am still having problems with the Grid population on my Db, I have extended the number of days covered to 14 and have the days being filled in for the whole form. The problem is that all rooms are not showing up for the whole 14 days, If 17/11/2008 is picked from the calendar all booked rooms 1,6,3,9,8,4,& 5 show as required, but not rm 7, pick 18/11 and only rm 9 & 5 show, 19/11 only rm 3,9,4,& 5 show, 20/11 only rm 1,3,9,4,& 5 show, 21/11 only rm 1,6,3,9,8,4, & 5 show. Room 2 has a guest booked in on the 1/12 this does not show up on the grid until you pick 29/11. Room 7 has a guest booked in on the 29/11 and this does not show up until you pick the 29/11. Any ideas why this is happening.

Another problem i am having is entering a new guests details. On entering thier details in the Reservation form a going to the next form(button Tariff) and picking there Customer ID number from the drop down list you get a message "Field cannot be updated", on opening the help file it says" you tried to change the value of a control whose locked value is set to yes" this not so, all locked propeerties are set to "No". I am the only one using the DB. This happens three times and then the form can be used, the text boxes it happens on are CustomerID, Name and Reservation ID.
Again any ideas on why this is happening, any help will be greatly appreciated.
Itchysf

I don't have any idea, but the place to start looking is qryDailyBookings which is the data source for the grid. Compare the query results with the grid display. Do they match or are they different? If they match, then the problem most likely is the query, not the grid. Have you modified the query or Added new constraints recently?

[itchysf]

I don't have any idea, but the place to start looking is qryDailyBookings which is the data source for the grid. Compare the query results with the grid display. Do they match or are they different? If they match, then the problem most likely is the query, not the grid. Have you modified the query or Added new constraints recently?

qryDailyBookings has not been changed in any way, the qry results match the grid display on the dates that it are displayed, I cann't work out why when sume dates are picked from the calendar all rooms show up correctly and when other dates are picked only sume room show up. The only thing I have done is add a bit of code before your code runs to populate the grid, the code clears the form of prevoiusly populated grids, the code is:

         On Error Resume Next
         For Each c In Me.Controls
       If InStr(1, c.Name, "Day") <> 0 Then
            c.Value = Vaccant
            c.BackColor = vbWhite
     End If
 
Next
On Error GoTo 0
 

[puppydogbuddy]

qryDailyBookings has not been changed in any way, the qry results match the grid display on the dates that it are displayed, I cann't work out why when sume dates are picked from the calendar all rooms show up correctly and when other dates are picked only sume room show up. The only thing I have done is add a bit of code before your code runs to populate the grid, the code clears the form of prevoiusly populated grids, the code is:

         On Error Resume Next
         For Each c In Me.Controls
       If InStr(1, c.Name, "Day") <> 0 Then
            c.Value = Vaccant
            c.BackColor = vbWhite
     End If
 
Next
On Error GoTo 0
 

Your new code appears to be the cause of the problem. It conflicts with part of the code I put in (excerpted below). Instead of adding your code, you should have added one line to the code I gave you (shown below).

Loop
 
  'make a final loop around the grid to pick up and identify all vacancies, where the calendar
    ' date is prior to the arrival and where the calendar date is after the departure date
    On Error Resume Next
       For Each ctl In Me.Controls
           If InStr(1, ctl.Name, "Day") <> 0 Then
              If IsNull(ctl.Value) Then
                 ctl.Value = "Vacant"
                 ctl.BackColor = vbWhite       '<<<<<<<<<<<<<<add this line
              End If
           End If
       Next
    On Error GoTo 0
 
Else
    Exit Sub
End If