Syntax for FROM tablename on a form

[Guest]

Problem: Recognizing the value of a textbox on a form as the FROM table name in an INSERT INTO query

Situation: DB has multiple tables, all identical structures. Each is generated from a page in a Visio drawing. Access form displays all fields of a table and allow user to switch tables. One of the purposes of the form is to allow user to move a record to a different table. (Don't worry about duplicate records or keys here.) The code below will read the INSERT INTO target as a control on the form and read several textbox control values.

What doesn't work is to recognize the FROM target from the form.
Hard coded (see below) it works fine.
Because I don't seem to be able to access the RECORD SOURCE programatically (in syntax that the query will accept), I put a text box [tbxTable] on the form that gets populated when the user changes the table to display. But I still can't figure out the correct syntax to recognize that field in the FROM clause

INSERT INTO [ME.frmProcessEditor].tbxNewTable ( PageKey, ShapeKey, ShapeText
SELECT tblP1.PageKey, tblP1.ShapeKey, tblP1.ShapeTex
FROM tblP
WHERE (((tblP1.PageKey)=forms.frmProcessEditor.tbxPageKey)
And ((tblP1.ShapeKey)=forms.frmProcessEditor.tbxShapeKey)
And ((tblP1.ShapeText)=forms.frmProcessEditor.tbxShapeText))

All suggestions appraciated

 

[Marshall Barton]

Problem: Recognizing the value of a textbox on a form as the FROM table name in an INSERT INTO query.

Situation: DB has multiple tables, all identical structures. Each is generated from a page in a Visio drawing. Access form displays all fields of a table and allow user to switch tables. One of the purposes of the form is to allow user to move a record to a different table. (Don't worry about duplicate records or keys here.) The code below will read the INSERT INTO target as a control on the form and read several textbox control values.

What doesn't work is to recognize the FROM target from the form.
Hard coded (see below) it works fine.
Because I don't seem to be able to access the RECORD SOURCE programatically (in syntax that the query will accept), I put a text box [tbxTable] on the form that gets populated when the user changes the table to display. But I still can't figure out the correct syntax to recognize that field in the FROM clause.

INSERT INTO [ME.frmProcessEditor].tbxNewTable ( PageKey, ShapeKey, ShapeText )
SELECT tblP1.PageKey, tblP1.ShapeKey, tblP1.ShapeText
FROM tblP1
WHERE (((tblP1.PageKey)=forms.frmProcessEditor.tbxPageKey)
And ((tblP1.ShapeKey)=forms.frmProcessEditor.tbxShapeKey)
And ((tblP1.ShapeText)=forms.frmProcessEditor.tbxShapeText));

First, you have to get the obligatory lecture about proper
normalization of your data. It is a violation of the rules
of relational database theory to have multiple tables for
the same kind of entity. There are many reasons why you
should stick to at least the first three normal forms, one
such reason is all the hoops you are jumping through to deal
with your structure.

The correct table structure would have only one table with
an additional field the identities the Visio page. Then
instead of flipping table names into the record source, you
would specify the drawing page in a Where clause. To move
an object from one page to another would only require an
edit to a bound text box on a form (or programmatically,
executing an Update query).

If you are unable to rectify the normalization issues, then
you will have to use code to construct the SQL statement
(concatenating the literal parts of the query to the form
reference with the table name). I say this facetiously, but
I suppose another way would be to have a separate query for
each table.

 

[Guest]

I totally agree about normalization.

However, in this case the source tables are generated by macros in Visio Pro - distributed by Microsoft! Both Visio and Access become ODBC client AND server. Either can send or receive updates. The Visio macros only allow dealing with one page of a drawing file at a time. It would be great if Visio would put the data from all pages in a single table and that is in fact what I do at a later stage of the application I am building. I don't want to rebuild the Visio part as well.

Come to think if it, there may be a way to trick Visio. I seems there are too many problems with this appraoch. Thanks for the nudge!

 

[Marshall Barton]

I totally agree about normalization.

Sorry about the lecture, I didn't pick up on the fact that
you're dealing with an external data source.

However, in this case the source tables are generated by macros in Visio Pro - distributed by Microsoft! Both Visio and Access become ODBC client AND server. Either can send or receive updates. The Visio macros only allow dealing with one page of a drawing file at a time. It would be great if Visio would put the data from all pages in a single table and that is in fact what I do at a later stage of the application I am building. I don't want to rebuild the Visio part as well.

Since you seem to know what you're about, writing your own
Visio procedure shouldn't be all that big a job.

OTOH, it's not too bad if you do have to resort to
constructing the Insert query in code.

Come to think if it, there may be a way to trick Visio. I seems there are too many problems with this appraoch. Thanks for the nudge!

Good luck,