Using DAO.Database and DAO.Recordset to copy table data

[Kemidan2014]

I am working on a macro to copy field information for the current record selected (in a continuous form) over to an excel spreadsheet to make it easier on my team to prefill an excel form.

This was an added request after I had since split the databased etc. So i wanting to not also have to modify the Query or the form. I have done data moves to excel multiple times but my usual strategy is to use Me.fieldname since i need to pull info related to the record that is not in the QUERY record set but still in my main table associated with the key field. i thought using DAO.Database would have worked but i get an compile error (added message in code below)

was i wrong to employ this method in a continuous form? or is it OK to do this but my syntax is wrong

Private Sub rpvcard_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strval As String
Dim excelapp As Object
Set excelapp = CreateObject("Excel.application")
Dim wb As Object
Dim path As String
Dim savepath As String
Dim qimsnum As String

savepath = "O:\1_All Customers\Current Complaints\Complaint Folders\"

Set db = CurrentDb()
Set rs = db.OpenRecordset("MainData")
path = "O:\1_All Customers\Current Complaints\Old Dashboards\Blank Scorecard DO NOT TOUCH.xlsx"
Set wb = Excel.Workbooks.Open(path)
qimsnum = rs.[QIMS#] '<-- it fails here Compile Error Method or Data member not found

If FileExists(savepath & qimsnum & "RPV" & ".xlsx") Then
 MsgBox "RPV Card already exists!"
 Exit Sub
End If

wb.worksheets("Response Scorecard").Range("B8") = rs.[PartName]
wb.worksheets("Response Scorecard").Range("B8:C9").Merge

wb.worksheets("Response Scorecard").Range("B14") = rs.[Auditor]
wb.worksheets("Response Scorecard").Range("B14:C14").Merge

wb.worksheets("Response Scorecard").Range("h8") = rs.[QIMS#]
wb.worksheets("Response Scorecard").Range("h8:j9").Merge

wb.worksheets("Response Scorecard").Range("B10") = rs.[Qty]
wb.worksheets("Response Scorecard").Range("B10:C11").Merge

wb.worksheets("Response Scorecard").Range("B16") = rs.[Defect]
wb.worksheets("Response Scorecard").Range("B16:C17").Merge

wb.worksheets("Response Scorecard").Range("H4") = rs.[NAMC]
wb.worksheets("Response Scorecard").Range("H4:L5").Merge

wb.worksheets("Response Scorecard").Range("H10") = rs.[OfficialIssuanceDate]
wb.worksheets("Response Scorecard").Range("H10:L11").Merge

wb.worksheets("Response Scorecard").Range("H12") = rs.[LTCMPlanSubmitted]
wb.worksheets("Response Scorecard").Range("H12:L13").Merge

wb.SaveAs (savepath & qimsnum & "RPV" & ".xlsx")
wb.Close

If MsgBox("RPV score card created, Would you like to open?", vbYesNo) = vbYes Then
 excelapp.Workbooks.Open (savepath & qimsnum & "RPV" & ".xlsx")
 excelapp.Visible = True
End If

End Sub

 

[Micron]

Could be that you don't have a reference set to DAO but that would depend on your Access version. 2016 and up, you shouldn't need to. That leaves me with the notion that the issue is the recordset field reference. Try removing the brackets [ ] - however the # character may be an issue. You should never use special characters or spaces in object names (save for underscore, which I never use). If no brackets and the # still raises that error, try this syntax: rs.Fields("QIMS#").

Seems to me that you took the hard way if you could have avoided recordset code by altering the query sql. You can have as many query fields in a form recordset as you want and don't have to use all of them on the form.

 

[Kemidan2014]

if i add fields to existing query but uncheck them ot be hidden will i still be able to reference the field then?
ive had success in the past using Me.[QIMS#] this field is shown in my form but does Me.[fieldname] work with continuous forms?

 

[Kemidan2014]

Micron, so i ultimately took your advice atleast i think i took it the right way and altered the query (which i was hoping not to do) but that did ultimately did the trick and i did not need to utilize DAO in the end. everything works the way i had intended it too.

 

[Micron]

Glad I could help & thanks for the recognition. As for the 1st q, I don't think so. #2, Me.FieldName on a continuous or datasheet form will return the value for the current (active/selected) record.