QueryDef

[331]

I'm trying to update an Oracle number that appears on a form (that's open) to a field on an underlying subform and subsequent table.

I'm able to accurately update Oracle numbers for ALL employee records, but I only want to have it apply to this employee's subform (and subsequent table) that is open. I get an error message when I include the WHERE clause.
My code is below. Can someone offer a suggestion? I've use similar code several years ago and it worked fine.
Also, can anybody direct me to information re. QueryDef and how to write SQL code using it, when to use it, etc. Many many thanks for any suggestions!


Private Sub cmdUpdateOracleNum_Click()

' * * * Open this database * * * *
Dim tsDB As Database, Query As QueryDef
Set tsDB = DBEngine.Workspaces(0).Databases(0)


' * * * Create a temp field, to HOLD the ORACLE NUMBER for this student * * *
Dim strOracleNum As String
strOracleNum = [Forms]![frmStaffing_Students]![StudOracleNum].[Value]
MsgBox "Oracle num is: " & strOracleNum

Dim sqlUpdateOracleNum As String
Me.Refresh

' * * * this statement works fine when I eliminate the WHERE clause but it updates ALL records, not just the one for the employee's Form I have open. * * *

sqlUpdateOracleNum = "UPDATE tblStaff_Student LEFT JOIN tblTimesheets ON tblStaff_Student.MUID = tblTimesheets.TimesheetMUID SET tblTimesheets.OracleNum = [tblStaff_Student].[StudOracleNum] WHERE (((tblTimesheets.TimesheetMUID)=[strOracleNum]));"

Set Query = tsDB.CreateQueryDef("", sqlUpdateOracleNum)
Query.Execute 'ERROR MESSAGE HERE: Run time error 3061 Too few parameters. Expected 1.
Me.Refresh

End Sub

 

[tehNellie]

It looks to me like your last WHERE clause has the syntax wrong:

sqlUpdateOracleNum = "UPDATE tblStaff_Student LEFT JOIN tblTimesheets ON tblStaff_Student.MUID = tblTimesheets.TimesheetMUID SET tblTimesheets.OracleNum = [tblStaff_Student].[StudOracleNum] WHERE (((tblTimesheets.TimesheetMUID)=" & chr(34) & strOracleNum & chr(34) & "));"

The chr(34) is ", I just find it easier than having hundreds of "s to go with Access' thousands of brackets.

 

[331]

Thank you! The code ran OK (no error message) when I clicked the command button to update Oracle. But I don't think the Query update actually ran -- it did not update the Oracle field on the subform or in the table. And, when executing the query, I should have gotten the usual message about an Update query on 1 record since my SetWarnings is True. I'm thinking the temp field I created in the code for the ID was not passing in the WHERE clause. Please send any further suggestions to me.

I so appreciate your suggestion about the chr(34) being "", and I'll definitely use this in the future. Simple thing, but means alot. Thanks.