Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125

    Form to display a calendar of specified stored dates

    Morning all,

    I have a sales order database which stores client orders. I need to try create a production management form which will display a calendar view of the order number & possibly the customer name as an item on the date of [PromisedDate] in the calendar.

    Click image for larger version. 

Name:	SalesOrderCalendar.PNG 
Views:	78 
Size:	10.3 KB 
ID:	45915

    Does anyone have any experience or suggestions I can look at regarding a calendar within a form preferably a "month" view of dates stored in a table?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,187
    I had/have an outlook calendar style db but can't locate it just now. I'm thinking that instead of creating appointments you'd create orders. Perhaps you'll have some luck searching for it. I think I even posted a copy of it somewhere in here but it might have been as long as a year ago.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,614
    Before looking for a solution you need to specify what the use can do. Is it just to look at the data ie form not editable, or does it need to be editable?

    If the former, you might want to consider a report, you can specify 7 columns to represent the days on the week. Then you would need to known a week number to specify the row. Both easily determined with date functions

  4. #4
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Thanks Micron, I'll take a look through the forum again, I did spent some time last night searching.

    Hello Ajax, I'm thinking to create something like the below which if an item is clicked it'll open to that specific form which is the [SalesOrderID].

    Image below courtesy of Trello, however, I'm now creating our own Access system with this similar feature:-
    Attached Thumbnails Attached Thumbnails SalesOrderCalendarMonth.jpg  

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,648
    Google Peter Hibbs Calendar

    Here is one I modified for someone a few years back.
    https://www.access-programmers.co.uk...tabase.275897/
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,614
    a report won't do as the columns only appear in print preview - which is not clickable

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,009
    Calendars aren't that hard to code but they are time consuming.

    here's one I use.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Hello moke123, your "frmMainCalendar" is really nice! Do you think it's possible to add an additional bit of code that could display items from my SalesOrder table? When that item is clicked it'll take to user to my sales order view form, "frmSalesOrderView"?

    Screenshot below is of your calendar with my paint job of adding the sales orders as "items" to the calendar. The production team can overview the month to see what they need to produce in a calendar view instead of a continuous form, they'll have two options.
    Attached Thumbnails Attached Thumbnails moke123CalendarDesign.png  

  9. #9
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    869
    Hi Greg
    The Calendar examples that I have looked at allow you to add specific Appointments on certain dates.
    I have not yet seen one that then allows you to click on an Item from the Calendar and then display a specific record related to it.

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,009
    You could probably modify the function ClickBox to open a form displaying the Sales orders for that day and then select the specific order from there.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,009
    The two procedures you would need to modify to get your data are fGetArray and DateArry.

    fGetArray gets a recordset of the distinct dates from your table in the selected month where there is a record for that day.
    It then loops through that recordset and adds the date as the key value of a scripting dictionary and for each date calls the DateArry function which concatenates the records for that date and adds them as the Item value of the dictionary.
    Then I iterate through the 42 textboxes on the form (note that the controls tag contains the date) and if the date exists in the dictionary it writes the dictionary item value to that textbox.

    The ClickBox function just opens a form which is filtered to the date in the textboxes tag property.

    Hope this makes sense.


    Code:
    Public Sub fGetArray()
    
        On Error GoTo fGetArray_Error
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
    
    
        Dim strsql As String, EndDate As Date, strKey As Variant
        Dim strItem As String, i As Integer, ctlK As Variant
    
    
        Set dict = New Scripting.Dictionary
    
    
        EndDate = DateAdd("d", 42, FirstDateOnGrid)
    
    
        strsql = "select Distinct dteDate from  tblEvents where dteDate between #" & FirstDateOnGrid & "# and #" & EndDate & "# order by dteDate"
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strsql)
    
    
        If rs.BOF And rs.EOF Then
            GoTo MyExit
        End If
    
    
        Do Until rs.EOF
    
    
            strKey = rs!dteDate
            strItem = DateArry(rs!dteDate)
    
    
            dict.Add strKey, CStr(strItem)
    
    
            rs.MoveNext
        Loop
    
    
        For i = 1 To 42
    
    
            ctlK = CDate(Forms("frmMainCalendar").Controls("Bx" & i).Tag)
    
    
            If dict.Exists(ctlK) Then
    
    
                Forms("frmMainCalendar").Controls("Bx" & i).Value = dict.Item(ctlK)
    
    
            End If
    
    
        Next i
    
    
        Set dict = Nothing
    
    
        Me.btoUp.SetFocus
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    
        On Error GoTo 0
        Exit Sub
    
    
    fGetArray_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fGetArray, line " & Erl & "."
    
    
    End Sub
    
    
    Function DateArry(dte As Date) As String
    
    
        On Error GoTo DateArry_Error
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strsql As String, strOut As String
    
    
        strsql = "select * from  tblEvents where dteDate = #" & dte & "# order by tmeTime"
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strsql)
    
    
        If rs.BOF And rs.EOF Then
            GoTo MyExit
             'MsgBox 0
        End If
    
    
        Do Until rs.EOF
    
    
            strOut = strOut & (Format(rs!tmeTime, "h:nnAMPM") + " ") & rs!EventDescription & vbNewLine
    
    
            rs.MoveNext
        Loop
    
    
        DateArry = strOut
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    
        On Error GoTo 0
        Exit Function
    
    
    DateArry_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateArry, line " & Erl & "."
    
    
    End Function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Morning moke123,
    Thanks for the information.
    I’ll give it a bash and come back here when I get stuck.

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,958
    Here's an idea of how to get a detail form for an item on the calendar:

    Click image for larger version. 

Name:	cal.png 
Views:	54 
Size:	59.0 KB 
ID:	45932

  14. #14
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Hello davegri, that’s a great example and that’s just what I’m looking to build.
    I’ll need to setup the data to pull from my sales order table which will store the date promised to the customer of the order and the sales order number. Linked is the quantity, product description and product code which I can list below so that when the day is selected it’ll display within the subform below.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,958
    Hello davegri, thatís a great example and thatís just what Iím looking to build.
    See attached DB. It's very simple in terms of forms, only two. The main (and edit) form is just a continuous form, but could be any form as needed for editing. See the comments in the calendar form VBA to load your data into the calendar and setup the listbox.

    Calendar-davegri-v01.zip

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calendar thingy for inputting dates
    By S2000magician in forum Access
    Replies: 3
    Last Post: 07-17-2019, 01:19 PM
  2. Calendar too small to see dates
    By dcavaiani in forum Access
    Replies: 2
    Last Post: 03-24-2019, 03:33 AM
  3. Replies: 3
    Last Post: 11-03-2014, 03:30 PM
  4. Help formatting calendar dates
    By cmb in forum Modules
    Replies: 1
    Last Post: 10-10-2014, 11:06 AM
  5. Calendar to and From Dates
    By bobfin in forum Reports
    Replies: 10
    Last Post: 08-05-2010, 07:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums