Recordset with selected records (1 Viewer)

abenitez77

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
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 :

Code:
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
 
Last edited:

Minty

AWF VIP
Local time
Today, 03:28
Joined
Jul 26, 2013
Messages
9,068
Could you edit that to include the code in Code Tags and indent it?
Press the </> button in the editor.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:28
Joined
Oct 29, 2018
Messages
17,577
Hi. What exactly do you mean by "selected" records? How are the records being selected?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:28
Joined
Oct 29, 2018
Messages
17,577
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've got your good things, and you've got mine.
Local time
Yesterday, 22:28
Joined
May 21, 2018
Messages
5,902
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.
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

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
You can see the column is selected on a filtered set of records:

Selected Records.png
 

abenitez77

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
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.
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

You've got your good things, and you've got mine.
Local time
Yesterday, 22:28
Joined
May 21, 2018
Messages
5,902
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.
Code:
 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

I’m here to help
Staff member
Local time
Yesterday, 19:28
Joined
Oct 29, 2018
Messages
17,577
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:

1635359391893.png


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

abenitez77

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
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

You've got your good things, and you've got mine.
Local time
Yesterday, 22:28
Joined
May 21, 2018
Messages
5,902
can you post a screen shot of how you are planning to do an non contiguous selection? Never seen it done.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:28
Joined
May 21, 2018
Messages
5,902
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

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
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

Member
Local time
Yesterday, 19:28
Joined
Aug 18, 2021
Messages
59
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

Registered User.
Local time
Yesterday, 22:28
Joined
Apr 29, 2010
Messages
135
Ok, so let's say I just wanted to export all the records that are filtered on the form? not the selected rows....
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:28
Joined
Oct 29, 2018
Messages
17,577
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

Member
Local time
Yesterday, 19:28
Joined
Aug 18, 2021
Messages
59
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.
 

Users who are viewing this thread

Top Bottom