Trying to use an update query

[lawdy]

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.

[davegri]

Here's what CoPilot suggests. What you have not provided is the matching ID for the target record between the tables. (the WHERE condition)

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

[lawdy]

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

[davegri]

Too few parameters is usually due to spelling. Be sure your table names and field names are absolutely correct. Maybe fLngProposalNo ?

[isladogs]

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:

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

[xps35]

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.

[lawdy]

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.

[madpiet]

Harsh lesson in proper table design, for sure.

[Ken Sheridan]

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).