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
What am I doing wrong????