Dynamically add table columns to a form 1

[1DMF]

Hi,

Is it possible to dynamically add table columns to a form in datasheet view?

I need to display the content of an imported XLS file so obviously cannot bind a form directly to the table as the table column names will change depending on the imported XLS file.

I've tried to use the table as a record source with a query using tablename.*

but as no fields are placed on the form , nothing is displayed.

How do I add the fields to the form dynamically?

Thanks,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

You can use an unbound form with a combo box and subform control.
[tt][blue]
Combo box:
Name: cboTables
Row Source: SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "msys*")
AND ((msysObjects.Type)=1))
ORDER BY msysObjects.Name;
After Update: [Event Procedure]

Subform Control:
Name: Child2
Source Object:
[/blue][/tt]

Set the After Update event of the combo box to:

Private Sub cboTables_AfterUpdate()
    Me.Child2.SourceObject = "Table." & Me.cboTables
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

You're a genius , thank you so much.

all i needed was...

    Me.Import_Subform.SourceObject = "Table.Provider_Import"

Works like a dream!!!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

Thanks for the star. "genius"? I doubt that. Maybe persistent.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

You're welcome, it was well earned.

You saved me a lot of work with extra 'holding' tables and data conversion with a bound subform.

Ok, there is a downside, I had set the subform 'format' to not show dividing lines, record selector etc.

But as soon as you change the objectsource , they all appear.

I'm assuming I can use code to set these attributes once the source has been changed?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[dhookom]

I'm not sure if you can change these properties when using a table as the Source Object. You can try and report back ;-)

Duane
Hook'D on Access
MS Access MVP