Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First of month entry in Form (Access XP)

    Hi

    I'm trying to design a form which includes (amongst other things) a start date for a new member. All new starts must be on the 1st of a month. Starts can be in the future, or the immediate past, therefore I cannot just use a "firstofmonth" on the current date.

    I am hoping to put some code in to either take the date entered and use a "First of Month" module on it to change it after entry. or my preferred choice, to restrict entry to the first of a month. I'm not sure however if this should be an input mask, or a validation requirement, and if so, how do I code it. Or is there yet another way?

    Hoping for your help


    Thanks

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Just a thought, but why don't you put a combobox on the form that has two values...
    FirstOfMonth... DateSerial(Year(Date()),Month(Date()),1)
    FirstOfNextMonth... DateSerial(Year(Date()),Month(Date())+1,1)
    That way the user would only be able to choose one of the two...

    Does that help?

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Wait a minute... I just realized that I misunderstood... Are they going to be able to choose ANY month in the future??... For example, IF I sign up today, can I start a year and a half from now, on the first of the month?

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Hi Colin...

    Why not do something like only showing the user a combo box with the Month name?
    ie... They select that the person wants to start in March or May or whatever... Behind the scenes on the form you take that value and fill a date field with the correct date...

    If you don't want the users to be able to input a date and chance that they get it wrong, don't let them input a date at all.... Only give them a choice... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    and PLEASE tell me that they are not going into the tables and making changes as they wish...

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Hi Trudi


    No you didn't misunderstand.

    In reality, they will join on the first of the current, previous or next months. Its possible that someone might want Next +1, but not reaalistically very often at all.

    I could therefore use 4 values in the combo.

    The only minor issue with this is that it doesn't stop someone amending the date later and getting it wrong.


    Colin

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Hi Trudi


    I like the idea of just showing the alpha names. That's perfect.

    And no, the only one who can edit in tables is me (but yes it has to happen sometimes)


    Thanks


    Colin

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Trudi

    Me again.

    Having looked at this a bit more I've decided it would in fact work better with your first suggestion.

    How do I get the combo box to accept the function

    DateSerial(Year(Date()),Month(Date()),1)
    ETC

    as a record source however.


    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: First of month entry in Form (Access XP)

    Set the Row Source Type of the combo box to Value List, and leave the Row Source blank. Populate the list part of the combo box in the On Load event of the form. Let's say that the combo box is named cbxStartDate:

    Private Sub Form_Load()
    Dim n As Integer
    For n = -1 To 1
    cbxStartDate.AddItem DateSerial(Year(Date), Month(Date) + n, 1)
    Next n
    End Sub

    For n = -1 To 1 means that you will add the first day of the previous month (n = -1), current month (n = 0) and next month (n = 1). If you would like to add more months, just adjust the loop limits.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First of month entry in Form (Access XP)

    Thanks to Hans & Trudi

    A great help.

    Colin

Posting Permissions

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