Set Form's Control Source

[LadyDi]

Is there a way to set the control source for an entire form through VBA? I have a database that was set up just as an archive file, not intending to have any forms or reports in it. It was simply to house tables containing data from previous years. My main database just copies the table over to the archive file and adds the year to the table name. I have just been told that the users would like a form and a series of reports set up in this database. I would like to set the main form up so that before it will allow them to do anything, they have to enter the year of the data they are looking for. After the year has been entered, I would like to adjust the control source of the form to reflect the appropriate table. In other words, when a user opens the archived database, he or she will see a field for the year an nothing else. Once they enter the year they are looking for, the form will populate with data from the corresponding table (the table names look like this - tblPM_Completed_2013). Is this possible?

 

[missinglinq]

A Form doesn't have a Control Source; a Control has a Control Source! A Form has a RecordSource! To set it, through code, you'd do something like this:

Private Sub Form_Load()
 Me.RecordSource = NameOfAppropriateTable
End Sub

Linq ;0)>

 

[LadyDi]

Okay, thanks so much for the information. I appreciate it.

 

[grahamvb]

Try something like this; expanding on missinglinq's post.

This blocks users from accessing the table

Private Sub Form_Load()
    Me.RecordSource = ""
End Sub

After the user enters a date in an unbound text box, named txtDate, formatted as date, this code will allow the form to connect to the desired table.

Private Sub txtDate_AfterUpdate
    Me.RecordSource = "tblPM_Completed_2013"
End Sub

 

[redalert]

Why not put all of the archived tables into one table with a field to indicate the year and then you do not have the problem of specifying the RecordSource.

Have an unbound ComboBox that has a row source like this for example:

cboYears.RowSource="SELECT YearNumber FROM tbl_Archive
GROUP BY YearNumber ORDER BY YearNumber"

Have the [YearNumber] field in the forms Record Source query reference the cboYears ComboBox and requery the form in the AfterUpdate event of the ComboBox.

All you will need to develop then is an import routine or even just a simple query to get the archiived data into the table each year.

 

[gemma-the-husky]

two possibilities really

recordsource, but sometimes sourceobject

a form has a "recordsource", which changes the underlying query

alternatively, say you have a form, with a subform -and the subform can have alternative displays - then you can change the subforms "sourceobject"

 

[grahamvb]

redalert, that is some sharp thinking!