Looping issue

[DanWallace]

Ok, so I'm a little new to this but here's what I'm doing:

At the beginning of my code I'm dim'ing my database (db1) and recordsets. rsTable is a recordset I use to go through each table and check for missing fields and other errors. rs2 and 3 are used to update my two error tables. At the beginning of my function I set them like so, so that I can update them as I go:

Set db1 = CurrentDb
    
strSQL2 = "SELECT * FROM [tblErrorRecords]"
    Set rs2 = db1.OpenRecordset(strSQL2)

strSQL3 = ("SELECT * FROM [tblErrorStats]")
    Set rs3 = db1.OpenRecordset(strSQL3)

Then at the beginning of each section (each section checks one table), I do this:

strSQL1 = ("SELECT * FROM [tablename]")
    Set db1 = CurrentDb
    Set rsTable = db1.OpenRecordset(strSQL1)
    rsTable.MoveFirst

And then of course I have some looping logic to step through the recordset and check my data and then I update both of my error tables as I go like:

rs3.AddNew
    rs3.Error_Code = 1
    rs3.Number_of_Records = counter
    rs3.Error_Description = "tablename - field Null"
    rs3.Criteria1 = count1
    rs3.Criteria2 = count2
rs3.Update

Anyways, that all works fine. But this is meant to be a broad function that should be portable. I should be able to load this function into another db with the same tables and go. The problem is that all the tables aren't always in each db I put the function in and the function seems to go haywire and loop eternally if it gets to a spot where the table it's trying to query is missing.

If I don't put a db1.close at the end of each "section" then it will still go through, but it seems to update using the previous recordset if it's missing that table. Is there any easy way to say "does this table exist?" and if so, skip this section and go on to the next?

Cheers.

 

[MarkK]

Here's a function that'll tell you if a table exists

[FONT="Verdana"]Function TableExists(tablename As String) As Boolean
   On Error Resume Next
   TableExists = CurrentDb.TableDefs(tablename).Name = tablename
End Function
[/FONT]

 

[DanWallace]

Here's a function that'll tell you if a table exists

[FONT=Verdana]Function TableExists(tablename As String) As Boolean
   On Error Resume Next
   TableExists = CurrentDb.TableDefs(tablename).Name = tablename
End Function
[/FONT]

Thanks! I don't exactly get how it works, but it works! lol