Results 1 to 9 of 9
  1. #1
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42

    VBA to export subform qry data to excel starting

    Hi all,


    For my students database, as they carry out an examination, the record their DNA Samples that they have taken in a related table.

    When my students are finished their examination, I expect them to check all the DNA sample numbers and then I want them to press a button and then those samples number, date, examination room, and submit by should be copied to a predefined excel spreadsheet...

    I thought my code would work, its opens the desired excel file with no issues but then I keep getting a run time error '91' Object variable or With Block variable not set..... Meaning my set command above is is wrong???? But there is nothing wrong with it !

    Can you check my code and see what's going on..

    Code:
    Dim clipboard As MSForms.DataObject
        Set clipboard = New MSForms.DataObject
        clipboard.SetText [PDFDNASampleREcord]
        clipboard.PutInClipboard
    
    
    Dim xl As Excel.Application
    Set xl = New Excel.Application
    xl.Visible = True
    
    
    xl.Workbooks.Open ("\\C:\Desktop\DNA Sample Record.xlsx")
    Dim ws As Worksheet
    
    
    'Set ws = xl.ActiveWorkbook.Sheets("sheet 1")
    'ws.Select
    
    
    Dim db As DAO.Database, rownum As Long
    Set db = CurrentDb
    
    
    Dim rs As DAO.Recordset
    Set rs = Me.fsub_results_dnasamplerecord.Form.RecordsetClone
    rownum = 14
    
    
    rs.MoveFirst
    
    
    Do While Not rs.EOF
    
    
        ws.Cells(rownum, 1).Value = rs.Fields("DNASampleNumber").Value
        ws.Cells(rownum, 4).Value = rs.Fields("ExaminationRoom").Value
        ws.Cells(rownum, 7).Value = rs.Fields("Dateofexamination").Value
        ws.Cells(rownum, 10).Value = rs.Fields("DNASRSubmitby").Value
            rownum = rownum + 1
            
    rs.MoveNext
    Loop
    
    
    
    
    End Sub
    See image for more info

    Click image for larger version. 

Name:	Annotation 2022-08-15 120606.jpg 
Views:	16 
Size:	105.0 KB 
ID:	48512

    What am I doing wrong????

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why is the Set ws line commented?
    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
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    Because I realised I didn't need it as there is only 1 sheet in excel. I was just testing it on and off haha

  4. #4
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    So I have actually been experimenting / researching on this for the last few hours and I think I have made progress but there is a problem...

    See my new code....

    Code:
    Private Sub cmd_opndnasample_Click()
    
    
    Dim xl As Excel.Application
    Set xl = New Excel.Application
    
    
    xl.Visible = True
    
    
    xl.Workbooks.Open ("\\C:\Desktop\Abilas DNA Sample Record.xlsx")
    
    
    Dim ws As Worksheet
    Set ws = xl.ActiveWorkbook.Sheets("Sheet1")
    ws.Select
    
    
    Dim db As DAO.Database
    Set db = CurrentDb
    
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("qry_results_DNAsamplerecord")
    rownum = 14
    
    
    rs.MoveFirst
    
    
    Do While Not rs.EOF
    
    
        ws.Cells(rownum, 1).Value = rs.Fields("DNASampleNumber").Value
            rownum = rownum + 1
    
    
    rs.MoveNext
    Loop
        
    
    
    End Sub
    I don't get any errors ! And it passes the data to correct spot on the excel spreadsheet! But it copies EVERY record in qry_results_DNAsamplerecord to the excel spreadsheet - which is currently hundreds and is expanding!!!!!!!!

    Is there a way to filter the recordset to only show records in the qry based on the current primary key number on the loaded form where I press the button to generate the DES? So say my form's primary key called [HubJobID] is 79..... Is there a way to add to my new code to open the recordset "qry_results_DNAsamplerecord" but filter only records with Me!HubJobID = 79 (current hub job id on the form)..... And then when another student loads the form with their HubJobID which will be a different number obviously, when they press the button it will open the recordset "qry_results_DNAsamplerecord" but filter only records Me!HubJobID = 161 (example).... Surely there must be a way to open a filter record set? I can't find anything

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Well it is only doing what you tell it to do?
    So make the recordset a select from your query where ID = me.hubjobid
    You will need to concatenate hubjobid with the select statement.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Instead of looping recordset, consider CopyFromRecordset method.
    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.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by June7 View Post
    Instead of looping recordset, consider CopyFromRecordset method.
    Much better idea, however you still need to get the source correct though.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    How do I do that??

    Can you show me an example of what that looks like?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Set rs = db.OpenRecordset("SELECT DNASampleNumber FROM qry_results_DNAsamplerecord WHERE HubJobID =" & Me!HubJobID)

    ws.Range("A14").CopyFromRecordset rs
    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: 5
    Last Post: 02-01-2020, 05:27 AM
  2. DB Design starting first model , with Matrix data from Excel
    By warlock916 in forum Database Design
    Replies: 6
    Last Post: 01-11-2016, 01:47 AM
  3. export data from subform to excel
    By raffi in forum Import/Export Data
    Replies: 2
    Last Post: 11-02-2014, 03:40 PM
  4. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  5. export to excel starting from Cell A2
    By ice051505 in forum Programming
    Replies: 7
    Last Post: 04-25-2013, 03:15 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