Writing to variable tables - Access 2003

[silverstriip]

I am trying to write to different tables depending on a user selected variable (ie: depending on the year selected, the data would goto the correct table). Am I able to setup my fields in my forms to reference a predefined variable, then from there write the data to the correct table? Any help or suggestions would be greatly appreciated.

[FishVal]

I am trying to write to different tables depending on a user selected variable (ie: depending on the year selected, the data would goto the correct table). Am I able to setup my fields in my forms to reference a predefined variable, then from there write the data to the correct table? Any help or suggestions would be greatly appreciated.

Hi, there.

If the tables have the same structure you may simply change Form.RecordSource after user has entered a year in let us say [ctlYear] control.

example, supposed tables are named: tblYear2001, tblYear2002 etc

Private Sub ctlYear_AfterUpdate()
    Me.RecordSource = "tblYear" & ctlYear
End Sub
 

[silverstriip]

dave,
thanks i'm playing around w/ that right now trying to get it to work for me. this doesnt really apply to your msg, but i thought i would post it since it gives a little more detail:
this is for a database i'm using for a golf outing. i have a table w/ static information (all participant data), then 2 tables set up w/ data that is specific to the year that are linked to the participants (sponsorships and donations). i need a way to have the sponsorship and donations be year specific. whereas the participant database will just keep growing and getting updated w/ any new info.

Hi, there.

If the tables have the same structure you may simply change Form.RecordSource after user has entered a year in let us say [ctlYear] control.

example, supposed tables are named: tblYear2001, tblYear2002 etc

Private Sub ctlYear_AfterUpdate()
    Me.RecordSource = "tblYear" & ctlYear
End Sub