Assigning control sources via code

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
 
D

Dorian

Rec is a recordset. You need the fields of the recordset:
MyControl = "DD" & Rec.fields("Rank").Value and not:
MyControl = "DD" & Rec!Rank
Click to expand...
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


Martin said:
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
Click to expand...
 
D

Dorian

One other thing, if Rank can be null you have to test for that, e.g.
NZ(Rec.fields("Rank").Value,0)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Top