?
Solved

Left Outer Join SQL query Problem for Form source

Posted on 2004-03-22
3
Medium Priority
?
664 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.
0
Comment
Question by:Jimby_Aus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 750 total points
ID: 10655592
Hello Jimby_Aus,

What happens when you compile your application?
Do you have the correct Library references - check Tools>References in Module design view.
If there are no problems with these then post the code that is giving problems.


Pete
0
 
LVL 1

Author Comment

by:Jimby_Aus
ID: 10655715
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.
0
 
LVL 23

Assisted Solution

by:heer2351
heer2351 earned 750 total points
ID: 10656258
Normally you would base the form on a query that only references tblPricing. Information required from table tblTmpthinkpadinventory can be added to the form using a subform; when multiple fields are required or a DLookup if you only require one field (or a small number of fields). Or depending on the application you could use a combo or listbox which rowsource references tblTmpthinkpadinventory.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question