By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,190 Members | 1,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,190 IT Pros & Developers. It's quick & easy.

Referrencing a combobox from a report

100+
P: 105
Hey everyone...

I have a report with 4 labels on it at the top. The first label is lblSunday1, the second is lblSunday2, and so forth...

I have a small form in my Database called frmReports. On this form I have cmbMonths, cmbYears, and cmbDates.

If I select June from cmbMonths and 2008 from cmbYears, all of the Sundays for that month and year are populated into cmbDates (column(1), column(2), column(3), and column(4)).

Here's my trouble...I have in my Rport On Open VBA code this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. lblSunday1.Caption = Forms!frmReports!cmbDates.Column(1)
  4. lblSunday2.Caption = Forms!frmReports!cmbDates.Column(2)
  5. lblSunday3.Caption = Forms!frmReports!cmbDates.Column(3)
  6. lblSunday4.Caption = Forms!frmReports!cmbDates.Column(4)
  7.  
  8. DoCmd.Maximize
  9.  
  10. End Sub 
However, I get an error when I try to open the report filtering to those specific cmbDates values.

Error: DataType Mismatch

What am I doing wrong?

Thanks!
May 13 '08 #1
Share this Question
Share on Google+
1 Reply


100+
P: 105
Actually I got it working...

I set up invisible text boxes on my frmReports to hold the Sunday values and then just threw them into the report



Expand|Select|Wrap|Line Numbers
  1. Public Function SetTextBoxDutiesDates()
  2.  
  3. Dim TargetDate As Date
  4.  
  5. TargetDate = CDate(Forms!frmReports!cmbMonths & "/1/" & Forms!frmReports!cmbYears)
  6.  
  7. 'loop through days of month and populate txt1st
  8. For i = 1 To GetTextBoxDutiesMonthDays
  9.     If Weekday(TargetDate) = vbSunday Then _
  10.         Forms!frmReports!txt1st.Value = TargetDate
  11.         TargetDate = DateAdd("d", 1, TargetDate)
  12. Next i
  13.  
  14. 'loop through days of month and populate txt2nd
  15. For i = 1 To GetTextBoxDutiesMonthDays
  16.     If Weekday(TargetDate) = vbSunday Then _
  17.         Forms!frmReports!txt2nd.Value = TargetDate + 0
  18.         TargetDate = DateAdd("d", 1, TargetDate)
  19. Next i
  20.  
  21. 'loop through days of month and populate txt3rd
  22. For i = 1 To GetTextBoxDutiesMonthDays
  23.     If Weekday(TargetDate) = vbSunday Then _
  24.         Forms!frmReports!txt3rd.Value = TargetDate + 0
  25.         TargetDate = DateAdd("d", 1, TargetDate)
  26. Next i
  27.  
  28. 'loop through days of month and populate txt4th
  29. For i = 1 To GetTextBoxDutiesMonthDays
  30.     If Weekday(TargetDate) = vbSunday Then _
  31.         Forms!frmReports!txt4th.Value = TargetDate + 0
  32.         TargetDate = DateAdd("d", 1, TargetDate)
  33. Next i
  34.  
  35. 'loop through days of month and populate txt5th
  36. For i = 1 To GetTextBoxDutiesMonthDays
  37.     If Weekday(TargetDate) = vbSunday Then _
  38.         Forms!frmReports!txt5th.Value = TargetDate + 0
  39.         TargetDate = DateAdd("d", 1, TargetDate)
  40. Next i
  41.  
  42. End Function
  43.  
  44.  
  45.  
  46.  
  47.  
  48. Public Function GetTextBoxDutiesMonthDays()
  49.  
  50. TargetDate = CDate(Forms!frmReports!cmbMonths & "/1/" & Forms!frmReports!cmbYears)
  51.  
  52. 'Exactly 1 week from TargetDate
  53. TargetDate = DateAdd("ww", 1, TargetDate)
  54.  
  55. 'Subtract 1 day
  56. TargetDate = DateAdd("d", -1, TargetDate)
  57.  
  58. GetTextBoxDutiesMonthDays = Day(TargetDate)
  59.  
  60. End Function
  61.  
  62.  
  63.  
  64. Private Sub Report_Open(Cancel As Integer)
  65.  
  66. lblSunday1.Caption = Forms!frmReports!txt1st.Value
  67. lblSunday2.Caption = Forms!frmReports!txt2nd.Value
  68. lblSunday3.Caption = Forms!frmReports!txt3rd.Value
  69. lblSunday4.Caption = Forms!frmReports!txt4th.Value
  70. lblSunday5.Caption = Forms!frmReports!txt5th.Value
  71.  
  72. DoCmd.Maximize
  73.  
  74. End Sub
May 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.