Skip recordset in loop and continue to next

Question

I inherited an Access program that reads Excel files in a directory and brings them in to Access in a For/Each Ofile loop using DoCmd.TransferSpreadsheet acLink and Set rs1=db.openrecordset.

It then goes into a loop: Do While not rs1.EOF.

In the loop it immediately checks the first two fields (ID and ID2) for correct formatting.

The previous code would just cancel out of the entire loop if there were any errors in the formatting and skip to the top of the For/Each loop and read the next spreadsheet.

I would like to test those two fields but skip to the top of the Do While loop if there is no error. I can't seem to do this. So I want to skip the recordset that is in error but read the next one.

My code is below. I've skipped the For/Each loop since it just reads the next Excel file. The following code is within the For/Each loop.

set rs2 = db.openrecordset("tblIn_Access")

source="C:\Documents\Test_for_import\" & oFile.name
doCMD.TransferSpreadsheet acLink, 10, ofile.name, source, true

Set rs1=db.openrecordset("Select * from " & ofile.name & " ;")

Do While not rs1.eof
   IDVal=rs1.fields(0).value
   ID2Val=rs1.fields(1).value

   if len(idVal) < 9 then
       'Here is where I want to stop and read the next record in the spreadsheet.
       ' I don't want any more processing to occur
   if left(id2Val) = "H" then
       'This is another case when I want to stop and read the next record
       ' and not do any more processing on the record

   'If these two errors do not occur then I want to continue with the next record
   'There is a lot of data manipulation here to get fields in the right format.
   ' Once they are correct I add the values to a table in Access
 rs2.AddNew
 rs2.("MBR_ID")=idval
 rs2.("ORD_ID")=id2val
 rs2.("DATE_IN")=dateinval
 rs2.("DATE_OUT")=dateoutval
 rs2.Update
 rs2.movenext

 rs1.movenext

Loop

I can't get the processing to stop on the first two fields and go back and read the next record if they are not correctly formatted. The only thing I've been able to do is what the code originally did, stop and read in the next Excel sheet. There is a lot of manipulation in the code following the checks on ID & ID2 but I only want that code to run if the two fields are in the proper format.

LeeG · Answer 1 · 2018-06-18T19:50:30.913

0

This is time for the otherwise unused 'GOTO command.

set rs2 = db.openrecordset("tblIn_Access")

source="C:\Documents\Test_for_import\" & oFile.name
doCMD.TransferSpreadsheet acLink, 10, ofile.name, source, true

Set rs1=db.openrecordset("Select * from " & ofile.name & " ;")

Do While not rs1.eof
   IDVal=rs1.fields(0).value
   ID2Val=rs1.fields(1).value

   if len(idVal) < 9 then GOTO SkipRecord

   if left(id2Val) = "H" then GOTO SkipRecord

 rs2.AddNew
 rs2.("MBR_ID")=idval
 rs2.("ORD_ID")=id2val
 rs2.("DATE_IN")=dateinval
 rs2.("DATE_OUT")=dateoutval
 rs2.Update
SkipRecord:
 rs2.movenext

 rs1.movenext

Loop

edited Jun 18 '18 at 19:50

answered Jun 18 '18 at 15:57

LeeG

708
5
14

Please don't use `GoTo` instead of a normal conditional statement. See things like [this post](https://stackoverflow.com/q/3517726/7296893). You can just as easily use `End If`. And you've even used it wrong, it's `SkipRecord:` and not `:SkipRecord`. – Erik A Jun 18 '18 at 18:19
Shows how much I use GoTo doesn't it. – LeeG Jun 18 '18 at 19:49

Skip recordset in loop and continue to next

1 Answers1