Record source

[rikklaney1]

I have a report that shows on a form and has this as the recordset

SELECT [ClnTbl-RS5].SCdate, [ClnTbl-RS5].[act1-1], [ClnTbl-RS5].[act2-1], [ClnTbl-RS5].[act3-1], [ClnTbl-RS5].[act4-1], [ClnTbl-RS5].[Cletime-1], [ClnTbl-RS5].[Tigtime-1], [ClnTbl-RS5].[Tottime-1], [ClnTbl-RS5].[Operator-1] FROM [ClnTbl-RS5] WHERE ((([ClnTbl-RS5].SCdate)=DateAdd('d',-22,Date())));

I can put that in the on open event of a form like this

'Me.Report1.Report.RecordSource = "SELECT [ClnTbl-RS5].SCdate, [ClnTbl-RS5].[act1-1], [ClnTbl-RS5].[act2-1], [ClnTbl-RS5].[act3-1], [ClnTbl-RS5].[act4-1], [ClnTbl-RS5].[Cletime-1], [ClnTbl-RS5].[Tigtime-1], [ClnTbl-RS5].[Tottime-1], [ClnTbl-RS5].[Operator-1] FROM [ClnTbl-RS5] WHERE ((([ClnTbl-RS5].SCdate)=DateAdd('d',-22,Date())));"


now my question is if I have a text box with a value of something other than ClnTbl-Rs5, can I use the same report but have the vba change depending on my text box? Like this

'Me.Report1.Report.RecordSource = "SELECT [Me.Tablename.value].SCdate, [Me.Tablename.value].[act1-1], [Me.Tablename.value].[act2-1], [Me.Tablename.value].[act3-1], etc...

 

[jdraw]

In theory you could. But in practical terms it is unlikely (rare) that you would have several tables with the same field names.

It might be easier to answer or offer advice if you described your business situation in plain English. A sample of what you/your company does, what the reports would be about etc.

 

[rikklaney1]

I have five tables with the same structure but different records. I don't know why but I inherited it this way. Each table represents a particular work area. Although now that you ask the question I'm wondering why they didn't just make one table.

 

[rikklaney1]

Looks like it was set up this way so there could only be one record for each day as the ScDate field is set to no duplicates.

 

[jdraw]

OK, so you have what you have.

You could have a form in which you set up some options to run your specific reports.

The recordsource for the report could be held in a combo or some other mechanism (table).
When you select the options for a report, the parameters for the report would be applied based on your needs and when you click the " Create the Report for area X" button, the report and parameters are automatic.

Maybe you have something like that now.

More info at Martin Green's site re Dynamic Reports.

 

[rikklaney1]

Thanks. I think that dynamic report site looks like it will be what I need. I'm actually considering just rebuilding the table. I guess the thought at the time was it would be simple to just add another table when you needed to expand so there are clntbl-rs1 thru 5 and you could just copy the table structure and add 6,7,8 etc... Bad thing is I'm learning by taking this thing apart so any bad ideas in there I'm learning. lol

 

[jdraw]

Here are some links that will help you.

Database series of 8 free videos by Dr. Daniel Soper starts here.
I recommend 1,2 and 4 as a start.

Programming VBA by Steve Bishop (70 free videos)

For hands on 30-45 minute tutorial to work through, try one of the examples here You have to do some work, but you'll learn a technique that can be used with any database.

Good luck.