?
Solved

Left Outer Join SQL query Problem for Form source

Posted on 2004-03-22
3
Medium Priority
?
668 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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