Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Update with LEFT JOIN

    Hello All,
    Just joined and this is my first post, so I hope this isn't a silly question.

    I've been trying to figure out how to use VB to create a LEFT JOIN and perform an update on the results of the query.
    The join works fine and returns the data to my form as expected.

    I will summarize in the hope that this is an obvious problem. Please let me know if I need more detail.

    Set rs = db.OpenRecordset("SELECT .... ")
    rs.Edit
    rs!Blah = frm!txtBlah


    rs.Update

    The problem is that when I select a value to update that exists in the "right" table, it creates a second entry in the "left" table and never updates the "right" table.

    I'm sorry if this is too vague, but is it even possible in Access to perform an update with a JOIN?

    Thanks for any assistance!

  2. #2
    Join Date
    Aug 2008
    Posts
    2
    I will elaborate.

    I have a LEFT JOIN that is performed in the "After Update" property. It is filling a form that contains 4 fields from one table and 1 from another. When pressing the Update button on the form, the code uses an rs.Edit and rs.Update to modify the tables. Right now I am trying to perform this with 2 separate recordsets because it seems the LEFT JOIN wasn't working for me.
    I am doing a simple SELECT on the left table and filling the rs fields with the frm data. The problem is that it isn't updating the table, instead it is creating a new row. Is this because of the extra field on the form? I am not including it in the rs fields, so I'm not sure why this is happening.
    Thanks for any help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relocating objects via "Left" properties value
    By Ripcut in forum Programming
    Replies: 0
    Last Post: 08-01-2008, 06:40 PM
  2. Examples of left joins?
    By narayanis in forum Queries
    Replies: 0
    Last Post: 06-14-2008, 06:17 AM
  3. Left function with various lenth of digits
    By mohsin74 in forum Queries
    Replies: 0
    Last Post: 07-10-2007, 02:00 PM
  4. Replies: 1
    Last Post: 09-19-2006, 11:07 AM
  5. Dynamic Query Outer Join Problem
    By mjack003 in forum Queries
    Replies: 0
    Last Post: 07-21-2006, 01:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums