unbound field as param for append query

J

JP

I have a form from which an append query can be triggered.
Two of the fields serve as inputs for the query.It works
fine but when I tried to add another unbound text box to
also serve as parameters for the append query it stops
working. I don't know SQL very well so this was done in
query design:
INSERT INTO tblContract ( ContractID, SubContract, RepID,
CType, CTypeInv,)
SELECT tblSubContracts.ContractID,
tblSubContracts.SubContract, tblContract.RepID,
tblContract.CType, tblContract.CTypeInv
FROM tblContract INNER JOIN tblSubContracts ON
tblContract.ContractID = tblSubContracts.SubContract
WHERE (((tblSubContracts.ContractID)=[Forms]!
[frmSubContracts]![ContractID]) AND
((tblSubContracts.SubContract)=[Forms]![frmSubContracts]!
[SubContract])) AND ((tblContract.RepID)=[Forms]!
[frmSubContracts]![txtRepID]));
 
J

JP

Sorry that typo is not in my code, I deleted some of the
fields when I pasted it here because it was too long. Any
other typos/expression errors?
-----Original Message-----
Looks like an extra comma after CTypeInv.

--
Duane Hookom
MS Access MVP
--

I have a form from which an append query can be triggered.
Two of the fields serve as inputs for the query.It works
fine but when I tried to add another unbound text box to
also serve as parameters for the append query it stops
working. I don't know SQL very well so this was done in
query design:
INSERT INTO tblContract ( ContractID, SubContract, RepID,
CType, CTypeInv,)
SELECT tblSubContracts.ContractID,
tblSubContracts.SubContract, tblContract.RepID,
tblContract.CType, tblContract.CTypeInv
FROM tblContract INNER JOIN tblSubContracts ON
tblContract.ContractID = tblSubContracts.SubContract
WHERE (((tblSubContracts.ContractID)=[Forms]!
[frmSubContracts]![ContractID]) AND
((tblSubContracts.SubContract)=[Forms]! [frmSubContracts]!
[SubContract])) AND ((tblContract.RepID)=[Forms]!
[frmSubContracts]![txtRepID]));
Click to expand...


.
Click to expand...
 
D

Duane Hookom

It's difficult to say without know what you mean by "stops working". Do all
your field types match including properties such as allowing nulls etc?

--
Duane Hookom
MS Access MVP
--

JP said:
Sorry that typo is not in my code, I deleted some of the
fields when I pasted it here because it was too long. Any
other typos/expression errors?
-----Original Message-----
Looks like an extra comma after CTypeInv.

--
Duane Hookom
MS Access MVP
--

I have a form from which an append query can be triggered.
Two of the fields serve as inputs for the query.It works
fine but when I tried to add another unbound text box to
also serve as parameters for the append query it stops
working. I don't know SQL very well so this was done in
query design:
INSERT INTO tblContract ( ContractID, SubContract, RepID,
CType, CTypeInv,)
SELECT tblSubContracts.ContractID,
tblSubContracts.SubContract, tblContract.RepID,
tblContract.CType, tblContract.CTypeInv
FROM tblContract INNER JOIN tblSubContracts ON
tblContract.ContractID = tblSubContracts.SubContract
WHERE (((tblSubContracts.ContractID)=[Forms]!
[frmSubContracts]![ContractID]) AND
((tblSubContracts.SubContract)=[Forms]! [frmSubContracts]!
[SubContract])) AND ((tblContract.RepID)=[Forms]!
[frmSubContracts]![txtRepID]));
Click to expand...


.
Click to expand...
Click to expand...
 
J

JP

With warnings on, I get "you are about to append 0 rows.."
message. As for the field types, i just tried going into
the query parameters and specifying them there but I get
the same message. I don't believe I have anything for
allowing nulls(SQL newbie here). thanks for your help.
-----Original Message-----
It's difficult to say without know what you mean by "stops working". Do all
your field types match including properties such as allowing nulls etc?

--
Duane Hookom
MS Access MVP
--

Sorry that typo is not in my code, I deleted some of the
fields when I pasted it here because it was too long. Any
other typos/expression errors?
-----Original Message-----
Looks like an extra comma after CTypeInv.

--
Duane Hookom
MS Access MVP
--

I have a form from which an append query can be triggered.
Two of the fields serve as inputs for the query.It works
fine but when I tried to add another unbound text box to
also serve as parameters for the append query it stops
working. I don't know SQL very well so this was done in
query design:
INSERT INTO tblContract ( ContractID, SubContract, RepID,
CType, CTypeInv,)
SELECT tblSubContracts.ContractID,
tblSubContracts.SubContract, tblContract.RepID,
tblContract.CType, tblContract.CTypeInv
FROM tblContract INNER JOIN tblSubContracts ON
tblContract.ContractID = tblSubContracts.SubContract
WHERE (((tblSubContracts.ContractID)=[Forms]!
[frmSubContracts]![ContractID]) AND
((tblSubContracts.SubContract)=[Forms]! [frmSubContracts]!
[SubContract])) AND ((tblContract.RepID)=[Forms]!
[frmSubContracts]![txtRepID]));



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

I usually change the query to a select query to test it. Apparently you
won't get any records returned. You need to troubleshoot either your
criteria or your joins or your data.

--
Duane Hookom
MS Access MVP
--

JP said:
With warnings on, I get "you are about to append 0 rows.."
message. As for the field types, i just tried going into
the query parameters and specifying them there but I get
the same message. I don't believe I have anything for
allowing nulls(SQL newbie here). thanks for your help.
-----Original Message-----
It's difficult to say without know what you mean by "stops working". Do all
your field types match including properties such as allowing nulls etc?

--
Duane Hookom
MS Access MVP
--

Sorry that typo is not in my code, I deleted some of the
fields when I pasted it here because it was too long. Any
other typos/expression errors?

-----Original Message-----
Looks like an extra comma after CTypeInv.

--
Duane Hookom
MS Access MVP
--

message
I have a form from which an append query can be
triggered.
Two of the fields serve as inputs for the query.It works
fine but when I tried to add another unbound text box to
also serve as parameters for the append query it stops
working. I don't know SQL very well so this was done in
query design:
INSERT INTO tblContract ( ContractID, SubContract,
RepID,
CType, CTypeInv,)
SELECT tblSubContracts.ContractID,
tblSubContracts.SubContract, tblContract.RepID,
tblContract.CType, tblContract.CTypeInv
FROM tblContract INNER JOIN tblSubContracts ON
tblContract.ContractID = tblSubContracts.SubContract
WHERE (((tblSubContracts.ContractID)=[Forms]!
[frmSubContracts]![ContractID]) AND
((tblSubContracts.SubContract)=[Forms]!
[frmSubContracts]!
[SubContract])) AND ((tblContract.RepID)=[Forms]!
[frmSubContracts]![txtRepID]));



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Appended field in query 1
Append Query Date Criteria Problem. 13
Append Query truncates data posted to a table memo field 2
append query from form only appends first record 0
Append Query from Unbound Textbox to subform 2
link append query to combobox 0
Null field in append query 4

Top