Thank you Bill and all. I finally located where caused the error 3211. It's because I opened the recordset and
Case 3010
Resume Next
Case Else
'Do something else or display msg
Resume err_PROC.
End Select
--- In ms_access_professionals@yahoogroups.com, <liz_ravenwood@...> wrote:
Good coding. You are the master. Master Bill!
p.s. How many times have you heard "mister Bill?"
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808
|
beaerospace.com
Passion to Innovate. Power to Deliver
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@...
Sent: Thursday, September 12, 2013 8:53 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: [MS_AccessPros] RE: Run-time error '30 10' Tab le 'tblName' already exist
HI Liz
I still don't use On Error Resume next simply because I want to be sure to trap anything out of the expected. What I do is use a Select Case statement in my error trap like this:
Select Case Err.Number
Case 3010
Resume Next
Case Else
'Do something else or display msg
Resume err_PROC.
End Select
Bill
--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:
I use it before I'm going to delete a temporary table that may or may not be there.
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
beaerospace.com
Passion to Innovate. Power to Deliver
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jpjones23@...
Sent: Wednesday, September 11, 2013 9:47 AM
To: MS Access Professionals
Subject: Re: [MS_AccessPros] RE: Run-time error '3010' Tab le 'tblName' already exist
Phucon,
I totally agree with Bill.
I have an additional suggestion related to the "On Error Resume Next" error handling. I know that there are folks who adamantly insist that "one should never use resume next" however there are real world situations where it makes sense. It always tickles me when these "absolute" statements are made because they have always, from my experience, proved to be a bit confining.
When I use the convention, it's because I expect a possible error that I want to handle in line because it doesn't make sense to me to jump somewhere only to jump back to the next line in the code. From a maintenance perspective, trailing the jump back and forth just takes time that isn't needed if the line of code that might throw the error is immediately tested for the expected error. Here's a snippet of code reflecting what I mean.
On Error Resume Next
fso.CopyFile (strSourceFile), strDestFile, True
If Not Err = 0 Then
Set fso = Nothing
Exit Sub
End If
On Error GoTo HandleError
This was a quick and dirty example I put together in an attempt to help another with some file handling. In a production mode, I'd test for the error number that reflects the possible failure and if I didn't get it or a 0, I'd bring the fact to the user's attention. In this example, I just wanted to stop as it was my test. If the copy was successful, additional code was executed to delete the source file. If it wasn't, then I didn't want to delete the source file because I'd have to recreate it to get the backup.
So, with 30+ years of coding experience I recommend that you do what makes sense but cover your "database" at the same time. If I get any other error than the possible expected error, as a developer I need to know because it's then obvious that I need to take care of the unexpected data or task.
Thus endth my 02 cents,
Jeff
From: wrmosca@...
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 11, 2013 10:45:32 AM
Subject: [MS_AccessPros] RE: Run-time error '3010' Tab le 'tblName' already exist
Phucon
First thing to do is to comment out all your On Error lines so you can debug the code. Then put a break point on this line:
If Not BuildResultsTable(strSql, "tblResults", lngRecAffected) Then
When the code breaks press F8 to step through each line so you can see what is going on. If the error occurs on the table Delete line just move the highlighter down to the next line of code and continue pressing F8.
Just some advice...
Dim your DAO objects like this to prevent ambiguity:
Dim db As DAO.Database
Dim qdfAction As DAO.QueryDef
And you might want to use an SQl statement to delete the table. I don't think it matters, but it might help.
db.Execute "DROP TABLE " & strTableName
--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:
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
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar