Query and Date Problems

[Metalm]

I have 2 problems. First ill give you a little background

I am making a form for a database, where the table im using was designed so that each column name had the year in it. An example of this would be 2007department, 2007salary, 2006department, 2006salary. Each year someone goes into the tables and adds new fields for the new year.

Now technically they shouldn't of designed the table that way, but I dont think I will be allowed to change the table design.

Currently for the form I am working on, every year someone comes in and changes the control sources of the textboxes to reflect the new year. I need to setup a query so that it will automatically look for the new columns when the current date changes.

Ok so here are my 2 problems

1. I need 5 textboxes to hold the current year and every year after that for a 5 year period. So 2007, 2008, 2009, 2010, 2011. I have it programmed so that on the form load it finds the current year and sets the textboxes text to the year. Yesterday my code was working perfect, but today I noticed that on the form load it loads the textboxes to the dates, but then the boxes go blank. So when you open the form you see the 2007 blink on but then go away.

Dim currentdate As Date
    Dim currentyear() As String
    Dim ItemTotals(5)
    Dim firstyear As String
    currentdate = Date
    
    currentyear = Split(currentdate, "/")

    firstyear = Mid(currentyear(2), Len(currentyear(2)) - 1, 2)
    
    If CDbl(firstyear) < 10 Then
        firstyear = Mid(firstyear, Len(firstyear), 1)
    End If

Item.SetFocus
    Item.Text = 1
    ItemTotals(2) = CDbl(Item.Text)

2. The form is loaded by another form and will be getting an ID from the calling form. I need to figure out how to take that ID and get the field names from a query based on the year values in the textboxes.

This is what ive tried so far but I keep getting type mismatches and stuff on this line - conn.Open CurrentDb(). I tried changing the CurrentDb() to "mydatabase.mdb" the name of the database but it said its locked. They examples I saw for this used an absolute path like "c:\database\mydatabase.mbd", but the file gets moved around so I can used absolutes.

    Dim conn As ADODB.Connection
        
    Set conn = New ADODB.Connection
        
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    
    conn.Open CurrentDb()
    
    Dim rs As Recordset
    
    Set rs = New Recordset
    
    rs.Open "TestTable", conn, adOpenDynamic, adLockPessimistic
    
    rs.MoveFirst
    
    Do Until rs.EOF
        If (rs.Fields("ID_Number") = "100") Then
            ItemBox.SetFocus
            ItemBox.ControlSource = rs.Fields(firstyear & "Item")
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs.ActiveConnection = Nothing
    
    conn.Close