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

Originally Posted by Help

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

Originally Posted by 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 = "FrmDelivery"

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

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

Originally Posted by 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..

:)

Originally Posted by 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

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

Originally Posted by VALIS

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.

Originally Posted by 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?



~~~~~~~~~~~