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

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
226
Office Version
  1. 365
Platform
  1. 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

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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
Solution
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top