Recordset with selected records

[abenitez77]

I have a table with a column named VendType and it has 3 values ("FS", "EXP, "DSD"). I want to export the records into 3 separate spreadsheets by VendType. I also only want to export the rows that I select from a grid on the form I am on. The form could be filtered and possibly have rows selected that are not contiguous. This is how I am currently doing it, but I need to modify it to only export selected records :

Set FrmDetail = Me.Parent.SubForm.Form



   For i = 1 To 3



       Select Case i

              Case 1

                  vType = "FS"

              Case 2

                  vType = "EXP"

              Case 3

                  vType = "DSD"

              Case Else

       End Select



       FrmDetail.Filter = "VendType = '" & vType & "'"

       FrmDetail.FilterOn = True



       Set xRS = CreateObject("ADODB.Recordset")

       Set xRS = FrmDetail.RecordsetClone

       set filRS = CurrentDB.OpenRecordset("Select

     

       If xRS Is Nothing Then

          MsgBox "There are no claims to upload.", vbExclamation, "Claims Upload"

          GoTo cmdUploadExit

       End If

   

       DoCmd.Hourglass True

   

       sUploadDate = Format(Date, "yyyy-mm-dd")

   



       'Create Spreadsheet for RiteAid

       If Not CreateExcelFile(sUploadDate, vType) Then

          bUploadSuccess = False

          MsgBox "Upload Excel file creation failed!" & vbCrLf & vbCrLf & "Text file NOT created and ClaimsPlus NOT updated!", vbCritical, "Claims Upload"

          GoTo cmdUploadExit

       End If





Private Function CreateExcelFile(sUpdateDate As String, vType As String) As Boolean

On Error GoTo ErrorHappened

    Dim xRS

    Dim loExcel As Object, loBook As Object, loWorkSheet As Object

    Dim iRow As Integer

    Dim sTemplateFile As String

    Dim sSpreadsheetFile As String

    Dim sUpdateDate2 As String

    Dim lsLineEntity As String

   

    SysCmd acSysCmdSetStatus, "Claims upload creating spreadsheet file..."



    DoCmd.Hourglass (True)



    CreateExcelFile = False



    Set xRS = CreateObject("ADODB.Recordset")

    Set xRS = FrmDetail.RecordsetClone



    If xRS Is Nothing Then

       MsgBox "There are no claims to upload.", vbExclamation, "Claims Upload"

       GoTo CreateExcelFileExit

    End If

   

    sUpdateDate2 = "_" & sUpdateDate



    'Get the template spreadsheet to work with

    sTemplateFile = "\\ccaintranet.com\dfs-fld-01\Audits\Riteaid\Decipher\ReportTemplates\RiteAidClaimsUploadTemplate.xlsx"



    'Build excel spreadsheet

    sSpreadsheetFile = "\\ccaintranet.com\DFS-FLD-01\Users\" & Identity.UserName & "\RiteAidClaimsUpload_" & vType & sUpdateDate2 & ".xlsx"



    'Copy the template to the destination path/filename

    FileCopy sTemplateFile, sSpreadsheetFile





    'Create the Excel object

    Set loExcel = CreateObject("Excel.application")

    With loExcel

        'Open the Excel file

        .Application.Workbooks.Open sSpreadsheetFile



        Set loBook = loExcel.Workbooks(1)

        Set loWorkSheet = loBook.Worksheets(1)

               

        loWorkSheet.Activate



        iRow = 2

        xRS.MoveFirst

        While Not xRS.EOF

            SysCmd acSysCmdSetStatus, "Claims upload creating spreadsheet file...Claim: " & Nz(xRS.FullClaimNum, "")

            loWorkSheet.Range("A" & CStr(iRow)).Value = Nz(xRS.FullClaimNum, "")

            loWorkSheet.Range("B" & CStr(iRow)).Value = Format(xRS.ClaimDate, "mm/dd/yyyy")

            loWorkSheet.Range("C" & CStr(iRow)).Value = Nz(xRS.VenNum, "")

            loWorkSheet.Range("D" & CStr(iRow)).Value = Nz(xRS.VenName, "")

            loWorkSheet.Range("E" & CStr(iRow)).Value = Nz(xRS.NetAmt, 0)

            loWorkSheet.Range("F" & CStr(iRow)).Value = Nz(xRS.ClaimCodeClient, "")

            loWorkSheet.Range("G" & CStr(iRow)).Value = Nz(xRS.ClaimCodeClientText, "")

            xRS.MoveNext

            iRow = iRow + 1

        Wend



        loWorkSheet.Columns("A:G").EntireColumn.AutoFit

   

        'show the excel object

        loExcel.Application.visible = True

    End With



    ' Save the workbook

    loExcel.ActiveWorkbook.Save



    ' Close the workbook without saving

    loBook.Close False

   

    CreateExcelFile = True



CreateExcelFileExit:

    On Error Resume Next

    xRS.Close

    Set xRS = Nothing

    Set loExcel = Nothing

    Set loBook = Nothing

    Set loWorkSheet = Nothing

    Exit Function



ErrorHappened:

    MsgBox "Error creating upload spreadsheet file!" & vbCrLf & vbCrLf & sSpreadsheetFile & vbCrLf & vbCrLf & err.Description, vbCritical, "cmdUpload_Click - CreateExcelFile Error"

    CreateExcelFile = False

    Resume CreateExcelFileExit





End Function

 

[Minty]

Could you edit that to include the code in Code Tags and indent it?
Press the </> button in the editor.

 

[theDBguy]

Hi. What exactly do you mean by "selected" records? How are the records being selected?

 

[abenitez77]

Hi. What exactly do you mean by "selected" records? How are the records being selected?

I have a form that has a grid and in that grid, rows are being selected.

 

[theDBguy]

I have a form that has a grid and in that grid, rows are being selected.

Can you please post a screenshot of that form with some records selected? Are records selected with gaps or always together?

 

[MajP]

You are currently doing this in a pretty convoluted way. You loop the VTYPEs then use that to filter the form then pull then set the recordset to the filtered forms recordset.

I assume you have a Selected field in the form to allow you to select a record.

I would skip the filtering and just build each RS in code.

Dim xrs as dao.recordset
set xrs = currentDb.openRecordset ("Select * from somequery where Vendtype = 'FS' and SelectedField = True")
Process these records pass xRS to module to create sheet
Set xRS = currentDb.openRecordset ("Select * from somequery where Vendtype = 'EXP' and SelectedField = True")
process these records
Set xRS = currentDb.openRecordset ("Select * from somequery where Vendtype = 'DSD' and SelectedField = True")

FYI forms return DAO recordsets so that code for creating an ADO RS does nothing.

 

[abenitez77]

Can you please post a screenshot of that form with some records selected? Are records selected with gaps or always together?

 

[abenitez77]

You can see the column is selected on a filtered set of records:

 

[abenitez77]

You are currently doing this in a pretty convoluted way. You loop the VTYPEs then use that to filter the form then pull then set the recordset to the filtered forms recordset.

I assume you have a Selected field in the form to allow you to select a record.

I would skip the filtering and just build each RS in code.

Dim xrs as dao.recordset
set xrs = currentDb.openRecordset ("Select * from somequery where Vendtype = 'FS' and SelectedField = True")
Process these records pass xRS to module to create sheet
Set xRS = currentDb.openRecordset ("Select * from somequery where Vendtype = 'EXP' and SelectedField = True")
process these records
Set xRS = currentDb.openRecordset ("Select * from somequery where Vendtype = 'DSD' and SelectedField = True")

FYI forms return DAO recordsets so that code for creating an ADO RS does nothing.

I agree with you on the approach, but the "SelectedField" is basically just the user highlighting the rows or the column which then hightlights the rows (in this case only the filtered records on the form). So there's no way for me to identify them and add it to the query.

 

[MajP]

The form could be filtered and possibly have rows selected that are not contiguous

That is not possible unless you change the form into a multiselect listbox or you need to add a field to the form. Selecting the column does nothing.

You might be able to select a group. I have seen it done but it is pretty clunky and relies on selection height.

 RS.MoveFirst
   
     ' Move to the first selected record.
     RS.Move F.SelTop - 1
   
     ' Enumerate the list of selected records presenting
     ' the CompanyName field in a message box.
     For i = 1 To F.SelHeight
        RS.Edit
            If Nz(RS!Active, False) = False Then
                RS!Active = True
            Else
                RS!Active = False
            End If
        RS.Update
        RS.MoveNext
     Next i

 

[theDBguy]

You can see the column is selected on a filtered set of records:

View attachment 95658

Hi. Thanks, but I can't tell from your screenshot which records are selected. I was expecting something more like this:

Also, you didn't answer my other question. Does this mean the user will only ever select contiguous records (no gaps)?

 

[abenitez77]

Hi. Thanks, but I can't tell from your screenshot which records are selected. I was expecting something more like this:

View attachment 95659

Also, you didn't answer my other question. Does this mean the user will only ever select contiguous records (no gaps)?

The user may select the records how you are showing it as well. If he wants to select all, he may select the column, but I can require them to select it the way you are doing it, if it helps. I expect it will be contiguous 95% of the time, but there may be an occasion where it won't be.

 

[MajP]

can you post a screen shot of how you are planning to do an non contiguous selection? Never seen it done.

 

[MajP]

If he wants to select all, he may select the column

Selecting a column is the same as not selecting the column. All records are selected. What is the purpose of selecting a column?

 

[abenitez77]

Selecting a column is the same as not selecting the column. All records are selected. What is the purpose of selecting a column?

My guess is, the user thinks he's selecting all rows by selecting the column, instead of going down and selecting the record selector where it selects the entire row. But I see your point...that does not select the actual record. I just need to educate the user on the correct way.

 

[LarryE]

The only way I have ever heard of that allows the selection of various different records is to add a field to your table with a Data Type of Yes/No. You could call it "Select" if you like. Then add a checkbox control to your form and bind it to the Select Field. That way the user can select various records at will using the checkbox. A query can then be created that contains only those selected records and then exported from that query to EXCEL. Otherwise, ACCESS doesn't know which records you want exported. You might be better off not exporting anything to EXCEL and just using ACCESS to do your analysis and reporting. It's pretty powerful in its own right.

 

[abenitez77]

Ok, so let's say I just wanted to export all the records that are filtered on the form? not the selected rows....

 

[theDBguy]

Ok, so let's say I just wanted to export all the records that are filtered on the form? not the selected rows....

One way to do that is to assign the RecordsetClone of the form to a Recordset object and then use the CopyFromRecordset method using Excel Automation.

 

[LarryE]

Ok, so let's say I just wanted to export all the records that are filtered on the form? not the selected rows....

Then the queries criteria is all the forms records. You could also include a checkbox control on the form that is NOT bound, but when checked True, it runs an UPDATE query that sets all the forms Select fields to True. When you Requery the form, all the checkboxes on the form would be set to True (-1). Then the export records query would include them all.