unbound field as param for append query

  • Thread starter Thread starter JP
  • Start date Start date
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]));
 
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...
 
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...
 
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...
 
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...
 
Back
Top