We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Left Outer Join SQL query Problem for Form source

Jimby_Aus
Jimby_Aus asked
on
Medium Priority
683 Views
Last Modified: 2008-02-01
Is is possible to use LEFT OUTER JOIN queries as a form source reliably in access?
whilse I can query the data, I am having all sorts of problems adding new records & editing records.

The SQL im trying to use is as follows.

SELECT id, acccode, tblPricing.itemcode, price, qty, tagqty, tag, blocked, description, unitsize, lastcost, gstcode FROM tblPricing LEFT OUTER JOIN tblTmpthinkpadinventory ON tblPricing.itemcode = tblTmpthinkpadinventory.itemcode WHERE tblPricing.acccode = 'ACAANN' ORDER BY tblPricing.itemcode, qty

the pricing table contains the records I want to edit/manage, the other table is just a stock file table that I am referencing the product descriptions from.

I get errors like the following when trying to edit or add.

"Method 'Fields' of Object _Recordset failed.

Thanks.

Jimby.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ive just compiled the application just to be sure, hasnt seem to made a difference.

Ive gotten rid of the left join query, replaced it with a simple SELECT statement, it now allows me to add records successfully, but some edits still fail with:

"Method 'Fields' of Object _Recordset failed."

Part of the problem may be related to the fact that I am assigning a dynamic recordsource to the form at runtime.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.