I have a Search Form that calls the function (which I copied from a book) as shown below. It generates a "Run-time error '3010' Table 'tblName' already exist." every other time. e.g.(the 1st time ok, the 2nd time will be an error, then the 3rd time ok again then the 4th will be error…) the function's called.
Logically it will delete the table each time the function is called, is that right? What caused it to skip only deletes the table every other time?
Phucon.
Function BuildResultsTable(strSql As String, strTableName As String, lngRecAffected As Long)
Dim db As Database
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next 'ignore any erors that may occur when deleting a table.
db.TableDefs.Delete strTableName
Debug.Print strTableName
On Error GoTo 0 'ignore errors in any subsequent lines of code.
strSql = Replace(strSql, " FROM ", " INTO " & strTableName & " FROM ")
Debug.Print strSql
Set qdfAction = db.CreateQueryDef("", strSql)
qdfAction.Execute dbFailOnError
lngRecAffected = qdfAction.RecordsAffected
qdfAction.Close
BuildResultsTable = True
End Function
Private Sub cmdFind_Click()
Dim strSql As String
Dim lngRecAffected As Long
If Not BuildResultsTable(strSql, "tblResults", lngRecAffected) Then
MsgBox "There was a problem building the SQL string", vbInformation, "Error"
Exit Sub
End If
Set rs = CurrentDb.OpenRecordset("qryResults", dbOpenSnapshot)
If Not rs.RecordCount = 0 Then
DoCmd.OpenForm FormName:="frmPledgeeShowData2", view:=acNormal
Else
If MsgBox(cErrMsg & Left$(Mid$(TempVars!stringwhere, 8), 15) & ". Add new record?", vbYesNo + vbExclamation, "Find Record") = vbYes Then
DoCmd.OpenForm FormName:="frmPledgeeDataEntry2", view:=acNormal
Else
Exit Sub
End If
End If
End Sub
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar