Dynamically query a table to OpenForm with an Access Database!!!

[melaine]

I have to work with an existing database with 25 tables.
I want the end User to be able to modify rows within the tables (add/amend/delete) via a Form. I do not want to create a query for the form, I want to dynamically create a query to access the table to then display the table within an editable form. Can this work? Anybody got any ideas of how I could do this?

I have tried the following syntax using VBA (access2000), but this does not work. I get no response! I am also confused with the building of my SQL string??? I am going mad here...
Any h elp will be much appreciated!

Thank you All!


~~~~~~~~~~~~~~~ ~~~~~~~
Dim strFormName As String
Dim strQueryName As String
Dim strSQL As String
Dim stLinkCriteria As String

strFormName = "FrmDeliver y"

DoCmd.OpenForm strFormName, acNormal, , "SELECT * FROM " & cboAcctName " & "WHERE (((" & cboAcctName & ".Location)="") );", acFormEdit, acWindowNormal

~~~~~~~~~~~~~~~ ~~~~~~~

[NeoPa]

expression.Open Form(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

You will see that a full SELECT query is not expected here.
You can enter a WHERE condition here, but the forms Record Source cannot be set at this point.
To set that to your own SQL, add it in the OnOpen event of the form.

[PEB]

And in the same form you want to display all 25 tables with the respectiv criterias...

So you need few things..

In your form have enough fields = max of fields of your tables... To be sure that it's ok create 255 fields...

Than when you want to visualise a table you need to access the tableDefs section and retrieve the information about the fields

Set all fields from the table to your fields in the form... /This is the control Source property

At the end generate your SQL as a filter... and your table in recordset property..

Your code seems very small to perform all this things... Consult TableDef property for more info..

:)

I have to work with an existing database with 25 tables.
I want the end User to be able to modify rows within the tables (add/amend/delete) via a Form. I do not want to create a query for the form, I want to dynamically create a query to access the table to then display the table within an editable form. Can this work? Anybody got any ideas of how I could do this?

I have tried the following syntax using VBA (access2000), but this does not work. I get no response! I am also confused with the building of my SQL string??? I am going mad here...
Any h elp will be much appreciated!

Thank you All!


~~~~~~~~~~~~~~~ ~~~~~~~
Dim strFormName As String
Dim strQueryName As String
Dim strSQL As String
Dim stLinkCriteria As String

strFormName = "FrmDeliver y"

DoCmd.OpenForm strFormName, acNormal, , "SELECT * FROM " & cboAcctName " & "WHERE (((" & cboAcctName & ".Location)="") );", acFormEdit, acWindowNormal

~~~~~~~~~~~~~~~ ~~~~~~~

[melaine]

Hello PEB,
What happens is that a User will select ONE Table to be displayed from a combo box drop down list.
I would then like the table to open up into a form (displaying all 8 column fields and all the rows of data from the tables).
I require form, so that the User can then make updates to this table!

Hope my query makes more sense!
I am sure that there is a simple solution, but I can figure one out!

I would like this application to work dynamically, rather than having to create 25 queries!

Mel

~~~~~~~~~~~~~~~ ~~~~~~

And in the same form you want to display all 25 tables with the respectiv criterias...

So you need few things..

In your form have enough fields = max of fields of your tables... To be sure that it's ok create 255 fields...

Than when you want to visualise a table you need to access the tableDefs section and retrieve the information about the fields

Set all fields from the table to your fields in the form... /This is the control Source property

At the end generate your SQL as a filter... and your table in recordset property..

Your code seems very small to perform all this things... Consult TableDef property for more info..

:)

[VALIS]

Hello PEB,
What happens is that a User will select ONE Table to be displayed from a combo box drop down list.
I would then like the table to open up into a form (displaying all 8 column fields and all the rows of data from the tables).
I require form, so that the User can then make updates to this table!

Hope my query makes more sense!
I am sure that there is a simple solution, but I can figure one out!

I would like this application to work dynamically, rather than having to create 25 queries!

Mel

~~~~~~~~~~~~~~~ ~~~~~~

Mel,

Are the 8 fields common to all the tables?
If not, are there always 8 fields in a table?

If so you could have a generic form with 8 fields.
Your code could change the controlsource property of the form depending on the selection.
If the field names are different for each table you will need to right some code looping through the controls collection to change the control source of each control.

[melaine]

Well my MAIN PROBLEM is how can I display a FORM dynamically?
I do not want to creat 25 queries. When a user selects a table to be displayed as a form, how can I pass through an SQL query (of sone sor) so that the correct information is shown in the FORM that has already been created?



~~~~~~~~~~~

Mel,

Are the 8 fields common to all the tables?
If not, are there always 8 fields in a table?

If so you could have a generic form with 8 fields.
Your code could change the controlsource property of the form depending on the selection.
If the field names are different for each table you will need to right some code looping through the controls collection to change the control source of each control.

[VALIS]

Well my MAIN PROBLEM is how can I display a FORM dynamically?
I do not want to creat 25 queries. When a user selects a table to be displayed as a form, how can I pass through an SQL query (of sone sor) so that the correct information is shown in the FORM that has already been created?



~~~~~~~~~~~

You won't need to write any queries.
If all the tables have 8 fields all you need to do is change the record source of the controls to reflect the fields of the table selected.

Dim ctl as control


For Each ctl in Myfrm.Controls