kbay808
asked on
How to modify my VBA code that can only target 1 row at a time to keep running for each additional row that is not empty?
I have an Access table that I need to update. The author of the DB created a sub that I can call with the info that needs to be added. The code works, but I can only do one record at a time. I’m trying to figure out how to run the code for row 2, and then if cell A3 is not blank run the code for row 3 and so on until it gets to a row where the cell in column A is blank.
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
ASKER
The result needs to look like this.
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
ok -- in Excel, it would be good to make the result something meaningful. Currently, you do not even need it -- you could use CALL instead or not surround arguments with parentheses since there is not anything returned ... however, there could be.
In Access, in the RecreateTool_AddRecord code, Make it a function and return a boolean value of true or false
Error handling could also be added. To learn more:
1. basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
2. Run and Fix Code Loop through rows of an Excel spreadsheet using VBA (6:00)
https://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html
3. Error Handling Part 3 - Run and Fix Bugs (7:51)
https://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html
In Access, in the RecreateTool_AddRecord code, Make it a function and return a boolean value of true or false
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 lineError handling could also be added. To learn more:
1. basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
2. Run and Fix Code Loop through rows of an Excel spreadsheet using VBA (6:00)
https://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html
3. Error Handling Part 3 - Run and Fix Bugs (7:51)
https://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html
ASKER
I'm sorry, but I don't have any control over the Access DB. Any changes would have to be in Excel.
it was only a suggestion ... the code I gave you should work fine. Did you try it?
ASKER
ASKER
I was thinking since the original code works for just 1 row/record, a workaround could be for the code to run for the first row (row 2) and then delete the row and then if cell A2 is not blank then run the code again and again until cell A2 is blank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I created my own solution and posted it.
Open in new window
what do you plan to do with the Result ? It is calculated but goes nowhere ...