CODEWITHSUNDEEP
vbams-access
user9489641
user9489641

Reputation:

Identical UPDATE query. One works the other doesn't

Trying to update a table using the UPDATE query which it gets from different Recordsets. One UPDATE query runs and updates the records but the other runs, doesn't update the records and doesn't throws any error where the problem is. I suspect it's somewhere in the WHERE clause but can't pinpoint.

I played around with the quotation marks but no luck and this is where I am confused the most about. Single, Double quotes and & character are a bit confusing for me.

extArea_SQL = "SELECT * FROM Area;"
Set rsArea = CurrentDb.OpenRecordset(extArea_SQL, dbOpenDynaset)
extDiscipline_SQL = "SELECT * FROM Disciplines;"
Set rsDisp = CurrentDb.OpenRecordset(extDiscipline_SQL, dbOpenDynaset)

instAreaName_SQL = "UPDATE ReportGen SET ReportGen.AreaName = '" & rsArea.Fields("AreaName") & "' WHERE ReportGen.AreaID = '" & rsArea.Fields("AreaID") & "';"
updtDispName_SQL = "UPDATE ReportGen SET ReportGen.DisciplineName = '" & rsDisp.Fields("DisciplineName") & "' WHERE ReportGen.DisciplineID = '" & rsDisp.Fields("DisciplineID") & "';"

db.Execute instAreaName_SQL, dbFailOnError
db.Execute updtDispName_SQL, dbFailOnError

I'd also like to point out that AreaName and the Discipline data are string and has special characters like -,()/ etc.

Not sure what I am doing wrong with the lower (updtDispName_SQL) UPDATE query where as the one above works with no problems. I copy pasted the format of the one working and made changes to the lower one but still can't point out.

Can any one point out what I am missing out here?

Upvotes: 1

Views: 54

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider pure SQL without recordsets as Access supports JOIN or subqueries in UPDATE. Save below as stored queries and run as needed with GUI or VBA. Unlike your code which did not loop and would only run on current recordset row (not necessarily the first), below queries run on all matched rows.

SQL

Query 1

UPDATE ReportGen r
INNER JOIN Area a
  ON r.AreaID = a.AreaID
SET r.AreaName = a.AreaName;

Query 2

UPDATE ReportGen r
INNER JOIN Disciplines d
   ON r.DisciplineID = d.DisciplineID
SET r.DisciplineName = d.DisciplineName;

VBA

'DOES NOT SHOW WARNINGS
CurrentDb.Execute "mySavedUpdateQuery"

'DOES SHOW WARNINGS (NO NEED TO CLOSE ACTION QUERIES)
DoCmd.OpenQuery "mySavedUpdateQuery"

Upvotes: 1

Related Questions