Solved

Left Outer Join SQL query Problem for Form source

Posted on 2004-03-22
3
663 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 250 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 250 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

724 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