Results 1 to 9 of 9
  1. #1
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    216

    Trying to use an update query

    I am trying to create an update query to add data from 3 fields of one table into the fields of a second table. I copied a formula from the internet but I really don't know what I am doing. Here is the formula I copied.

    Sub modProQue_toCal()




    Dim db As DAO.Database
    Set db = CurrentDb

    Dim strSQL As String
    strSQL = "UPDATE tblCalendar SET fLngProposalNo = (SELECT fLngProposalNo FROM tblProposal WHERE tblCalendar.ref = tblProposal.ref) WHERE tblCalendar.ref IS NOT NULL;"

    db.Execute strSQL, dbFailOnError
    End Sub

    The query would have to locate the field fDate in tblCalendar then locate the correct record in tblProposal by the lngProposalNo (a long) . Then from tblProposal, put data into 3 fields in tblCalendar. The fields are same in both tables. One is fDate (a date), one is fJobName ( a string) then fRev (a double). The tables are in the same folder. This is a lot to ask, but I need the help. Thanks in advance.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Office 365
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,739
    Here's what CoPilot suggests. What you have not provided is the matching ID for the target record between the tables. (the WHERE condition)

    Code:
    Sub UpdateCalendarFromProposal()
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim strSQL As String
    
        strSQL = "UPDATE tblCalendar " & _
                 "SET fDate = (SELECT fDate FROM tblProposal WHERE tblProposal.ID = tblCalendar.ID), " & _
                 "fJobname = (SELECT fJobname FROM tblProposal WHERE tblProposal.ID = tblCalendar.ID), " & _
                 "fRev = (SELECT fRev FROM tblProposal WHERE tblProposal.ID = tblCalendar.ID) " & _
                 "WHERE EXISTS (SELECT 1 FROM tblProposal WHERE tblProposal.ID = tblCalendar.ID);"
    
        db.Execute strSQL, dbFailOnError
    End Sub

  3. #3
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    216
    Ok davergri, I added what I thought was missing as follows

    Sub UpdateCalendarFromProposal()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim strSQL As String


    strSQL = "UPDATE tblCalendar " & _
    "SET fDate = (SELECT fDate FROM tblProposal WHERE tblProposal.lngProposalNo = tblCalendar.lngProposalNo), " & _
    "fJobname = (SELECT fJobname FROM tblProposal WHERE tblProposal.lngProposalNo = tblCalendar.lngProposalNo), " & _
    "fRev = (SELECT fRev FROM tblProposal WHERE tblProposal.lngProposalNo = tblCalendar.lngProposalNo) " & _
    "WHERE EXISTS (SELECT 1 FROM tblProposal WHERE tblProposal.lngProposalNo = tblCalendar.lngProposalNo);"


    db.Execute strSQL, dbFailOnError
    End Sub

    I get run-time error 3061 - too few parameters. Expected 4. I looked that up. It could be about anything. Any Ideas

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Office 365
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,739
    Too few parameters is usually due to spelling. Be sure your table names and field names are absolutely correct. Maybe fLngProposalNo ?

  5. #5
    isladogs's Avatar
    isladogs is offline Access MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,204
    First of all, you should not duplicate data in more than one table so it should never be necessary to perform this operation.

    Having said that you are making this unnecessarily difficult.
    You want to update 3 fields in tbl~Calendar with data from the same fields in tblProposal but only where the fields fLngProposalNo and Ref match
    So you make a query joining the 2 tables by those fields and change it to an update query:

    Code:
    UPDATE tblCalendar
    INNER JOIN tblProposal ON tblCalendar.Ref = tblProposal.Ref
    AND tblCalendar.fLngProposalNo = tblProposal.fLngProposalNo
    SET
        tblCalendar.fDate = tblProposal.fDate,
        tblCalendar.fJobName = tblProposal.fJobName,
        tblCalendar.fRev = tblProposal.fRev;


    There is no need to filter for
    tblCalendar.ref IS NOT NULL due to the join
    Last edited by isladogs; 09-21-2025 at 06:46 AM.
    Colin Riddington, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I know I don't know, I keep quiet!

  6. #6
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    299
    The main question here is why you want the same data in two tables?
    Please explain more about the context and show us at least your datamodel. Beter though is to share a copy of your database.
    Groeten,

    Peter

  7. #7
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    216
    Well, thanks everyone. I have the query worked out. As far as having the same data in different tables, I set this up when I was very new with Access & computers. I am still a novice. But I am now going back to restructure the tables more correctly. Thanks again for all your help.

  8. #8
    madpiet is offline Expert
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    564
    Harsh lesson in proper table design, for sure.

  9. #9
    Join Date
    Sep 2025
    Location
    Newport, Shropshire, UK
    Posts
    16
    Quote Originally Posted by lawdy View Post
    I am now going back to restructure the tables more correctly.
    On the basis of the information we have, that will require the dropping of the fDate, fJobName and fRev columns from the Calendar table. You then just need to join the tables on the ProposalNo columns and return whatever columns are needed from the two tables. Be sure that any other non-key columns in the Calendar table are functionally determined solely by the whole of the table's primary key, which I assume to be the ProposalNo column. The table will then be normalized to Third Normal Form (3NF). I am attaching a little demo file which gives as simple as possible (but no more so) illustrations of normalization up to Fifth Normal Form (5NF).
    Attached Files Attached Files

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

Similar Threads

  1. UPDATE 3073 error trying to update a table field
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 10-11-2018, 05:11 PM
  2. Replies: 2
    Last Post: 02-23-2017, 10:46 AM
  3. Replies: 5
    Last Post: 01-26-2017, 04:18 PM
  4. Replies: 7
    Last Post: 01-01-2016, 11:17 PM
  5. trying to use after update macro to change field
    By justlearning123 in forum Programming
    Replies: 3
    Last Post: 07-03-2015, 12:32 AM

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