Using Concatenation formula to set the value of a form control 1

[CharlieT302]

Hello Everyone,

I need to be able to set value of an unbound control using a concatenation formula.

I have a calendar form with 42 unbound controls named as such: M0, M1, M2, M3, and so forth.

Out of the first seven controls (M0 thru M6), one of them will begin with the number 1; as determined by a formula. A separate unbound box (called Start_Num) stores the "name" of the control (less the 'M' designation) whose value is 1.

Example:
Start_Num = 5 (which is control name: M5 less the letter).

All I want to do is set the following controls with the next sequential value, so that M6 = 2, and M7 = 3, and so on.

As a test, I used a simple formula to navigate to the next control:
DoCmd.GoToControl "M" & Me.Start_Num + 1 (this moves to M6. In other words: M & (5 + 1)
This works fine. Note: this was just a test. I do not need to move to the control.

I just need a similar way to set the value of the next control so that:
"M"& me.Start_Num + 1 = 2
"M"& me.Start_Num + 2 = 3
etc.

Ideas?

 

[MajP]

  Dim i As Integer
  Dim j As Integer
 'Find control with 1 in it
 ' clear
  For i = 0 To 6
    If Me.Controls("M" & i).Value = 1 Then Exit For
  Next i
  'Add 1 to all controls passed the one with a 1
  For j = i + 1 To 6
     Me.Controls("M" & j).Value = j - i + 1
  Next j

 

[CharlieT302]

Thank you very much. I think this will work great.

 

[CharlieT302]

Hi,
I have a follow up to my last question. I have a calendar control utilizing the type of code you suggested. I also have a Next arrow that jumps the calendar display to the following month. The calendar does, in deed, move to the next month and number correctly.

However, as is typical in calendars, the empty spots following the last day of the month should be populate with the beginning dates of the next month. In other words, if the last day is the 28th, and there are currently five vacant controls remaining, I would like them to be filled with the beginning of the month.

The problem is that rather than the first vacant control beginning with the number 1 and numbering consecutively, it simply leaves whatever dates were there from the month before.

Heres is my code so far:

Num Month = the month to which the user is moving
Num_Next_Month = should be the beginning dates of the following month.
enom = The ending date within the month (occurs in code higher up in the page)

Num_Month:

Dim i As Integer
Dim J As Integer

For i = 1 To 7
If Me.Controls("M" & i).Value = 1 Then Exit For 'Find control with 1 in it
Next i

For J = i + 1 To i + enom - 1

Me.Controls("M" & J).Value = J - i + 1 'Add 1 to the value of all controls beyond the control with a value of 1
Me.Controls("M" & J).FontWeight = 700
Me.Controls("M" & i).FontWeight = 700

Next J

GoTo Num_Next_Month
'GoTo exit_sub

Num_Next_Month:

Dim A As Integer
Dim k As Integer

For A = 29 To 42
If Me.Controls("M" & A).Value = enom Then Exit For 'Find control with 1 in it
Next A

'For k = i + enom - 1 + 1 To 14
For k = A + 1 To A + (42 - A) '
Me.Controls("M" & k).Value = 1

Next k

I am extremely grateful for any ideas anyone has.

Thanks,

 

[MajP]

In thread702-1723375, I demonstrate that along with about a thousand other things related to a calendar control. The links to the code may still be working. It is extremely long thread.

 

[CharlieT302]

Oops! An apology for the last line of the code I pasted. The value should not be "1". That was put in as a test.
It doesn't really matter anyway since no matter what I put in it was ignored.

Majp

Thanks for the string. You are right, it is a very long string. I have looking at it and don't seem to find what I need (of course it has been a long day and I am a little tired).

Basically, I cannot seem to set the numbering of controls after end of the month date has been reached. All I want is for it to start over again with the number 1 until the last control is filled.

I will see again if there is anything in the thread that pertains to me but, in the meantime, if you or anyone can provide a way of numbering those lasts few empty controls with the beginning dates of the next month. I will be most appreciative. I keep thinking there is something simple I am overlooking.

 

[MajP]

I see what you want to do. I will try to post some code tomorrow. But I will explain this in general for others.
To make a calendar control you normally do 6 rows and 7 columns of labels and maybe textboxes if you plan to enter information for that day. Easiest is to simply name them consistently something like lbl1 - lbl42, txt1 to txt42.

1) Calculate the offset (how far the first day is shifted over). In this case the offset is 6. The first day is shifted over 6 additional columns. I make a standard function that takes a month and year then returns the offset
2) Calculate the number of the last day of the month. For august it is 31. Build a function that you can pass a year and month and get the last day.
3) Calculate the number of the last day of previous month (if doing the previous month labels)
3) Now start iterating the controls. Go all the way from 1 to 42.
4) Calculate the (Counter - Offset). To figure out what labels goes where, you subtract (counter - offset). So in this case when the counter is the seventh control the label would be (7-6) = 1. See sat the 1st.
5) Have a couple of if checks
if counter - offset is less than 1 then you would do the previous month labels (26-31 July)
if counter - offset is 1 or more and less than the last day then it is in this month (1-31 Aug)
if counter - offset is greater than or equal to the last day then it is in the next month (1-5 Sep)

6) For the Previous Month part (counter - offset) <=0 then the label is
LastDayPreviousMonth - offset + counter
To show this works when the counter = 1 and august has an offset of 6 and July has a last day of 31
31 - 6 + 1 = 26

7) For the current month part (counter - offset) >0 and < last day of month then the label is
Counter - offset

8) For the next month part (counter - offset) > last day of month the label is
Counter - lastDay - offset.
To show that works 1 september is the 38th control the offset is six the lastday is 31.
38-31-6 = 1.

 

[CharlieT302]

Thank you MajP,

I will be sure to check for your code tomorrow. I am really frustrated as I need to get this running. It should be simple.
Here is the code I created so far (I modified it from earlier). The code pertains to the numbering of the current month and the beginning dates of the following month that fills the empty controls that follow the last day of the current calendar.

Note: the dates preceding the first of the month display correctly (they are not represented here) as do those of the current month. The controls following the last date of the month will not populate. If the date of the current month goes into the last row (May 31) and the following month ends in the prior week (June 30), the controls following June 30 remain at their May values.

In fact, Dim L (from code) seems to be ignored completely. I have not used the - For Loop - function before and am still getting used to it. Anyway, I will touch base tomorrow. Thanks for your help.

Num_Month:

Dim i As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer

For i = 1 To 7
If Me.Controls("M" & i).Value = 1 Then Exit For
Next i

For J = i + 1 To i + Enom - 1
Me.Controls("M" & J).Value = J - i + 1 'Add 1 to the value of all controls beyond the control with a value of 1
Me.Controls("M" & J).FontWeight = 700
Me.Controls("M" & i).FontWeight = 700

Next J

For K = i + Enom To 41
If Me.Controls("M" & K).Value = Enom Then Exit For
Next K

For L = (K + 1) To 42
Me.Controls("M" & L).Value = 0
Me.Controls("M" & L).Value = (L - 42) + (42 - K)

Next L

GoTo Format_Month_Dates

 

[MajP]

You need to do some error checking. See what values you are getting

debug.print "Enom = " & enom
For K = i + Enom To 41
debug.print Me.Controls("M" & K).Value
If Me.Controls("M" & K).Value = Enom Then Exit For
Next K
Debug.print "K = " & K

 

[MajP]

Public Sub FillMonthLabels(frm As Access.Form, TheYear As Integer, TheMonth As Integer)
'==================================================================================================
'//Fills the grids label(s) with the correct day and;
'  1) Hides day labels that dont have a date associated with them
'  2) Disable and locks text boxes without a date so data cant be entered
'==================================================================================================
    Dim dayLabel As Access.Label
    Dim dayTextBox As Access.TextBox    'Added to disable/lock text boxes without a date so data cant be entered
    Dim I As Integer
    Dim FirstDayOfMonth As Date   'First of month
    Dim DaysInMonth As Integer    'Days in month
    Dim LastDayOfPreviousMonth As Date
    Dim intOffSet As Integer      'Offset to first label for month.
    Dim intDay As Integer         'Day under consideration.
    Const dayLabelBackColor = 14211288    'Gray color thats used for Holiday shading/unshading
    
    FirstDayOfMonth = getFirstOfMonth(TheYear, TheMonth)
    DaysInMonth = getDaysInMonth(FirstDayOfMonth)   'Days in month.
    LastDayOfPreviousMonth = getLastDayOfPreviousMonth(TheYear, TheMonth)
    intOffSet = getOffset(TheYear, TheMonth, vbSunday)    'Offset to first label for month.
    ' Debug.Print DaysInMonth
    frm.cmdSubFormTransButton.SetFocus    'Sets focus to a transparent button in the subcalendar form
    frm.LblMonth.Caption = Format(TheMonth & "/1/2015", "MMMM")
    For I = 1 To 42
        Set dayLabel = frm.Controls("lbl" & I)
        Set dayTextBox = frm.Controls("txt" & I)    'Added to disable/lock text boxes without a date so data cant be entered
        dayLabel.Caption = ""
        dayLabel.BackColor = dayLabelBackColor  'Resets the backcolor to Gray
        intDay = I - intOffSet        'Transforms label number to day in month
        dayLabel.Visible = True
        dayTextBox.Enabled = True
        'These are the labels before the first day of the current month so need to fill in last months values
        If intDay <= 0 Then
          dayLabel.Caption = Day(LastDayOfPreviousMonth) - intOffSet + I
          dayLabel.BackColor = vbGreen
          'daylabel.visible = false
          'dayLabel.textbox.enabled = false
        'These are the days within the current month
        ElseIf intDay > 0 And intDay <= DaysInMonth Then
            dayLabel.Caption = intDay  'Displays day number in correct label
            'Added to enable textbox(s) that have a date associated with them
        'These are the labels after the last day of current month, need to add the next month values
        ElseIf intDay > DaysInMonth Then
            'dayTextBox.Enabled = False    'Added to disable/lock text boxes without a date so data cant be entered
            'dayLabel.Visible = False    'Added so months lables that don't display or have a date do not show on grid
             dayLabel.Caption = I - DaysInMonth - intOffSet
             dayLabel.BackColor = vbGreen
        End If
    Next I
End Sub

Public Function getOffset(intYear As Integer, IntMonth As Integer, Optional DayOfWeekStartDate As Long = vbSunday) As Integer
'==================================================================================================
'If your calendar starts on Sunday and the first day of the month is on a Monday
'Then everything is shifted one day so label 2 is day one
'If the first day was Saturday then everything shifts 6 days. So label seven shows 1
'==================================================================================================
    Dim FirstOfMonth As Date
    FirstOfMonth = getFirstOfMonth(intYear, IntMonth)
    getOffset = Weekday(FirstOfMonth, DayOfWeekStartDate) - 1
End Function

Public Function getFirstOfMonth(intYear As Integer, IntMonth As Integer) As Date
    getFirstOfMonth = DateSerial(intYear, IntMonth, 1)
End Function

Public Function getDaysInMonth(FirstDayOfMonth As Date) As Integer
    getDaysInMonth = Day(DateAdd("m", 1, FirstDayOfMonth) - 1)   'Days in month.
End Function

Public Function getLastDayOfMonth(TheYear As Integer, TheMonth As Integer) As Date
  If TheMonth = 12 Then
    TheMonth = 1
    TheYear = TheYear + 1
  Else
    TheMonth = TheMonth + 1
  End If
  getLastDayOfMonth = DateSerial(TheYear, TheMonth, 0)
End Function

Public Function getLastDayOfPreviousMonth(TheYear As Integer, TheMonth As Integer) As Date
  getLastDayOfPreviousMonth = DateSerial(TheYear, TheMonth, 0)
End Function

 

[CharlieT302]

Thanks Majp
I will take a good look at your code as soon as my day eases up. By the way, do you see any "obvious" flaws in the logic from my code?

As I mentioned, Dim L was ignored completely. I inserted a MsgBox into it (a quick method for testing which code is being read), and it would not trigger.