Display images in a calendar report?

[hinsdale1]

I am developing a wardrobe database and would like to generate simple reports of prior months outfit usage in calendar form. Standard blank calendar form with the appropriate days of the week structure for the particular month as the column headers and with each box filled with only the date and a picture of the outfit worn.



It is obviously a simple task to generate a query to produce the datesworn and the image of the outfit worn for the time perioud.. but am looking for approaches to automate generation of the report to fill a calendar.


I am not terribly good at all this so am hoping for some help in how I might approach something like this. All thoughts, suggestions are welcomed and most appreciated.

[June7]

I have seen Access databases dedicated to the generation of calendars. It's not simple because requires a reconfiguration of data that is not a conventional structure. Takes a lot of VBA. Suggest a search of forum and Google. Might hit a good example.

[amrut]

As suggested by June, lot of VBA is required. I have successfully tried a similar report. I required some data to be displayed besides the date in a calender style report.
Here is the process -
-Created a grid of unbound textboxes for date and listboxes to show required data.(6 rows X 7 Columns)
-In the command button's code (which is used to open the report), opened the report in Report view
-Looped through the listboxes of report and assigned appropriate SQL as row source.
-In the report's load event, assign the dates to the unbound text boxes
-hide the text boxes not required
You can use image controls instead of list boxes.

[hinsdale1]

Thanks for the replies.

Amrut, your process sounds like what I am going to have to accomplish and I appreciate your taking the time to point me in the right direction.

If you have a chance to post some of your loop code, or a few snippets of other relevant subs it would always be helpful... I am still getting my feet wet so always great to be looking at some known working bits of code.

thanks again.

[amrut]

'this is the button's code opening report

Dim FirstDay As Integer
Dim DayIndex As Integer
Dim TextBoxIndex As Integer
Dim Done As Boolean
Dim ctlCalendar As control
Dim strNum, strSQL, upSQL As String

FirstDay = Weekday(DateSerial(Me.cmbYr, Me.cmbMth, 1)) ' Find out the first day of the week
    DayIndex = 1   ' Start counting days at 1
    TextBoxIndex = FirstDay   
    Done = False
    DoCmd.OpenReport "CalenderSchedule", acViewReport
       While Not (Done)
        
        strNum = right("00" & TextBoxIndex, 2)

        Set ctlCalendar = Reports("CalenderSchedule").Controls("List" & strNum) 

       strSQL = "SELECT Contracts.QuarryName FROM Contracts INNER JOIN workDetails ON Contracts.ContractID = workDetails.QName" _
        & " WHERE Day([workDate])=" & DayIndex & " AND Month([workDate])=" & Me.cmbMth & " AND Year([workDate])=" & Me.cmbYr & ";"

        Reports("CalenderSchedule").Controls("List" & strNum).RowSource = strSQL
        DayIndex = DayIndex + 1
        TextBoxIndex = TextBoxIndex + 1
    
        ' Are we done?  Check to see if we have indexed into next month
        If (Month(DateSerial(Forms!Schedule!cmbYr, Forms!Schedule!cmbMth, 1) + (DayIndex - 1)) <> Month(DateSerial(Forms!Schedule!cmbYr, Forms!Schedule!cmbMth, 1))) Then
           Done = True
        End If
    
    Wend
    
    
    Set ctlCalendar = Nothing
*************************************************************************
'This is the loop in report's load event. 
    FirstDay = Weekday(DateSerial(Forms!BlastSchedule!cmbYr, Forms!BlastSchedule!cmbMth, 1))  ' Figure out the first day of the week
    DayIndex = 1   ' Start counting days at 1
    TextBoxIndex = FirstDay   ' Start indexing text boxes at first day in month
    Done = False
    
 While Not (Done)
        ' Set the value of the correct date text box
        
        ' Make a 2-digit string with the number, e.g. "01" or "08" or "12" etc.
        strNum = right("00" & TextBoxIndex, 2)
        
        Set ctlCalendar = Me("WDay" & strNum) 
        ctlCalendar.Value = DayIndex          ' add dates to text boxes
                      
        DayIndex = DayIndex + 1
        TextBoxIndex = TextBoxIndex + 1
    
        ' Are we done?  Check to see if we have indexed into next month
        If (Month(DateSerial(Forms!Schedule!cmbYr, Forms!Schedule!cmbMth, 1) + (DayIndex - 1)) <> Month(DateSerial(Forms!Schedule!cmbYr, Forms!Schedule!cmbMth, 1))) Then
           Done = True
        End If
    
    Wend
    
    Set ctlCalendar = Nothing
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If IsNull(ctl) = True Then
            ctl.Visible = False                                'hide the text boxes not required
            Me("List" & right(ctl.Name, 2)).Visible = False             'hide the list boxes not required
            End If
        End If
    Next ctl

I tried to combine the above loops but text box value could not be assigned in report's load event.

This is snapshot of part of report in design view. The bold font is for text boxes displaying date and other the list box.The textboxes are numbered as WDay01 to WDay42 and list boxes from List01 to List42.

[hinsdale1]

Brilliant! Will save me lots of time seeing what you have done.. so kind of you to post. I am most grateful.