We have a SQL back end, and an Access data project as the front end. We have a form that displays customer info from a customer table, then we are trying to have a list box with data from another table but ONLY the data that corresponds to that CustomerID. CustomerID is in both tables and relationship has been set. I need to use a value from the form in the query to do this, so we tried this:
SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms]![frmCustomer]![CustomerID]
which from what I have read everywhere is the corret way. But I get an error everytime. (Error in WHERE clause near '!'.
Unable to parse query text.) , and if I save it and go back, it put spaces like this:
SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms] ! [frmCustomer] ! [CustomerID]
I've tried without the square brackets, same problem. If I try to put it in the criteria field for CustomerID instead of editting the SQL directly, I get a Data Type Error in Expression. Any ideas?
SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms]![frmCustomer]![CustomerID]
which from what I have read everywhere is the corret way. But I get an error everytime. (Error in WHERE clause near '!'.
Unable to parse query text.) , and if I save it and go back, it put spaces like this:
SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms] ! [frmCustomer] ! [CustomerID]
I've tried without the square brackets, same problem. If I try to put it in the criteria field for CustomerID instead of editting the SQL directly, I get a Data Type Error in Expression. Any ideas?