Check boxes issue

[Naveen Marapaka]

Hi dears,



Expecting a great help here.

I have a Form named Form1.

2 text Fields are Date1 & Date2 as we can pick the date from the datepicker.

now I have 31 check boxes named D1,D2,D3,D4,D5.........................D30,D31

what i want is , when i select Date1 & Date2, as an example 01/Dec/12 & 15/Dec/12 (i.e with in the month)

I want correspondingly Checkboxes D1 upto D15 to be checked automatically.


how can i do it ?

[June7]

Try something like:

Dim intStart As Integer, intEnd As Integer, i As Integer
intStart = Day(Me.Date1)
intEnd = Day(Me.Date2)
For i = intStart To intEnd
Me.Controls("D" & i) = True
Next

[Naveen Marapaka]

thank you so much dear.
its really working.

thanks a million.

[Naveen Marapaka]

Dear further to my solved issue, just adding a small problem.

now i have Total 2 sets of Check boxes for 2 months

as D1,D2,D3...........D30,D31 & DD1,DD2,DD3..................DD30,DD31.

and it is working but it is not accurate, like no of days in a month issue is there.

as checkedboxes are 31 in each set. what i want is to select according according to the no of days in selected month.

Example : 01/Feb/2012 to 10/Mar/2012

It should be 28 checkboxes only selected in First set of check boxes and 10 checkboxes in second of checkboxes.

like D1,D2.............D28 & DD1,DD2.....D10


currently i have below code but i know it is not correct to get this, pls help me
#If Format(Date1 "mm") & Format(Date1, "yyyy") = Format(Date2, "mm") & Format(Date2, "yyyy") Then
intStart = Format(Date1, "dd")
intEnd = Format(Date2, "dd")

For i = intStart To intEnd

Me.Controls("D" & i) = True

Next

Else
intStart = Format(Date1, "dd")
intEnd = Format(Date2, "dd")

For i = intStart To 31

Me.Controls("DD" & i) = True

Next

For i = 1 To intEnd

Me.Controls("DD" & i) = True

Next


End If#

[June7]

What is this database for? Why do you have two sets of checkboxes? Data structure does not seem optimized and normalized.

You might find discussion in this thread relevant https://www.accessforums.net/databas...ses-18459.html

intStart = Day(Date1)
intEnd = IIf(Month(Date1) = Month(Date2), Day(Date2), DateSerial(Year(Date1), Month(Date1) + 1, 1), -1)
For i = intStart To intEnd
    Me.Controls("D" & i) = True
Next
If Month(Date1) <> Month(Date2) Then
    intStart = 1
    intEnd = Day(Date2)
    For i = 1 To intEnd
        Me.Controls("DD" & i) = True
    Next
End If

Do you need to restrict user to a 2-month data range?


PS: I deleted duplicate thread

[Naveen Marapaka]

Dear let me give clear details.

Mainform Name: Mainpage.
Subform Name: DaysofLeave (fields are Name, Intranet_no, Start_Date, Join_Date)

Command Button Named Generate Leave Report.


In Report : I Designed our format to Annual Leaves Form which is like . 2 sets of Check boxes for 2 months
Name, & other details.

so I entered the code in Report only on load Event.

If Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "mm") & Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "yyyy") = Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "mm") & Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "yyyy") Then
 intStart = Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "dd")
 intEnd = Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "dd")
 
   For i = intStart To intEnd
 
  Me.Controls("D" & i) = True
 
 Next
 
 Else
  intStart = Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "dd")
  intEnd = Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "dd")
 
  For i = intStart To 31
 
  Me.Controls("D" & i) = True
 
 Next
 
 For i = 1 To intEnd
 
  Me.Controls("DD" & i) = True
 
 Next
 
 
 End If
End Sub

.

Report is generating with selecting checkboxes but as i mentioned my problem, no of days in month issue.

so now i applied taking your code.

intStart = DAY(Forms!Mainpage.DaysofLeave.Form.Start_Date)
  intEnd = IIf(Month(Forms!Mainpage.DaysofLeave.Form.Start_Date) = Month(Forms!Mainpage.DaysofLeave.Form.Join_Date), DAY(Forms!Mainpage.DaysofLeave.Form.Join_Date), DateSerial(Year(Forms!Mainpage.DaysofLeave.Form.Start_Date), Month(Forms!Mainpage.DaysofLeave.Form.Start_Date) + 1, 1), -1)
  For i = intStart To intEnd
    Me.Controls("D" & i) = True
  Next
  If Month(Forms!Mainpage.DaysofLeave.Form.Start_Date) <> Month(Forms!Mainpage.DaysofLeave.Form.Join_Date) Then
    intStart = 1
    intEnd = DAY(Forms!Mainpage.DaysofLeave.Form.Join_Date)
    For i = 1 To intEnd
        Me.Controls("DD" & i) = True
    Next
   End If

.


Errors i am getting:

1. IIF - wrong number of arguments or invalid property assignment.
2.Dateserial - Argument Not optional.

[June7]

Sorry, remove the comma in front of -1.

[Naveen Marapaka]

I did the same dear.

Error: Object does not support this property or method

intStart = DAY(Forms!Mainpage.DaysofLeave.Form.Start_Date)


pls advise.

[June7]

DAY() is a valid function although VBA should automatically fix the uppercase letters to Day(). I think VBA doesn't like the syntax used to reference the subform fields/controls. Not sure why. At this point, I would have to review the db. If you want to provide, follow instructions at bottom of my post.

[Naveen Marapaka]

Now I just tried like this below and it is working when i select dates with in same month.

but it is showing Error as Overflow when i select 2 differnt months.

 intStart = Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "dd")
  intEnd = IIf(Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "mm") = Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "mm"), Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "dd"), DateSerial(Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "yyyy"), Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "mm") + 1, 1) - 1)
  For i = intStart To intEnd 
    Me.Controls("D" & i) = True
  Next
  If Format(Forms!Mainpage.DaysofLeave.Form.Start_Date, "mm") <> Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "mm") Then
    intStart = 1
    intEnd = Format(Forms!Mainpage.DaysofLeave.Form.Join_Date, "dd")
    For i = 1 To intEnd - 1 
        Me.Controls("DD" & i) = True
    Next
   
  End If

pls advise.

[Naveen Marapaka]

Vacation Plans.zipDear I am attaching my DB here pls advise solution

[June7]

My misunderstanding of what you wanted. Can't use code to set report textbox values. The data must be saved to table then report uses the table as a RecordSource.

Why do this for a leave request? The leave request form my employer uses just requires leave begin and end dates and the number of days in that range applied to leave. Then on time sheet we report daily hours.

Did you review the thread I referenced in earlier post?

[Naveen Marapaka]

Hi,
I dont require to store values in table unnecessarily. I am storing values of starting and end date in table which i require.

so in report i am pulling the same info from form directly + checkboxes checked for selected dates,

which is standard format of leave request.

I am able to pull all what i want in report including check boxes selected, but only problem is first set of check boxes should be upto number of days of starting date.

like i said, if leave is Feb 10th to March 10 , then 28 check boxes only to be selected in first set which i am not getting any idea how to do it.

what i am getting now is feb month also 31checkboxes selected and till10th march in 2nd set of check boxes.


any more ideas to solve it pls.

[June7]

I repeat, can't use code to change the data in a report. Can't set values of controls, that includes checkboxes. The data must come from RecordSource. Sorry, don't have any ideas on how to accomplish what you want other than something like the examples in the thread I referenced earlier.

[Naveen Marapaka]

Hi No issues, thank you for your reply.