Results 1 to 6 of 6

Updating recordset "with .datasource" where first record =" input box" and last record="input box".

  1. #1
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131

    Updating recordset "with .datasource" where first record =" input box" and last record="input box".

    Hello sir,



    I am using mailmerge by vba with following code:

    Code:
    Private Sub MailMerge_Click()On Error GoTo DoMailMerge_Err
    
    
    'Nodte: A VBA Reference to the Word Object Model is required, via Tools|References
    Dim wdApp As New word.Application, wdDoc As word.Document
    Dim strWorkbookName As String: strWorkbookName = CurrentProject.FullName
    With wdApp
      'Disable alerts to prevent an SQL prompt
      .DisplayAlerts = wdAlertsNone
      'Open the mailmerge main document
      Set wdDoc = .Documents.Open(CurrentProject.Path & "\" & [GteeIssueADSubForm].Form![GTEE_FORMATE], _
        ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
      With wdDoc
        With .MailMerge
          'Define the mailmerge type
          .MainDocumentType = wdFormLetters
          'Define the output
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = False
          'Connect to the data source
          .OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
            LinkToSource:=True, AddToRecentfiles:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="TABLE GteeTrayTbl", _
            SQLStatement:="SELECT * FROM [GteeTrayTbl]", _
            SubType:=wdMergeSubTypeAccess
            
            With .DataSource
            .FirstRecord = InputBox("Enter the First Record # to merge", , 1)
            .LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount) ' replace 2o to .RecordCount to get record count
            End With
      
          'Excecute the merge
          .Execute
          'Disconnect from the data source
          .MainDocumentType = wdNotAMergeDocument
        End With
        'Close the mailmerge main document
        .Close False
      End With
      'Restore the Word alerts
      .DisplayAlerts = wdAlertsAll
      'Display Word and the document
      .Visible = True
    End With
    
    
    
    
    DoMailMerge_Exit:
        Exit Sub
    
    
    
    
    DoMailMerge_Err:
        Select Case MsgBox(Error$)
        End Select
        Resume DoMailMerge_Exit
    End Sub
    It works properly.
    I want to update my "Remarks" field with value "Done" which data I have successfully mailmerged with same click(I mean to say with same command button).
    i.e.
    With .DataSource .FirstRecord = InputBox("Enter the First Record # to merge", , 1)
    .LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount) ' replace 2o to .RecordCount to get record count
    End With
    this recordset fields' "remarks" should be updated as "Done".

    I'm using Access2007, window 10.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,376
    This code is in Access but you open a Workbook to merge with Word?

    Where exactly do you want "Done" to update - field in Access table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131
    I copy this code from excel and edited it so that it works in access. I have tasted this code in access with form (datasheet view) and got proper working.

    This code is in Access but you open a Workbook to merge with Word?
    I use merge from access data from access form. Not from workbook.

    Where exactly do you want "Done" to update - field in Access table?
    I want to update "done" in access table field. Field name is "Remarks".

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,376
    If Excel is not datasource then suggest using different variable name.

    If you want to update all records in table:

    CurrentDb.Execute "UPDATE GteeTrayTbl SET Remarks = 'Done'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    joshynaresh is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Aug 2013
    Posts
    131
    I don't want to update all repords in table. I want to update those record only which I have selected to mail merge.

    With .DataSource .FirstRecord = InputBox("Enter the First Record # to merge", , 1)
    .LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount) ' replace 2o to .RecordCount to get record count
    End With
    record should be selected from above code. Other record should not be changed.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,376
    I don't see any filter criteria on datasource: "SELECT * FROM [GteeTrayTbl]"

    You use .FirstRecord and .LastRecord properties to define set of records to merge. Why don't you instead restrict dataset retrieved?

    SELECT * FROM GteeTrayTbl WHERE ID BETWEEN " & [input here] & " AND " & [input here]

    You need same filter criteria for UPDATE action.

    CurrentDb.Execute "UPDATE GteeTrayTbl SET Remarks = 'Done' WHERE ID BETWEEN " & [input here] & " AND " & [input here]

    Instead of popup InputBox, advise comboboxes on form. Easier to validate user input.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  2. Replies: 6
    Last Post: 06-24-2016, 06:07 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

Posting Permissions

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