append query for associate entity

[cableguy47905 via AccessMonster.com]

There are a lot of posts for append queries, but I can't quite find one that
helps me out.

I am trying to run an append query from a command button on a form that has
two bound fields and one unbound field.

This is the following code that I have.

Dim strSQL As String
strSQL = "INSERT INTO TBL_CompanyContractLocation ( CompanyID, LocationID,
ContractNumber )" & "SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID, TBL_CompanyContractLocation.
ContractNumber FROM TBL_CompanyContractLocation WHERE ((
(TBL_CompanyContractLocation.CompanyID)=[Forms]![FRM_AddLocations]!
[txtCompanyID]) AND ((TBL_CompanyContractLocation.LocationID)=[Forms]!
[FRM_AddLocations]![txtLocationID]) AND ((TBL_CompanyContractLocation.
ContractNumber)=[Forms]![FRM_AddLocations]![txtContractNumber]));"
Debug.Print strSQL
DoCmd.RunSQL strSQL

The code is one long string, so I don't have the problem with the multiple
lines.

The following is the string in the immediate window:

INSERT INTO TBL_CompanyContractLocation ( CompanyID, LocationID,
ContractNumber )SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID, TBL_CompanyContractLocation.
ContractNumber FROM TBL_CompanyContractLocation WHERE ((
(TBL_CompanyContractLocation.CompanyID)=[Forms]![FRM_AddLocations]!
[txtCompanyID]) AND ((TBL_CompanyContractLocation.LocationID)=[Forms]!
[FRM_AddLocations]![txtLocationID]) AND ((TBL_CompanyContractLocation.
ContractNumber)=[Forms]![FRM_AddLocations]![txtContractNumber]));


It doesn't do anything. No errors, nothing. I go back to the table and
there aren't any added lines.

The three fields all make up the primary key. They are all set as duplicates
OK.

The other restrictions are that there can't be duplicate locations per
contract, and there can't be duplicate contracts per company.

The restrictions are pretty tight, but even following the correct criteria,
it will not give me anything.
Can anyone help with this?
I would greatly appreciate it.
Thanks in advance,
Lee

 

[SteveM]

So is this button on FRM_AddLocations?
If so, what is the point of getting the ID from a table when you have the
values in your form anyway? You could do it like this:

strSQL = "INSERT INTO TBL_CompanyContractLocation (CompanyID, LocationID,
ContractNumber) VALUES (" & Me![txtCompanyID] & "," & Me![txtLocationID] &
"," & Me![txtContractNumber] &");"

In any case, for the query to work as posted:
strSQL = "INSERT INTO TBL_CompanyContractLocation (CompanyID, LocationID,
ContractNumber) SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID,
TBL_CompanyContractLocation.ContractNumber FROM TBL_CompanyContractLocation
WHERE ((
(TBL_CompanyContractLocation.CompanyID)= " & [Forms]![FRM_AddLocations]!
[txtCompanyID] & ") AND ((TBL_CompanyContractLocation.LocationID)= " &
[Forms]![FRM_AddLocations]![txtLocationID] & ") AND
TBL_CompanyContractLocation.ContractNumber)= " &
[Forms]![FRM_AddLocations]![txtContractNumber] & "));"

Steve

There are a lot of posts for append queries, but I can't quite find one that
helps me out.

I am trying to run an append query from a command button on a form that has
two bound fields and one unbound field.

This is the following code that I have.

Dim strSQL As String
strSQL = "INSERT INTO TBL_CompanyContractLocation ( CompanyID, LocationID,
ContractNumber )" & "SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID, TBL_CompanyContractLocation.
ContractNumber FROM TBL_CompanyContractLocation WHERE ((
(TBL_CompanyContractLocation.CompanyID)=[Forms]![FRM_AddLocations]!
[txtCompanyID]) AND ((TBL_CompanyContractLocation.LocationID)=[Forms]!
[FRM_AddLocations]![txtLocationID]) AND ((TBL_CompanyContractLocation.
ContractNumber)=[Forms]![FRM_AddLocations]![txtContractNumber]));"
Debug.Print strSQL
DoCmd.RunSQL strSQL

The code is one long string, so I don't have the problem with the multiple
lines.

The following is the string in the immediate window:

INSERT INTO TBL_CompanyContractLocation ( CompanyID, LocationID,
ContractNumber )SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID, TBL_CompanyContractLocation.
ContractNumber FROM TBL_CompanyContractLocation WHERE ((
(TBL_CompanyContractLocation.CompanyID)=[Forms]![FRM_AddLocations]!
[txtCompanyID]) AND ((TBL_CompanyContractLocation.LocationID)=[Forms]!
[FRM_AddLocations]![txtLocationID]) AND ((TBL_CompanyContractLocation.
ContractNumber)=[Forms]![FRM_AddLocations]![txtContractNumber]));


It doesn't do anything. No errors, nothing. I go back to the table and
there aren't any added lines.

The three fields all make up the primary key. They are all set as duplicates
OK.

The other restrictions are that there can't be duplicate locations per
contract, and there can't be duplicate contracts per company.

The restrictions are pretty tight, but even following the correct criteria,
it will not give me anything.
Can anyone help with this?
I would greatly appreciate it.
Thanks in advance,
Lee

 

[cableguy47905 via AccessMonster.com]

Awesome.

Thanks Steve, I don't know what I was thinking, trying to get them from a
table. I was thinking waaaay to hard about it.

Thanks again. It worked.

So is this button on FRM_AddLocations?
If so, what is the point of getting the ID from a table when you have the
values in your form anyway? You could do it like this:

strSQL = "INSERT INTO TBL_CompanyContractLocation (CompanyID, LocationID,
ContractNumber) VALUES (" & Me![txtCompanyID] & "," & Me![txtLocationID] &
"," & Me![txtContractNumber] &");"

In any case, for the query to work as posted:
strSQL = "INSERT INTO TBL_CompanyContractLocation (CompanyID, LocationID,
ContractNumber) SELECT TBL_CompanyContractLocation.CompanyID,
TBL_CompanyContractLocation.LocationID,
TBL_CompanyContractLocation.ContractNumber FROM TBL_CompanyContractLocation
WHERE ((
(TBL_CompanyContractLocation.CompanyID)= " & [Forms]![FRM_AddLocations]!
[txtCompanyID] & ") AND ((TBL_CompanyContractLocation.LocationID)= " &
[Forms]![FRM_AddLocations]![txtLocationID] & ") AND
TBL_CompanyContractLocation.ContractNumber)= " &
[Forms]![FRM_AddLocations]![txtContractNumber] & "));"

Steve

There are a lot of posts for append queries, but I can't quite find one that
helps me out.

[quoted text clipped - 45 lines]

Thanks in advance,
Lee