Combo Box

What's wrong with the following used in the row source of a combo box on a
form with other controls (including cbVendorID)...

SELECT DISTINCTROW [tbl_Contracts].[ContractID],
[tbl_Contracts].[ContractNbr], [tbl_Contracts].[ContractReferencable],
[tbl_Contracts].[ReferenceContract],
[tbl_Contracts].[VendorID],
[tbl_Contracts].[ContractDate],
[tbl_Contracts].[ContractType], [tbl_Contracts].[ContractDescription]
FROM [tbl_Contracts]
WHERE vendorid=cbvendorID.column(1);

Access says i've go an undefined function in the WHERE clause.
--
Jim

I don't believe you can use the Column collection in a query. Even if you
could, you aren't telling Access on what form to find the combo box.

Try:

WHERE vendorid=Forms!NameOfForm!cbvendorID.column(1)

(although, as I said, I'm not sure that's legal)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JimS" wrote in message
...
What's wrong with the following used in the row source of a combo box on a
form with other controls (including cbVendorID)...

SELECT DISTINCTROW [tbl_Contracts].[ContractID],
[tbl_Contracts].[ContractNbr], [tbl_Contracts].[ContractReferencable],
[tbl_Contracts].[ReferenceContract],
[tbl_Contracts].[VendorID],
[tbl_Contracts].[ContractDate],
[tbl_Contracts].[ContractType], [tbl_Contracts].[ContractDescription]
FROM [tbl_Contracts]
WHERE vendorid=cbvendorID.column(1);

Access says i've go an undefined function in the WHERE clause.
--
Jim

Yeah, tried that, but no joy. Looks like I'll have to do up an invisible
control or a vba variable. Thanks.
--
Jim


"Douglas J. Steele" wrote:

I don't believe you can use the Column collection in a query. Even if you
could, you aren't telling Access on what form to find the combo box.

Try:

WHERE vendorid=Forms!NameOfForm!cbvendorID.column(1)

(although, as I said, I'm not sure that's legal)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JimS" wrote in message
...
What's wrong with the following used in the row source of a combo box on a
form with other controls (including cbVendorID)...

SELECT DISTINCTROW [tbl_Contracts].[ContractID],
[tbl_Contracts].[ContractNbr], [tbl_Contracts].[ContractReferencable],
[tbl_Contracts].[ReferenceContract],
[tbl_Contracts].[VendorID],
[tbl_Contracts].[ContractDate],
[tbl_Contracts].[ContractType], [tbl_Contracts].[ContractDescription]
FROM [tbl_Contracts]
WHERE vendorid=cbvendorID.column(1);

Access says i've go an undefined function in the WHERE clause.
--
Jim

One other possibility would be to write a public function that returns the
desired value.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"JimS" wrote in message
...
Yeah, tried that, but no joy. Looks like I'll have to do up an invisible
control or a vba variable. Thanks.
--
Jim


"Douglas J. Steele" wrote:

I don't believe you can use the Column collection in a query. Even if you
could, you aren't telling Access on what form to find the combo box.

Try:

WHERE vendorid=Forms!NameOfForm!cbvendorID.column(1)

(although, as I said, I'm not sure that's legal)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JimS" wrote in message
...
What's wrong with the following used in the row source of a combo box
on a
form with other controls (including cbVendorID)...

SELECT DISTINCTROW [tbl_Contracts].[ContractID],
[tbl_Contracts].[ContractNbr], [tbl_Contracts].[ContractReferencable],
[tbl_Contracts].[ReferenceContract],
[tbl_Contracts].[VendorID],
[tbl_Contracts].[ContractDate],
[tbl_Contracts].[ContractType], [tbl_Contracts].[ContractDescription]
FROM [tbl_Contracts]
WHERE vendorid=cbvendorID.column(1);

Access says i've go an undefined function in the WHERE clause.
--
Jim