Cannot add record when textboxes are bound to a JOIN statement

[loonytick11]

Access 2000 front end, MS SQL backend
------------
Two tables (tblA and tblB) have a one-to-one relationship. tblB's primary key is an autonumber. Nulls are allowed on all fields of both tables except the primary keys.

I made a form whose recordsource is the following SQL statement:

SELECT tblA.*, tblB.* FROM tblA INNER JOIN tblB ON tblA.Cann_Tracking_No = tblB.Tracking_No

My form has textboxes that are bound to every field from the RecordSource query, but when I try to add a new record, I get an error. "Invalid input parameter values. Check the status values for detail."

I googled this error and found a few websites (e.g.

http://www.nocomsoftware.se/p5745/files/TD41Fixes.htm)

that tell me this error is a problem with MS SQL in the WHERE clause. However, I do not have a WHERE clause in my statement, and surely JOINing two tables and using that query to populate a form is a very common task (one that should not be giving an error, for pete's sake!).

Why am I receiving this error, and is there a workaround?

 

[ThomasLafferty]

Maybe I'm missing something here, but in examining your SQL statement, I notice that it doesn't have a closing semi-colon. Is that a typo? If not, try adding one like this:

SELECT tblA.*, tblB.* FROM tblA INNER JOIN tblB ON tblA.Cann_Tracking_No = tblB.Tracking_No[COLOR=red][b];[/b][/color]

Maybe this is it...

Tom


Live once die twice; live twice die once.

 

[loonytick11]

Thank you for your help, Tom.

In an Access control's RecordSource there can only be one line, so there is no need for a semicolon. However, in most SQL programming the semicolon is necessary and in general that's a good rule to live by, so I'll try it tomorrow when I get to work.