Basing a form on a temp table

[LeanneGodney]

Hi Everyone,

I would like to base a form on a temp table.

I have a form that is displaying the results of a grouped query in the form of a pivot table. This is working like a dream. Only problem is that the underlying SQL changes, depending on what report the user asks for.

Once the person has run the report and close it I no longer need the table. Therefore, I was thinking of having some kind of stored procedure analyse what the user wants, and create a temp table showing that data. It's doing this beatifully through VBA. But I can't get the form to display the TEMP TABLE data.

Does anyone know how to base a form on a temp table?

Thanks,
Leanne

 

[lupins46]

You can base a bound form on a temp table so long as the fields in the temp table are always the same.

If they are not the same then you will probably have to use an unbound form and fill the labels and textboxes with values in code.

 

[LeanneGodney]

Hi Lupins,

Yes, the column names will be the same. I just need to filter out certain info, hence the need for a temp table visible to only the user...

Leanne

ps. How do I base a form on it? I tried putting the form name #TempTable as the recordsource, but it didn't want to do it for me...

 

[lupins46]

SOrry, i am missing the point here...
If you have a table that will always have the same columns why can't you create a form. Replacing the table with a new one with the same name and columns isn't going to affect anything.

More info please...

 

[cmmrfrds]

As Lupins pointed out as long as the column names don't change the SQL can be done dynamically in vba code and sent to sql server as the record source of the Form. There probably is no need for a temp table as such.

In the OnOpen Event
Dim mySQL as String
mySQL = "select ........"
Me.RecordSource = mySQL