VBA combo code filter in 2000, 2003 not working in 2007 or 2010 be

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

Hi, I have a glitch,
Trying to have one code work on 2000, 2002, 2003 office plus 2007.
On an afterupdate I select a row source for a combo box.

This code works on 2000 - 2003 office, but gives blank on 2007 and 2010.

Forms!frmSER![cboDeliverCustomer].RowSource = "SELECT
[CustomerDeliveryName],[Delivery_Record_ID],[Cust_ID],[CustomerDeliveryAddress],[CustomerDeliverySuburb]" & _

"FROM [tblDelivery]" & _

"WHERE [Cust_ID] = """ & [Forms]![frmSER]![cboCompany_Name].Column(2) &
"""" 'this works for text


What have I done wrong?
What can I do to make this universal?

Thank you
Kind regards
Bill
 
Hi Ling,
The combo box does not show any result.
The code comes out of a module separate to the form in question.

Where I have refereneced the combo box column as (2) it is the third column
in the query - however 2007 doesn't see this but will see the Column if it is
(0) which is not the correct column - so Access 2000 thru to 2003 will have
no result because the columns are a mismatch.

Other code works it is this referencing back from a module to the forms
controls??????????

Thanx
Bill
 
Don't know if you have a typo in your posting or not, but you are missing a
spaces in the SQL Statement before the WHERE and before the FROM

Forms!frmSER![cboDeliverCustomer].RowSource = "SELECT " & _
"[CustomerDeliveryName], [Delivery_Record_ID], [Cust_ID]" & _
", [CustomerDeliveryAddress], [CustomerDeliverySuburb]" & _
" FROM [tblDelivery]" & _ <<< ADDED SPACE BEFORE FROM
" WHERE [Cust_ID] = """ & _ <<< ADDED SPACE BEFORE WHERE
[Forms]![frmSER]![cboCompany_Name].Column(2) & """"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top