Bad table design needs wierd workaround

[Metalm]

I have a table that keeps track of data on a per year basis.

For some reason the person who orignally developed it set it up so that the field names contained the year and that with each new year they just added more fields

So the table design would look like this...

Table items
---------
2007department number
2007item varchar
2007sales number
2006department number
2006sales number
2006item varchar

There already exist a form where you can look through another table that contains stuff broken up by department. On that form you hit a button and a new form pops up showing information from that items table for the current year thats relavent to the deparment.

At the moment when the next year rolls around they change the query and reassign the values in the textboxes.

Im pretty sure im not going to be to allowed to redesign the table. So my question is, is there a way to set a controlsource to a field name that has a wildcard in it.

so maybe have yeardepartment as the control source instead of 2007 and somehow have it pull the year from the heading (the heading is a textbox saying 2007)

The layout it like this

Textbox: Year (gets year by Year(Date())) so that its the current year

Textbox: Data (needs to change the column reference when the year is changed)
Textbox: Data (needs to change the column reference when the year is changed)

 

[CraigDolphin]

Assuming the user changes the values in txtYear manually, you can use the after update event of your textbox that contains the year to set the control source of the other two textboxes.

something like

Private sub txtYear_AfterUpdate()
if Me.txtYear & "" <> "" then
Me.txtDepartment.Controlsource = Me.txtYear & "Department"
Me.txtItem.Controlsource = Me.txtYear & "Item"
Me.txtSales.Controlsource = Me.txtYear & "Sales"
'etc etc
Else
Msgbox "You must specify a year",vbinformation
End if
End Sub

 

[Metalm]

Assuming the user changes the values in txtYear manually, you can use the after update event of your textbox that contains the year to set the control source of the other two textboxes.

something like

Private sub txtYear_AfterUpdate()
if Me.txtYear & "" <> "" then
Me.txtDepartment.Controlsource = Me.txtYear & "Department"
Me.txtItem.Controlsource = Me.txtYear & "Item"
Me.txtSales.Controlsource = Me.txtYear & "Sales"
'etc etc
Else
Msgbox "You must specify a year",vbinformation
End if
End Sub

Ahh I totally forgot I could set the controlsource programmatically. I think what ill do then is on page load, set the year then do what you suggested. Thanks.

 

[CraigDolphin]

Glad to help