Define FROM target in Append Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 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!
 
You may need to use DAO to modify the SQL property of the querydef. In it's
simplest form:
CurrentDb.QueryDefs("qselYourQuery").SQL = "INSERT INTO " & Me.tbxNewTable &
" (....."

--
Duane Hookom
MS Access MVP


rbrandt said:
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
Click to expand...
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
Click to expand...
programatically (in syntax that the query will accept), I put a text box 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));

All suggestions appraciated!
Click to expand...
 
Sorry, your way over my head. I think this looks like VBA which is still a foreign language to me. Is there possibly another way to get where I'm trying to go

thanx
 
You can't use a reference to a form control in the SQL statement like you
are attempting. I believe the only way is to write code to modify the sql
property of the querydef.

--
Duane Hookom
MS Access MVP


rbrandt said:
Sorry, your way over my head. I think this looks like VBA which is still
Click to expand...
a foreign language to me. Is there possibly another way to get where I'm
trying to go?
 
Assuming you have code in the form frmProcessEditor perhaps you place code
in the after update event of tbxNewTable.

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qdf = db.QueryDefs("qappQuery")
strSQL = "INSERT INTO [" & Me.tbxNewTable & "] ( PageKey, ShapeKey,
ShapeText )" & _
"SELECT tblP1.PageKey, tblP1.ShapeKey, tblP1.ShapeText " & _
"FROM tblP1 " & _
"WHERE PageKey=forms.frmProcessEditor.tbxPageKey " & _
"And ShapeKey =forms.frmProcessEditor.tbxShapeKey " & _
"And ShapeText=forms.frmProcessEditor.tbxShapeText;"
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

You could resolve the other references to the form. I didn't do this since I
wasn't aware of the data types.
 
Back
Top