I have a form in my database that I want to refer to in VBA code to limit the records it returns.
I have a calendar form that pulls data from a SchedulingTable called ROSchedule, in this table there is a BUID (Business Unti ID) column.
The calendar is launched from a form that has a drop down list where the user can select a particular business Unit, this form is called BusinessUnit.
All of the queries filter out the records specific to the business unit in the form BusinessUnit
Sample Query SELECT RoScheduling.ArrivedDate, RoScheduling.ABSRO, RoScheduling.SchedulingHours, RoScheduling.Customer, RoScheduling.BUID FROM RoScheduling WHERE (((RoScheduling.BUID)=[Forms]![BusinessUnit]![BUID]));
but when the calendar loads the VB select Where doesn't seem to filter out the records.
Here is the VB code that works without the BUID filter. I need to be able to have the VB code filter out the records the same way the Sample Query above does. I only want the records that match the BUID in the BusinessUnit form.
Any help with the VBA code would be greatly appreciated, I'm not that good with the VBA side of Access so please don't make any assumptions that I know what I'm doing with it.
Thanks
Randy
Private Sub cbo_Month_Change()
'Month has changed so we have to recalculate the form
Dim cnt As Long
'First step is to find out what the month is composed of
Dim DaysInMonth As Long
DaysInMonth = DateDiff("d", cbo_Month, DateAdd("m", 1, cbo_Month))
Dim FirstDay As Long
FirstDay = Weekday(cbo_Month, vbMonday)
'Hide the first few forms that dont count
For cnt = 1 To 6
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay > 1 Then
For cnt = 1 To FirstDay - 1
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If
'Hide the last few forms that dont count
For cnt = 28 To 37
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay < 7 Or DaysInMonth < 31 Then
For cnt = FirstDay + DaysInMonth To 37
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If
Dim frm As Form
Dim DayOfMonth As Long
'Now we need to set the record source for each day.....
For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
DayOfMonth = (cnt - FirstDay) + 1
Set frm = Forms!frm_Calendar.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT ArrivedDate,ABSRO,SchedulingHours,Customer FROM ROScheduling WHERE ArrivedDate = #" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_Month, "mmm/yyyy") & "#"
frm.lbl_Date.Caption = CStr(DayOfMonth)
frm.Controls("Date").DefaultValue = "#" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_Month, "mmm/yyyy") & "#"
Me.Controls("SF" & CStr(cnt)).Requery
Next
End Sub