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 qryDailyBooking s.
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

[puppydogbuddy]

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

[itchysf]

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 qryDailyBooking s is made on this tbl.
Thankyou again and please help.

[puppydogbuddy]

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…..so mething 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

[puppydogbuddy]

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_room_booking_system_data_model.html

[puppydogbuddy]

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

[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 qryDailyBooking s 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

[puppydogbuddy]

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 qryDailyBooking s 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?

[puppydogbuddy]

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]))

[yaaara]

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]))

[puppydogbuddy]

Hi Yaaara,
Thanks for the info. We have to wait for itchysf to tell us whether it works or not.

pDog

[itchysf]

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 "qryDailyBookin gs" 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_mous eDown" to show the calender, any help with this problem is greatly appreciated and needed.
Thanks guys.

[puppydogbuddy]

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_mous eDown" 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_mouse Down function in the StartDate textbox should be as follows:
= startDate_mouse Down()

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_mouse Down () 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....Fo rms!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_mouse Down() function procedure should be executed.

[itchysf]

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

[puppydogbuddy]

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.....yo u 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_mouse Down 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.