Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No worries, I do that quite often !



    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  2. #17
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    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!

  3. #18
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ 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)

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  5. #20
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    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

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I changed one line:
    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
    and got this:

    Click image for larger version. 

Name:	raster1.png 
Views:	16 
Size:	58.0 KB 
ID:	47062

  7. #22
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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"

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  9. #24
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I got the same result using different weeks and didn't change anything.
    @Micron
    The DB supplied has the "before/after recordcount" fix included, so it shows correct result as is.

  10. #25
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    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.


    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"
    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?

    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

  11. #26
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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.

  12. #27
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The DB supplied has the "before/after recordcount" fix included, so it shows correct result as is.
    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 more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #28
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  3. Replies: 5
    Last Post: 08-10-2015, 02:07 PM
  4. Replies: 9
    Last Post: 07-06-2015, 11:51 AM
  5. Replies: 2
    Last Post: 03-07-2014, 09:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums