SQL error. "runtime error '2001' you cancelled the previous operation"

bobmac-

Registered User.
Local time
Today, 09:50
Joined
Apr 28, 2008
Messages
59
Hi

I'm using the following vba to reset the record source i.e. (Me.RecordSource = strFilter)

strFilter = "SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id " & _
"WHERE (tblCustomer.cust_short_name) = " & [Forms]![frmApplication]![cboCustomerSearch] & ";"

Unfortunately I get an error message;
"runtime error '2001' you cancelled the previous operation"
From other post I believe the error is in the SQL.

Any ideas anyone. Your help would be greatly appreciated

Cheers
 
If the name field is text, you'd need to surround the value with single quotes. You can add this to debug the SQL string:

Debug.Print strFilter
 
Thanks for the quick reply.
I tried the single quotes but I get a 'expected expression' error (note, originally I had a numeric column). Without the single quotes the debug_print gives the following;

SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id WHERE tblCustomer.cust_short_name = Jones;

Can you suggest another way of putting quotes around the name

strFilter = "SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id " & _
"WHERE tblCustomer.cust_short_name = " & 'Me![cboCustomerSearch].Column(1)' & ";"
 
Thanks for the quick reply.
With single quotes i.e. ''Me![cboCustomerSearch].Column(1)' I get an 'expected expression' compile error. The debug_print gives the following;

SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id WHERE tblCustomer.cust_short_name = Jones;

Is there another way of getting quotes around -Jones-?

Cheers
 
Thanks for the quick reply.
I tried the single quotes but I get a 'expected expression' error (note, originally I had a numeric column). Without the single quotes the debug_print gives the following;

SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id WHERE tblCustomer.cust_short_name = Jones;

Can you suggest another way of putting quotes around the name

strFilter = "SELECT * FROM tblApplication INNER JOIN tblCustomer ON tblApplication.cust_id = tblCustomer.cust_id " & _
"WHERE tblCustomer.cust_short_name = " & 'Me![cboCustomerSearch].Column(1)' & ";"
 
Phew,
I got it to work. "'"Me![cboCustomerSearch].Column(1)"'"
Thanks for the hints
 
Sorry, I meant
& "'" & ..... & "'" &

Cheers
Have a good day!
 
Sorry, was having dinner. Glad you got it sorted out.
 

Users who are viewing this thread

Back
Top Bottom