No worries, I do that quite often !
Cheers,
No worries, I do that quite often !
Cheers,
I never had so many replies to a question, massive thanks and respect to all posters.
To answer some points raised:
I HAVE tried enclosing the Execute/RecordsAffected bit in a With block, and in a QueryDef. It made no difference. Copying still works; record count does not.
I tried getting rid of "If Me.Dirty Then Me.Dirty = False". Turns out I didn't need it. But it made no difference to my problem.
I'm also right pleased with Welshgasman's suggestion to Debug.Print the SQL, that's going to be handy debugging complicated parameter queries, although I'd already (EVENTUALLY) managed it in this case.
I feel pretty frustrated that I've put sooo much effort into trying to figure out why RecordsAffected won't work with my query (when, as I mentioned, it works perfectly with e.g. ssanfu's sample query) and I still don't have an answer.
BUT I have resorted to using Gicu's suggestion of getting the tblSession counts before and after insert (post #5) and, finally, I have my count. So simple. Nice one, Gicu.
I'm still going to keep an eye on the thread, in case anyone can explain what's going on!
@ RasterImage,
There are many things in the code that I consider issues.
However, I want to focus on the SQL. There are two problems (errors) and it is with the functions DateAdd() and DatePart().
The syntax for DateaPart is
Syntax
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
The first parameter is:
interval Required
. String expression that is the interval of time you want to return.
You have
SELECT tblSession.[AppointmentID], DateAdd('d'," & 7 * lngNumWks & ", tblSession.[SessionDate]
and
AND DatePart('ww', tblSession.[SessionDate], 2) = " & DatePart("ww", dtmWCOld)
Note that the delimiters are single quotes instead of double quotes!
The delimiters MUST be double quotes!!!
Add a Debug.Print strSQL immediately after the strSQL assignment to see if the SQL is properly formed.
(this is the way I write the SQL)
Code:strSQL = "INSERT INTO tblSession" strSQL = strSQL & " (AppointmentID, SessionDate, StartHours, StartMinutes, EndHours, EndMinutes, Online, Typed, HoursTyped, Comments, AttType)" strSQL = strSQL & " SELECT tblSession.[AppointmentID], DateAdd('d'," & 7 * lngNumWks & ", tblSession.[SessionDate])," strSQL = strSQL & " tblSession.[StartHours], tblSession.[StartMinutes], tblSession.[EndHours], tblSession.[EndMinutes], tblSession.[Online], tblSession.[Typed]," strSQL = strSQL & " -1 * tblSession.[Typed] *((tblSession.[EndHours] + tblSession.[EndMinutes] / 60) - (tblSession.[StartHours] + tblSession.[StartMinutes] / 60))," strSQL = strSQL & " tblSession.[Comments], " & 1 strSQL = strSQL & " FROM tlkpScheme" strSQL = strSQL & " INNER JOIN ((tblAppointment LEFT JOIN tblStudent ON tblAppointment.[StudentID] = tblStudent.[StudentPK])" strSQL = strSQL & " INNER JOIN tblSession ON tblAppointment.[AppointmentID] = tblSession.[AppointmentID])" strSQL = strSQL & " ON tlkpScheme.[SchemePK] = tblAppointment.[WorkScheme]" strSQL = strSQL & " WHERE tblAppointment.[StaffID] = " & lngUserID strSQL = strSQL & " AND DatePart('ww', tblSession.[SessionDate], 2) = " & DatePart("ww", dtmWCOld) strSQL = strSQL & " AND Year(tblSession.[SessionDate]) = " & Year(dtmWCOld) & ";" Debug.Print strSQL '<<-- add this line/ set a break point here to see the immediate window - single step (F8)
Any chance you would post your dB?? (only need a few records for testing/ change any sensitive info)
I agree - copy db, remove what's not required to solve issue if that helps with privacy, zip it and post. See How to Attach files at top of forum menubar. It would be nice to solve this one. Besides, whoever figures it out will probably get a bonus added to their usual salary from here!
@ RasterImage - if you didn't detect the joke, then you don't know how these forums work.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
ssanfu, thank you so much for taking the trouble to look at my code, and whatever you have to tell me about the issues will be eagerly pored over.
The single quotes in the DatePart bit of the SQL are because if I put double quotes I get a syntax error, I assume because the compiler thinks it's the end of the SQL statement? The single quotes seem to work.
Micron, yes I know you guys do it for love and kicks! And even as a self-taught VBA novice I'm beginning to understand the satisfaction of getting it to do what you want and the thrill that comes with overcoming an intractable problem. I would like to know what's going on with this one.
My database front end is linked to SharePoint lists I keep on the University server. I've made a copy with local tables and a couple of sample users (staff members) and students. For the user experience, log on with password 'password'.
It will be a mess—I've totally been learning how to do this as I went along, so the way I do code has changed a lot as I've gone on. Still lots to learn of course.
The thing we've been looking at is Form_fdlgCopySessions Private Sub cmdOK_Click.
I'll have a go at posting my db. It's the full thing, apart from the changes I mentioned.TimesheetGeneratorSample.zip
I changed one line:
and got this:Code:'Copies the current week's sessions to a new week. On Error GoTo Error_Handler Set dbs = CurrentDb 'dtmWCOld = Forms.frmSessions.txtWC dtmWCOld = Forms("frmSessions").Controls("txtWC") lngNumWks = Me.txtCopyNoWeeks.Value dtmWCNew = DateAdd("ww", lngNumWks, dtmWCOld) lngUserID = Forms.frmMenu.txtUserID
With a bit more investigating I discovered that the original problem was bypassed in the supplied DB.
I removed the "fix" and restored the dbs.RecordsAffected and still got "1 session copied".
So I am unable to replicate the original problem.
I have Access 2019 and Win 11.
Code:Debug.Print strSQL 'lngCountBefore = DCount("*", "tblSession") 'Get count BEFORE append. dbs.Execute strSQL, dbFailOnError 'Perform the append query. lngRowInsrt = dbs.RecordsAffected 'lngCountAfter = DCount("*", "tblSession") 'Get count AFTER append. 'lngRowInsrt = lngCountAfter - lngCountBefore 'Count number of rows added. Forms.frmSessions.Requery 'Sessions form reflects new data. DoCmd.Close acForm, "fdlgCopySessions", acSavePrompt 'Close the dialogue strMsg = lngRowInsrt & " sessions copied to w/c " & dtmWCNew & "." 'Confirm MsgBox strMsg, vbInformation, "Copy confirmation"
Did you do that more than once? I got the same result using different weeks and didn't change anything. I had to refresh the table though, to be sure anything was being added because the form records don't change. Some instructions on how to replicate the issue would be nice.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
@MicronI got the same result using different weeks and didn't change anything.
The DB supplied has the "before/after recordcount" fix included, so it shows correct result as is.
Sorry I am stupid, obviously I should have posted the version of the DB which had the problem we're talking about.
So I pasted the following code into the sample I uploaded—and it worked perfectly!
So I pasted the exact same code into the master copy—and the problem is still there. Append query works; RecordsAffected does not.
The only significant difference is that the sample copy has local tables, the master copy has links to SharePoint lists. Could that be the problem?Code:Dim dbs As DAO.Database 'This database Dim dtmWCOld As Date 'Original week commencing date Dim dtmWCNew As Date 'New week commencing Dim lngNumWks As Long 'Copy to this many weeks in the future Dim lngUserID As Long 'Staff primary key Dim qdfTemp As QueryDef 'The append query Dim strSql As String 'SQL for append query Dim lngRowInsrt As Long 'Number of rows copied Dim strMsg As String 'Message box text 'Copies the current week's sessions to a new week. On Error GoTo Error_Handler 'Save any updates to Copy number of weeks. If Me.Dirty Then Me.Dirty = False Set dbs = CurrentDb dtmWCOld = Forms.frmSessions.txtWC lngNumWks = Me.txtCopyNoWeeks.Value dtmWCNew = DateAdd("ww", lngNumWks, dtmWCOld) lngUserID = Forms.frmMenu.txtUserID strSql = "INSERT INTO tblSession " & _ "(AppointmentID, SessionDate, StartHours, StartMinutes, EndHours, EndMinutes, Online, Typed, HoursTyped, Comments, AttType) " & _ "SELECT tblSession.[AppointmentID], DateAdd('d'," & 7 * lngNumWks & ", tblSession.[SessionDate]), " & _ "tblSession.[StartHours], tblSession.[StartMinutes], tblSession.[EndHours], tblSession.[EndMinutes], tblSession.[Online], tblSession.[Typed], " & _ "-1 * tblSession.[Typed] *((tblSession.[EndHours] + tblSession.[EndMinutes] / 60) - (tblSession.[StartHours] + tblSession.[StartMinutes] / 60)), " & _ "tblSession.[Comments], " & 1 & _ " FROM tlkpScheme " & _ "INNER JOIN ((tblAppointment LEFT JOIN tblStudent ON tblAppointment.[StudentID] = tblStudent.[StudentPK]) " & _ "INNER JOIN tblSession ON tblAppointment.[AppointmentID] = tblSession.[AppointmentID]) " & _ "ON tlkpScheme.[SchemePK] = tblAppointment.[WorkScheme] " & _ "WHERE tblAppointment.[StaffID] = " & lngUserID & _ " AND DatePart('ww', tblSession.[SessionDate], 2) = " & DatePart("ww", dtmWCOld) & _ " AND Year(tblSession.[SessionDate]) = " & Year(dtmWCOld) & ";" Set qdfTemp = dbs.CreateQueryDef("", strSql) 'Perform the append query. With qdfTemp .Execute dbFailOnError lngRowInsrt = .RecordsAffected End With Forms.frmSessions.Requery 'Close the dialogue DoCmd.Close acForm, "fdlgCopySessions", acSavePrompt 'Confirm strMsg = lngRowInsrt & " sessions copied to w/c " & dtmWCNew & "." MsgBox strMsg, vbInformation, "Copy confirmation"
I also use RecordsAffected in Forms_fdlgRestoreBU Private Sub cmdRestore_Click, and in this case it works.
Code:Private Sub cmdRestore_Click() Dim dbs As DAO.Database 'This database Dim lngBackup As Long 'Primary key of backup to resotre Dim strSQLDel As String 'The delete query Dim strSQLInsrt As String 'The append query Dim lngRowDel As Long 'No. of rows deleted Dim lngRowInsrt As Long 'No. of rows inserted On Error GoTo HandleError Set dbs = CurrentDb If Me.Dirty Then Me.Dirty = False If Nz(Me.cboRestore, 0) Then 'Chosen backup is valid. lngBackup = Me.cboRestore End If ' If Nz(Me.cboRestore, 0) Then 'User has chosen a backup. Continue. If DCount("*", "tblOptionsBackup", "[ID] = " & lngBackup) Then 'User's chosen backup exists. 'It's going to be easiest to close the options form and open it again. DoCmd.Close acForm, "frmUserOptions", acSavePrompt 'Define the queries strSQLDel = "DELETE FROM tblUserOptions" strSQLInsrt = "INSERT INTO tblUserOptions " & _ "([UserID],[DefaultScheme],[ExportSave],[ExportDirectory],[ShowSpreadsheet]," & _ "[EmailSpreadsheet],[EmailRecipient],[SignaturePath],[PasswordAlways],[BUName]) " & _ "SELECT [UserID],[DefaultScheme],[ExportSave],[ExportDirectory],[ShowSpreadsheet]," & _ "[EmailSpreadsheet],[EmailRecipient],[SignaturePath],[PasswordAlways],[BUName] " & _ "FROM tblOptionsBackup " & _ "WHERE [ID] = " & lngBackup 'Delete the current record from User Options dbs.Execute strSQLDel, dbFailOnError lngRowDel = dbs.RecordsAffected If lngRowDel Then 'Previous options deleted, can restore backup. 'Add the selected backup to the Options table. dbs.Execute strSQLInsrt, dbFailOnError lngRowInsrt = dbs.RecordsAffected 'Close the backup form. DoCmd.Close acForm, "fdlgRestoreBU", acSavePrompt If lngRowInsrt Then 'A record was appended to options table. 'Requery the welcome form, since it is now based on a new record. If IsFrmOpen("frmMenu") Then Forms.frmMenu.Requery End If 'Re-open the Options form. DoCmd.OpenForm "frmUserOptions" Forms("frmUserOptions").Move 0, 0 'Show a confirmation message MsgBox "Your chosen backup has been restored.", _ vbInformation, "Restore confirmation" Else 'Failed to insert an options record. Application can't work without it. MsgBox "Critical failure to restore your options. " & vbNewLine & _ "You'll have to close the application and start again.", _ vbCritical, "Critical error" CloseFormsReports Exit Sub End If Else 'Failed to delete previous options. MsgBox "Restore operation failed for unknown reason." & vbNewLine & _ "Your previous options are intact.", vbExclamation, "Error message" End If Else 'We don't have a valid backup to restore. MsgBox "Please choose a backup from the drop-down list.", _ vbExclamation, "Sorry pal" Me.cboRestore.Requery End If
Did a bing search and found this. Not a solution but a confirmation.
vba - RecordsAffected count from query not working with Sharepoint - Stack Overflow
So it looks like you'll have to stay with the recordcount fix for appends to sharepoint.
I'm getting lost in here. If the form action I used only ever copies one record, what is the point of worrying about RecordsAffected if it's always going to be 1? And we know this still doesn't work on SP lists if the Access code uses either a query def object or a stored query?The DB supplied has the "before/after recordcount" fix included, so it shows correct result as is.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks for finding that, davegri. Disappointing, but an explanation of sorts. I guess that SharePoint just doesn't send the information to Access. Except it's weird how it works in some of my subs but not others.
Micron, in my Post #25 I put 2 bits of code. The first copies all sessions from one week to another. There could be any number of sessions, and I want a confirmation message for the user to check that the expected number of sessions were copied. In this case RecordsAffected isn't working, so I'm using before/after RecordCount instead.
The second bit of VBA is to restore a single record from backup. No, for this one I didn't really need to count the sessions, I just use RecordsAffected to check that a record was successfully restored. In this case RecordsAffected is working, returning 0 or 1 as expected.