By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Trying to add the necessary SQL to turn a simple append query into a parameter query...

P: n/a
MLH
I would like to modify the following SQL...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt;

somehow so that an extra field in tblPmtsRcd would also
receive a value. The extra field is [CkNo]. When the query
runs, I'd like the user to be asked to enter the check number.
I tried the following, but it didn't work...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt, MyCkNo
WHERE MyCkNo=[Check Number? (may be left blank):];

but Access tells me there's a syntax error due to a missing operator.
Feb 12 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
MLH
.... answering my own post again - I get tired
of playing with myself. Is the following a valid
way to do it?

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt, 0 AS MyCkNo
WHERE 0=[Check Number? (may be left blank):];

Feb 12 '07 #2

P: n/a
MLH
That was no good (Query input must contain at least
one table or query - error). This gives me the same error...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt, 0 AS MyCkNo
WHERE 0=[Check Number? (may be left blank):];

Suggestions?
Feb 12 '07 #3

P: n/a
MLH wrote:
I would like to modify the following SQL...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt;

somehow so that an extra field in tblPmtsRcd would also
receive a value. The extra field is [CkNo]. When the query
runs, I'd like the user to be asked to enter the check number.
I tried the following, but it didn't work...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt, MyCkNo
WHERE MyCkNo=[Check Number? (may be left blank):];

but Access tells me there's a syntax error due to a missing operator.
Where is the "From" table in your Select statement part?
Feb 12 '07 #4

P: n/a
MLH
<snip>
I had a hunch someone would ask that. Well, I'm not pulling
data FROM any source table to go into another. Was kind-a-hopin'
I could skip the FROM. Obviously not. So I tried this - I made up
a bogus table with a single 10-char text field and put 1 bogus record
in it - no INDEX. Then I tried this SQL...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID, 500 AS MyPmtAmt,
tblOneRecord.OneRecordTableField
FROM tblOneRecord
WHERE tblOneRecord.OneRecordTableField=[Check Number? (may be left
blank):];

Unfortunately, that didn't work either. I have 3-values I want to put
in 3-fields in a new table record. One is return value of procedure,
the other is sitting in a control on an open form (typed by user) and
the last I want the PARAM query to ask for. Maybe it's not possible.
I haven't been able to figure it out.
>
Where is the "From" table in your Select statement part?
Feb 12 '07 #5

P: n/a
MLH wrote:
<snip>
I had a hunch someone would ask that. Well, I'm not pulling
data FROM any source table to go into another. Was kind-a-hopin'
I could skip the FROM. Obviously not. So I tried this - I made up
a bogus table with a single 10-char text field and put 1 bogus record
in it - no INDEX. Then I tried this SQL...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID, 500 AS MyPmtAmt,
tblOneRecord.OneRecordTableField
FROM tblOneRecord
WHERE tblOneRecord.OneRecordTableField=[Check Number? (may be left
blank):];

Unfortunately, that didn't work either. I have 3-values I want to put
in 3-fields in a new table record. One is return value of procedure,
the other is sitting in a control on an open form (typed by user) and
the last I want the PARAM query to ask for. Maybe it's not possible.
I haven't been able to figure it out.
Here's another method to insert.
Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Sometimes when things get to be a pita I simply use DAO to write the
record. You can use an Input box or form to get the prompt data.

Not sure what the Where clause is used in your command if not coming
form a table.

>
>>Where is the "From" table in your Select statement part?

Feb 12 '07 #6

P: n/a
On Mon, 12 Feb 2007 09:50:24 -0500, MLH <CR**@NorthState.netwrote:
>That was no good (Query input must contain at least
one table or query - error). This gives me the same error...

INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt, CkNo)
SELECT GetCurrentVehicleJobID() AS MyVehicleJobID,
[Forms]![frmVehicleEntryForm]![PmtWJDC] AS MyPmtAmt, 0 AS MyCkNo
WHERE 0=[Check Number? (may be left blank):];

Suggestions?
Have you tried running the query from the SQL view of the query editor?
If you attempt to run the query from the design view you must have at least one
table or it will throw the error you are getting. This is not always the case
when the query is executed from the SQL view. The query will execute from the
SQL view if the SQL syntax is valid.
Wayne Gillespie
Gosford NSW Australia
Feb 12 '07 #7

P: n/a
MLH
<snip>
I'm with you. Life is too short. Going with DAO.
>
Sometimes when things get to be a pita I simply use DAO to write the
record. You can use an Input box or form to get the prompt data.
Feb 13 '07 #8

P: n/a
MLH
I've noticed SQL that won't go into DESIGN view from SQL
view (IE, union query SQL). I can't say that I've ever run a
query in SQL view that would zing me with an error if converted
to design view first and then run. I'll have a shot at it. So far,
my SQL is just screwed up, I think.

Will bear your comments in mind. For now, I'm just going with
DAO to get on with things.
Feb 13 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.