Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2014
    Location
    Quebec, Canada
    Posts
    3

    Red face Unanswered: Listbox calendar

    Hi all.

    I am trying to convert my Excel app into an MS-Access app.
    I had done this years ago, and no longer have a copy of what I did.

    I am basically trying to make a monthly assignment calendar for all my personnel.

    Enclosed in the zip file is a picture my excel spreadsheet which is what I am trying to do in Access.

    I remember that I had a listbox (or 2?) that had columns for the month, and rows for the personnel, and the rest of the columns were filled in with the dates that people were tasked.
    I have been trying to think this through for the past 2 weeks to no avail.

    What would really set me up on the right path is how to auto-populate a listbox's columns to reflect the days of the selected month.

    I had done this in VB.net in 2005, and have not played with VB since 2008 and my memory is not as good as it used to be.

    VB.net is not an option where I work now, so it has to be Access.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's an example. Two combobox controls are used but you can easily transpose for listbox controls
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_ListSep As String
    
    Private Sub Combo_Month_AfterUpdate()
    
        With Me.Combo_Day
            .RowSource = GetDayList(Me.Combo_Month.Column(0))
            .Value = 1
        End With
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        m_ListSep = GetListSeparator
        With Me.Combo_Month
            .ColumnCount = 2
            .RowSourceType = "Value List"
            .RowSource = GetMonthList
            .Value = 1
        End With
        With Me.Combo_Day
            .ColumnCount = 2
            .RowSourceType = "Value List"
            .RowSource = GetDayList(1)
            .Value = 1
        End With
        
    End Sub
    
    Private Function GetMonthList() As String
    
        Dim i As Long
        
        For i = 1 To 12
            If Len(GetMonthList) > 0 Then GetMonthList = GetMonthList & m_ListSep
            GetMonthList = GetMonthList & i & m_ListSep & Format(Year(Now) & "-" & i & "-" & 1, "mmm")
        Next i
        
    End Function
    
    Private Function GetDayList(ByVal Month As Long) As String
    
        Dim i As Long
        Dim dteRunning As Date
        
        dteRunning = CDate(Year(Now) & "-" & Month & "-1")
        For i = 1 To 31
            If Len(GetDayList) > 0 Then GetDayList = GetDayList & m_ListSep
            GetDayList = GetDayList & i & m_ListSep & Format(Year(Now) & "-" & Month & "-" & i, "ddd")
            dteRunning = DateAdd("d", 1, dteRunning)
            If VBA.Month(dteRunning) > Month Then Exit For
        Next i
        
    End Function
    
    Private Function GetListSeparator() As String
    
        If InStr(CStr(2.5), ",") Then
            GetListSeparator = ";"
        Else
            GetListSeparator = ","
        End If
              
    End Function
    Have a nice day!

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    auto-populate a listbox's columns to reflect the days of the selected month..
    maybe this will get you started:

    Code:
    private sub FillDays(intYYYY as integer, intMM as integer)
    dim dPointer as date
    dim dEnd as date
    dim sItem as string
    dPointer = dateserial(intYYYY, intMM, 1)
    dEnd = dateadd("m", 1, dPointer))
      with myList
        .rowsource = vbnullstring
        do while dPointer < dEnd
          sItem = right$("0" & cstr(datepart("d",dPointer)),2) & ";" & weekdayname(weekday(dPointer))
          .additem sItem
          dPointer = dateadd("d", 1, dPointer)
        loop
      end with
    end sub
    the above talks to your listbox as "myList"
    - in reality it should be Me.NameOfTheList assuming code is running in the module of the form holding the list.
    your list is assumed to have two text columns holiding dd (01, 02, 03 etc) and dayname(Monday, Tuesday etc)

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2014
    Location
    Quebec, Canada
    Posts
    3
    Will try that now

    Thx.

  5. #5
    Join Date
    Feb 2014
    Location
    Quebec, Canada
    Posts
    3
    That doesn't seem to be what I'm looking for.

    I have a way of making the ListBox show the correct amount of columns per month.

    Number of days in month:
    Code:
    Function DaysInMonth(MyDate)
        Dim NextMonth, EndOfMonth
        NextMonth = DateAdd("m", 1, MyDate)
        EndOfMonth = NextMonth - DatePart("d", NextMonth)
        DaysInMonth = DatePart("d", EndOfMonth)
    End Function
    Make List9 Show correct amount of columns
    Code:
    Private Sub Form_Load()
    Me.List9.ColumnCount = DaysInMonth(Date)
    End Sub
    Now I just need to find a way to format and add my values to the ListBox from a query.

    Can't seem to find a way to add values to secific columns...
    Tried
    me.List9.Column(12,0).value = "text"
    and many different variations of this to no avail.

    The number of columns will also change depending on selected month, so can't really be using
    Me.List9.AddItem ";;;;;Text;;;;;;;;;;;;;"

    Here is a pic of what I am trying to convert to access
    Attached Thumbnails Attached Thumbnails example.jpg  

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aaaah: you want a column-per-day rather than a row-per-day.

    .Column(c,r) is read-only.

    bludgeoning a list control into a grid control look-alike is going to be very ugly and involve all those lovely semicolons that you can't really be using

    Access does have real grid controls, but they are only usable for looking at tables & query results.

    how about a subform with a query as rowsource?

    with a bit of tweaking you should be able to get it to look close to what you want and it will be way faster than any workaround with a list.

    the data will be editable (if you want it to be) if your query is simple enough.

    izy
    currently using SS 2008R2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •