Looping through subform records

[Penmore]

I’m in the process of developing a holiday property availability form for displaying the available and free dates for a particular property in a particular period (usually 35 days). The main form allows the selection of the start and end dates of the period using a Calendar control and then completes text boxes with the days of the week for each day of the period.

The next step for the user will be to click on a property button to run a select query to find the booking records that are present for the availability period being looked at. Each booking record has the start date and the number of days booked together with the property name and other fields.

I have the select query results as a subform and can access fields on the first record using
Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate
MsgBox Mydate
However, I need to be able to access each record on the subform to be able to colour each text box for each day the property is booked so the user can see visually which days are booked for that period.

I’m struggling to find out how access the subform recordset to be able to loop through the records and pick off the days that are booked. I’ve tried defining a recordset but VB rejects the dim setting with Can't find project or library. Can somebody point me in the right direction?

 

[RuralGuy]

Are you using DAO? Do you have a reference set to the DAO Library?

 

[Penmore]

I'm a bit blank here, I'm using an Office Pro Access 2003 upgrade. I haven't knowlingly set the reference to the DAO Library. The previous version I used was from Office Pro 2000 and as far as my memory servers me, it worked them without problem.

 

[RuralGuy]

I believe the default is ADO so you will need to set a reference to DAO if that is what you are using. <ALT> F11 - Tools>References and scroll down to Microsoft DAO 3.6 Object Library and check it. You might want to uncheck ADO (ActiveX) if you are not using it.

 

[Penmore]

RuralGuy, thanks for that, I have to go out now but will try what you have suggested later today and let you know.

Appreciate your help

 

[Penmore]

Hi,
Well thanks to RuralGuy I have that working now. I had to remove an earlier version of the object library before I could set Microsoft DAO 3.6 Object Library but once I'd done that it loaded straight away.

I used the following code to test and all record dates were displayed:

Dim temprs As Recordset

temprs.MoveFirst
While Not temprs.EOF
Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate
MsgBox Mydate
temprs.MoveNext
Wend

I just need to replace this with the code for isolating the day number of each date, changing the textbox back color to the appropriate colour for the booking.

Thanks again for your prompt reply and first class help.

Peter

 

[RuralGuy]

Glad I could help.