Kemidan2014
Board Regular
- Joined
- Apr 4, 2022
- Messages
- 226
- Office Version
- 365
- Platform
- Windows
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
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
VBA Code:
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
