Need form to display a dynamically generated table

[djpingpong]

Hi,

I'm running Access 2002
I have created a form, which allows the end user to select a YEAR value (ie: 2006)
The first thing this form does is create a dynamic temp table (let's name it: tblTemp) which will pull in data according to the year selected.... so, it drops tblTemp, then re-creates tblTemp with new column names and gets populated again
One of the reason why tblTemp needs to be recreated each time is because of the Earnings history data. So if user selects 2006, then there'll be an earnings history from 2001-2006 and the field names will be named 2001_earnings, 2002_earnings,etc..

So after the OnLoad() process is completed creating this table, the form displays a datasheet of tblTmp. My problem is the form keeps the same field names as the first run. Meaning, if I ran 2006 the first time, i'd have 2006_earnings... 2001_earnings as field names... but the next time I run it and i select 2005, the field name "2006_earnings" still shows in the form and it's displaying #NAME as an error

I have tried the following, but it still didn't work for me:

me.recordsource = "SELECT * FROM tblTmp"
me.requery
me.refresh

what else can I do? I didn't think it would cause this much headache

 

[Remou]

I think the best bet would be to name the fields in the temp table F1 to Fn and then re-name the labels on the form.

 

[djpingpong]

not to sound terrible...
but is there a better suggestion?

 

[Remou]

Not really, you will have to rename one thing or another. You could set the control sources of the textboxes to the new field names.

 

[TheAceMan1]

How are ya djpingpong . . .

Agree with [blue]Remou[/blue]. Your problem is [blue] not so much the table as it is the form![/blue]

Your real problem is: [blue]how does the form account for increasing field count in tblTemp . . . compared to the fields already alloted in the form?[/blue]

This dictates . . . change the field count in the table and [blue]change the same count in the form accordingly![/blue]

[blue]Ya Think![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[djpingpong]

how does the form account for increasing field count in tblTemp . . . compared to the fields already alloted in the form?

Good question.. that is one of my problems...
but I figured that if I set the form's recordsource = "SELECT * FROM tlbTmp".. shouldn't it display the entire table content no matter what.... am I wrong?

cheers

 

[LonnieJohnson]

I played around with your concept. What I did was to create a subform on my form with your temp table as the objects source.

I then put two buttons on my form. Each one will run a query to over write the temp table with a different set of fields.

In my buttons code I erase the subform object and replace it with the temp table name.

Works perfect.

Here is my code...

Private Sub Command0_Click() 'many fields on my temp table

    DoCmd.SetWarnings False 'turn off warnings
        Me.Child4.SourceObject = "" 'remove source of subform
            DoCmd.OpenQuery "makeTempTable1" 'running a query to overwrite _
                                                temp table with many fields
        Me.Child4.SourceObject = "Table.myTemp" 'reset object to temp table
    DoCmd.SetWarnings True 'turn warnings back on
    
End Sub

Private Sub Command1_Click() 'a few fields in my temp table
    
    DoCmd.SetWarnings False 'turn off warnings
        Me.Child4.SourceObject = "" 'remove source of subform
            DoCmd.OpenQuery "makeTempTable2" 'running a query to overwrite _
                                                temp table with a few fields
        Me.Child4.SourceObject = "Table.myTemp" 'reset object to temp table
    DoCmd.SetWarnings True 'turn warnings back on
    
End Sub

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[TheAceMan1]

[blue]I figured that if I set the form's recordsource = "SELECT * FROM tlbTmp".. shouldn't it display the entire table content no matter what.... am I wrong?[/blue]

Sorry to say . . . Yes . . . your wrong!

[blue]A recordsource simply provides the fields you can display in the form.[/blue] Although accessible as if they were, all fields do not have to be displayed. In fact, you only have to display what you need from that recordsource! As an example, in all my forms you never see the PrimaryKey field, although its included in the recordsource. There's no textbox for it. But I can access the PK as if a textbox existed!

Now consider you delima, [purple]the recordsource can return a dynamic number of fields![/purple] Nothing you can do as far [blue]as what you want too see on the form, other than remove/delete & rename textboxes accordingly![/blue]

[blue]A forms RecordSource does not automatically create textboxes for view. Whats in view (via textboxes) is up to you . . . the programmer![/blue]

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[djpingpong]

thank you LonnieJohnson ... that worked...
thanks all for helping