Sub modIncTool()
On Error Resume Next
Dim objAcc As Object
theOriginalQuote = Sheets("Import").Range("A2")
theNewQuote = Sheets("Import").Range("B2")
theSiteGroup = Sheets("Import").Range("F2")
theTechnician = Sheets("Import").Range("G2")
theReason = Sheets("Import").Range("H2")
theDateRequested = Sheets("Import").Range("I2")
Set objAcc = GetObject(, "Access.Application")
Result = objAcc.run("RecreateTool_AddRecord", theOriginalQuote, theNewQuote, theSiteGroup, theTechnician, theReason, theDateRequested)
Set objAcc = Nothing
End Sub
Result = objAcc.run("RecreateTool_AddRecord", theOriginalQuote, theNewQuote, theSiteGroup, theTechnician, theReason, theDateRequested)
The result is calling for the below sub to run in the Access DB. Public Sub RecreateTool_AddRecord( _
ByVal theOriginalQuote As String, _
ByVal theNewQuote As String, _
ByVal theSiteGroup As Long, _
ByVal theTechnician As String, _
ByVal theReason As String, _
ByVal theDateRequested As String)
sSQL = "INSERT INTO [Quote Recreation Tracker] ([Original Quote#], [New Quote#], [Status], " & _
"[Requesting Technician Site Group], [Requesting Technician User Name], [Reason for Request], " & _
"[Date Requested], [Date Completed]) " & "VALUES ( '" & _
theOriginalQuote & "', '" & theNewQuote & "', 'Approved', " & theSiteGroup & ", '" & _
theTechnician & "', '" & theReason & "', #" & theDateRequested & "#, #" & Date & "#);"
CurrentDb.Execute sSQL, dbFailOnError
End Sub
Public Function RecreateTool_AddRecord( ...) as boolean
then instead of "CurrentDb.Execute sSQL, dbFailOnError"
with CurrentDb
.Execute sSQL, dbFailOnError
if .RecordsAffected >0 then
RecreateTool_AddRecord =true
else
RecreateTool_AddRecord =false
end if
end with 'currentdb
then in Excel:
if objAcc.Run( _
"RecreateTool_AddRecord" _
, theOriginalQuote _
, theNewQuote _
, theSiteGroup _
, theTechnician _
, theReason _
, theDateRequested _
) <> true then
if msgbox ("Error occurred on row " & nrow _
& vbcrlf & "Do you want to stop?" _
vbyesno, "Error -- stop?") = vbyes then
go to proc_exit
end if
end if
proc_exit would then be a line label added above the statement to release the Access object, which would be done after the message box
MsgBox "Did up to row " & nRow - 1, , "Done"
Proc_Exit:
on error resume next
Set objAcc = Nothing
space underscore at the end of a line means the statement is continued on the next line
Open in new window
what do you plan to do with the Result ? It is calculated but goes nowhere ...