Combo Box

[Guest]

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.

 

[Douglas J. Steele]

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)

 

[Guest]

Yeah, tried that, but no joy. Looks like I'll have to do up an invisible
control or a vba variable. Thanks.
--
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

(no e-mails, please!)

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.

 

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yeah, tried that, but no joy. Looks like I'll have to do up an invisible
control or a vba variable. Thanks.
--
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

(no e-mails, please!)

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.