SQL saved as query? 1

[solo7]

I am using SQL to set the record source of a report based on 2 parameters chosen by the user.
My user selects a table to look in, and then specifies the dates to search between to perform the query. My report works ok for a specified table but as soon as I try and selct a different table the text boxes error out as they are still pointing to the original table, despite the reports record source being correct. For example my reports source is pointing to Table3, and works great as all my text boxes are pointing to Table3 as well - but as soon as I manipulate the Reports recordsource to point to Table2 the text boxes remain pointing to Table3.

So my question is can I change the data source of all my text boxes on my report to reflect the changed SQL in the recordsource of the report. Or would it be feasable to save my SQL as a Query and base my Report on that query (if so how do I save a SQL statement as a query)

solo7
hope I explained it well enough for someone to understand it!

 

[Remou]

Hi
This is probably not what you mean, but here goes anyway. If you click the three little dots to the right of Record Source, just beside the down arrow, you will invoke the query builder. You can then chose Save As from the file menu.

 

[solo7]

Thanks for the reply Remou but It's not what I need. I'm trying to use 1 report to display data from multiple tables and it's the user parameters that decide which table the report looks at. I can sucessfully set the reports recordsource to the correct SQL but it's the text boxes that are my problem, I don't know how to point their recordsource's to reflect the changes I have made in the reports recordsource.

Solo7

 

[Remou]

Hi
Sorry for being stupid, but (for example) you have a text box on the report that is bound to CustID for Report1, whereas you want it bound to SuppID for Report2? [Ponder]

 

[RivetHed]

I assume that you are using an event procedure to change the reports recordsource so what about adding something like:

select case mycombobox 'or whatever the user is using to select the table

case is = "table1"

textbox1.controlsource = something
textbox2.controlsource = something else

case is = "table2"

etc.....

 

[solo7]

Ok,
imagine you have set up a report with a wizard. Basing the report on Table3. It's got 2 text boxes both pointing to say a Name field (TXT_Tbl3Forename and TXT_Table3Surname) - you open the form and the correct data is displayed as you would expect.

Now what I have done is to allow the user to select from which table the data comes from. So I have re-set the report's recordsource to reflect the user's choice of say Table2. So in the report's recordsource the SQL correctly shows the user has selected Table2 - However the text boxes still point to Table3 - I need to set the text boxes that are already there to point to TXT_Table2Forename and TXT_Table2Surname.

Hope that makes sense. But I would also consider saving the SQL as a query and basing a report on it if I could find out how to save an SQL statemant as a Query

solo7

 

[RivetHed]

Are the field names always going to behave like that i.e. only one characters difference depending on which table they come from or is this just an example?

Also what process is the user following to open this report? do they select the table from a drop down and then hit a button to generate the report?

 

[solo7]

Yes.
I was hoping to use this one form instead of 7 if I used seperate queries. It is just the Table number that is different I have TblMod1 - TblMod7.

solo7

 

[solo7]

Sorry rivethead, pressed submit a quickly

The user selects a Table to query from a combobox on a form, and at the same time a range of dates to query between. These 3 parameters are then joined into an SQL statement. I open a report in design mode at runtime, set the recordsource of the report to the SQL, and then preview the report.

Well that's the theory ...:>)

solo7

 

[RivetHed]

ok maybe try something like this on the onclick event of a button that you use to execute the report:

select case right(mycombobox,1) 'find out which table you're using

case is = 1

myreport.recordsource = 'insert your SQL here
mytextbox1.controlsource = myfieldname
mytextbox2.control source = myotherfieldname

case is = 2

myreport.recordsource = 'insert your SQL here
mytextbox1.controlsource = myfieldname
mytextbox2.control source = myotherfieldname

'place all other cases here

end select

if however your field names are all standardized across your tables with the only difference being a number you could maybe try something like

mytextbox.controlsource = "TXT_Table" & right(mycombobox,1) & "Forename"

this will generate a field name for your forename text box based on the standard field name and inserting the number from the end of your table name in the middle.

this code has just been typed in here rather than into a code window so the syntax will probably need tweaking but this should give you the basic principle you should follow.

 

[solo7]

Rivethead,
I think that may well do it. Will try when I get home - Many thanks in advance ...

solo7

 

[RivetHed]

no problem, post back if you can't get it to work

 

[solo7]

RivetHed
Couldn't help but it try asap. The code seems logical to me but I'm getting an error 'Property is not availiable in design view'
The field name of each textbox is in the .Tag field, so the field Fld_Mod3_TestTime is the name and is in the Tag. All my fields are as the example, I need to manipulate the number 3 to change between 1 through 7

'----------   StrMod holds the table number to query

'---------- set the source of the reports SQL
'
DoCmd.OpenReport "Rpt_ModuleTest_ByDate", acViewDesign

'########  SQL CODE HERE (StrSQL)   ######


'---------- assign the SQL as the reports record source
'
Reports!Rpt_ModuleTest_ByDate.RecordSource = StrSQL

 
For Each Ctl In Reports!Rpt_ModuleTest_ByDate
    If Left(Ctl, 8) = "Fld_Mod3" Then
        Ctl.ControlSource = Left(Ctl.Tag, 7) & StrMod & Right(Ctl.Tag, (Len(Ctl.Tag) - 7))
    End If
Next Ctl

DoCmd.Save acReport, "Rpt_ModuleTest_ByDate"
DoCmd.Close

'---------- print or preview?
'
If Combo1a.Value = 0 Then
    stDocName = "Rpt_ModuleTest_ByDate"
    DoCmd.OpenReport stDocName, acPreview
Else
    stDocName = "Rpt_ModuleTest_ByDate"
    DoCmd.OpenReport stDocName, acPrint
End If

The answer feels close on this one!

solo7

 

[solo7]

I think I have this one now.

I have taken out the setting of the controlsource of each textbox and placed it in the Reports 'OnOpen' event. Referencing the combo which selects the different tables on the open Form - I would have liked a more clinical solution but this seem to work fine and I'm a happy bunny !!!

Thanks for the clues RivetHed, much appreciated.

solo7