Form SQL statement

[Guest]

Hey everyone,
I am working on a purchasing database. I have a form that brings up
the order details (Items, quantities, ect) that is based off the below SQL
statement. My problem is that the CompanyID isn't carrying over to the form
when it opens. Any help you can offer would be really appreciated.

SELECT DISTINCTROW tblPurchase.*, tblCompanies.Name
FROM tblCompanies INNER JOIN tblPurchase ON tblCompanies.CompanyID = _
tblPurchase.CompanyID
Where (((tblPurchase.CompanyID)=[forms]![frmPurchasebySupplier]! _
[CompanyID]));

Troy W.

 

[Van T. Dinh]

You haven't got the CompanyID in the selection list.

Try:

SELECT DISTINCTROW tblPurchase.*, tblCompanies.CompanyID, tblCompanies.Name
FROM tblCompanies INNER JOIN tblPurchase ON tblCompanies.CompanyID = _
tblPurchase.CompanyID
Where (((tblPurchase.CompanyID)=[forms]![frmPurchasebySupplier]! _
[CompanyID]));

 

[JohnFol]

Troy, I think you need to explain "CompanyID isn't carrying over to the
form". Also, it's source is 2 separate tables. Which one does not appear?

1) Does it appear in the query?
2) Is it aliased in the query as company ID exists more that 1 time in the
returned recordset?
3) Does it appear in the Field List?

You haven't got the CompanyID in the selection list.

Try:

SELECT DISTINCTROW tblPurchase.*, tblCompanies.CompanyID,
tblCompanies.Name
FROM tblCompanies INNER JOIN tblPurchase ON tblCompanies.CompanyID = _
tblPurchase.CompanyID
Where (((tblPurchase.CompanyID)=[forms]![frmPurchasebySupplier]! _
[CompanyID]));

--
HTH
Van T. Dinh
MVP (Access)

Hey everyone,
I am working on a purchasing database. I have a form that brings up
the order details (Items, quantities, ect) that is based off the below
SQL
statement. My problem is that the CompanyID isn't carrying over to the form
when it opens. Any help you can offer would be really appreciated.

SELECT DISTINCTROW tblPurchase.*, tblCompanies.Name
FROM tblCompanies INNER JOIN tblPurchase ON tblCompanies.CompanyID = _
tblPurchase.CompanyID
Where (((tblPurchase.CompanyID)=[forms]![frmPurchasebySupplier]! _
[CompanyID]));

Troy W.