Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Making a calendar

    I’m trying to build a calendar. On my form, I have some unbound fields: [StartDate], [cboMonth], [txtYear]. [cboMonth] is a drop down of the months. The fields [cboMonth] and [txtYear] only serve to let the user select which month he wants to look at. I have a code that sets the field [StartDate] to be the first Sunday on the calendar. It works great. I’m trying to get the dates that are not part of that month to grey out. This is where I’m having problems. The dates are labeled Date1, Date2….through Date42. The list boxes under the date fields are labeled Day1, Day2….through Day42. I used this code:
    Private Sub GreyOutDates()
    Dim X As Integer
    Dim S1 As String, S2 As String
    For X = 1 To 42
    S1 = "Date" & X
    S2 = "Day" & X


    If Month(S1) <> Me.cboMonth Then
    Forms!frmSchShiftCalendar(S2).BackColor = &HD8D8D8
    End If
    Next X
    EndSub
    It goes to the debugger and the “if” statement is wrong. I’m having a hard time getting this done.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a look at this rather than re-invent the wheel ?
    http://www.mendipdatasystems.co.uk/b...ker/4594398118
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I was hoping not to go that route. My calendar works very well except for greying out the dates that are not part of the calendar.

  4. #4
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ok, In the immediate window type
    ?Month("Date1")

    and see what you get - an error. What I have typed is what you are passing to the month function - a string, not the value of the control.
    You will need to need evaluate the control something like

    Month(Me.Controls(S1).Value)


    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I got the same error. It doesn't like the IF statement. Didn't I make the S1 and S2 a string?

  6. #6
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What is the error ?

    Add a debug.print Me.Controls(S1).Value before the If to see what it evaluates to?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by UT227 View Post
    I got the same error. It doesn't like the IF statement. Didn't I make the S1 and S2 a string?
    It is not the IF() function, it is the MONTH() function. The MONTH() function requires a DATE!

    (Syntax -> Month(date))

    Code:
    If Month(S1) <> Me.cboMonth Then
    You are passing S1 to the month function - where S1 is declared as a STRING NOT a DATE! (as Minty stated)



    Maybe you could post an example of your calendar and code........

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Changed the code to:
    Private Sub GreyOutDates()

    Dim X As Integer
    Dim S1 As String, S2 As String

    For X = 1 To 42
    S1 = "Date" & X
    S2 = "Day" & X

    If Month(Forms!frmSchShiftCalendar(S1)) <> Me.cboMonth Then
    Forms!frmSchShiftCalendar(S2).BackColor = &HD8D8D8
    End If

    Next X

    End Sub

    It worked for the first month that I open. However, any month after that I change it to everything goes grey.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Consider hiding the non valid dates rather than disabling or greying them out.
    That's the approach I use in my better date picker utility -see link in post #2.

    Just out of interest why didn't you want to use that?
    Not suitable for your needs … or you wanted the challenge of creating your own?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Why not refer to the control value as I showed ? Does it not make sense.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It worked for the first month that I open. However, any month after that I change it to everything goes grey.
    Where are you calling the sub?
    Are you calling it again when you change months?

  12. #12
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I have use for all of the dates, so don't really want to hide the dates. I just want to grey them out so it's obvious that it's on a previous month or next month.

  13. #13
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I appreciate all of the input, but now I'm completely lost. I'm going to have to just leave it be for the time being. I thought I was using the date, but I guess I'm not. I tried the best I could what Minty said, but I'm just not understanding. Guess I need to figure it out a little more.

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Why not post a sample so we can see it instead of guessing at it.

  15. #15
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I will have to take it out of a larger database. It'll take me some time.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-30-2016, 10:53 AM
  2. Calendar
    By jaykappy in forum Programming
    Replies: 2
    Last Post: 02-27-2012, 01:00 PM
  3. Calendar example
    By pkstormy in forum Code Repository
    Replies: 1
    Last Post: 02-28-2011, 04:09 AM
  4. Help with making a calendar
    By slmorgan25 in forum Access
    Replies: 1
    Last Post: 09-15-2010, 10:32 AM
  5. Help with Calendar
    By alanl in forum Access
    Replies: 1
    Last Post: 03-15-2010, 08:16 AM

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