M
Martin
Hello,
I am designing a holiday chart and have all my dates (2 years plus) in the
rows and my 62 members of staff as columns (as I could only have 255 columns
so not enough for 2 years worth of days)
I have a table of all staff and I want a form to be able to filter on the
particular team that current user belongs to. I have this all working but
what I want to do is assign currently blans labels and combo boxes on the
form based on a query. So for instance, if a user works in a particular team
then I only want the lables and combo boxes assigned to the appropriate team
members using the control source property. Here is my code so far:
Private Sub Label350_Click()
Dim MyControl As ComboBox
Dim MyLabel As Label
Dim DB As DAO.Database
Dim Rec As DAO.Recordset
DoCmd.ShowToolbar "Web", acToolbarNo
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryHolidayChartCST02", acNormal
DoCmd.SetWarnings True
Set DB = CurrentDb
Set Rec = DB.OpenRecordset("Select Rank From QryHolidayChartCST Order By
Rank")
While Not Rec.EOF
MyControl = "DD" & Rec!Rank
MyLabel = "DD" & Rec!Rank & "_L"
MyControl.ControlSource = DLookup("Username", "QryHolidayChartCST", "[Rank]
= Rank")
MyLabel.Caption = DLookup("Name", "QryHolidayChartCST", "[Rank] = Rank")
MyControl.Visible = True
Rec.MoveNext
Wend
End Sub
The code stops at the line "MyControl = "DD" & Rec!Rank" stating that the
object variable is not set.
Can anyone advise where I am going wrong?
Thanks in advance
Martin
I am designing a holiday chart and have all my dates (2 years plus) in the
rows and my 62 members of staff as columns (as I could only have 255 columns
so not enough for 2 years worth of days)
I have a table of all staff and I want a form to be able to filter on the
particular team that current user belongs to. I have this all working but
what I want to do is assign currently blans labels and combo boxes on the
form based on a query. So for instance, if a user works in a particular team
then I only want the lables and combo boxes assigned to the appropriate team
members using the control source property. Here is my code so far:
Private Sub Label350_Click()
Dim MyControl As ComboBox
Dim MyLabel As Label
Dim DB As DAO.Database
Dim Rec As DAO.Recordset
DoCmd.ShowToolbar "Web", acToolbarNo
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryHolidayChartCST02", acNormal
DoCmd.SetWarnings True
Set DB = CurrentDb
Set Rec = DB.OpenRecordset("Select Rank From QryHolidayChartCST Order By
Rank")
While Not Rec.EOF
MyControl = "DD" & Rec!Rank
MyLabel = "DD" & Rec!Rank & "_L"
MyControl.ControlSource = DLookup("Username", "QryHolidayChartCST", "[Rank]
= Rank")
MyLabel.Caption = DLookup("Name", "QryHolidayChartCST", "[Rank] = Rank")
MyControl.Visible = True
Rec.MoveNext
Wend
End Sub
The code stops at the line "MyControl = "DD" & Rec!Rank" stating that the
object variable is not set.
Can anyone advise where I am going wrong?
Thanks in advance
Martin