Setting Record Source in Report_Open Event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with syntax. (I am working with legacy data in static tables,
so my options are limited).

I have an open form "frmA" with a field "Case". I want my report's record
source dependent on the first 4 characters of "Case". I have tbl2000,
tbl2001 and tbl2002 as possible record sources. I believe I need to add code
to the Report_Open event.

In psuedo code, this is what I want to do:

If frmA.Case is like "2000*" Then
Me.Recordsource = tbl2000
else
If frmA.Case is like "2001*" Then
Me.Recordsource = tbl2001
else Me.Recordsource = tbl2002
End If
End If

Thank you for your help!!
 
I need help with syntax. (I am working with legacy data in static tables,
so my options are limited).

I have an open form "frmA" with a field "Case". I want my report's record
source dependent on the first 4 characters of "Case". I have tbl2000,
tbl2001 and tbl2002 as possible record sources. I believe I need to add code
to the Report_Open event.

In psuedo code, this is what I want to do:

If frmA.Case is like "2000*" Then
Me.Recordsource = tbl2000
else
If frmA.Case is like "2001*" Then
Me.Recordsource = tbl2001
else Me.Recordsource = tbl2002
End If
End If

Thank you for your help!!
Click to expand...

1) It appears to me that your database is not properly designed.
Having a table for each year is not the way to go, just as having a
table for each week of the year would not be efficient.

One table with a date field included should be all you need.
Access is quite capable of filtering records and returning just the
data for the year wanted.

2) Case is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

3) To set a recordsource in a report's Open event you could use
something like the following:

If forms!FormA!ControlName like "2000*" Then
Me.Recordsource = "tbl2000"
ElseIf forms!FormA!ControlName like "2001*" Then
Me.Recordsource = "tbl2001"
Else
Me.Recordsource = "tbl2002"
End If

FormA MUST be open when this report event is run.

But do change your database design.
 
Thanks, Fred. I'll give this a try.

I know the database is not properly designed, but it is out of my control.
I stated at the beginning that I was accessing static data legacy system.
There are static yearly tables stretching back to 1997, each with hundreds of
thousands of records. That is why I decided not to import and combine them.
 
Back
Top