Solved

Left Outer Join SQL query Problem for Form source

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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