updating a query with data from a table

[wendy]

I have a button (cmdcopyfromdisk) on a form (Reports-main), that when
clicked creates a table from data in an excel file:
There are two comboboxes on the form listing forms(cmbformtodisk) and
subjects (cmbsubjecttodisk). When the button is clicked the computer
will look to import a filename determined by the comboboxes and term
value on the form, and the filename becomes the table name, ensuring
no one overwrites data, and it is all automated. This all works

Private Sub cmdcopyfromdisk_Click()

Dim filename As String
Dim tablename As String
tablename = filename

filename = [Forms]![Reports-main]![Term].Value & "-" & [Forms]!
[Reports-main][cmbsubjecttodisk] & "-" & [Forms]![Reports-main]!
[cmbformtodisk]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
[filename], "C:\" & [filename] & ".xls", True

The import works fine and the table is created with no problem

Then i want two fields Tablename.comments and tablename.examresults
copied out the newly created table "tablename" and copied into an
existing query called "qry-report"

I tried

Update [qry-Report]
Set [qry-Report]!comments = tablename.comments
WHERE [qry-Report].SetID = tablename.SetID And [qry-Report].Subject =
tablename.Subject And [qry-Report].Pupilname = tablename.Pupilname

but it does not like Update - says sub is no defined!

Help, i am self taught, so please go easy on me!

 

[Michel Walsh]

Try:


UPDATE [qry-Report] INNER JOIN tableName
ON [qry-Report].SetID = tablename.SetID
AND [qry-Report].Subject = tablename.Subject
AND [qry-Report].Pupilname = tablename.Pupilname
SET [qry-Report]!comments = tablename.comments



Hoping it may help,
Vanderghast, Access MVP