Full Version: Capture Errors In Duplication Routine
UtterAccess Forums > Microsoft® Access > Access Forms
With Thanks to Leigh Purviss who got me straight yesterday, I have a small enhancement I'm seeking that my feeble brain cannot fathom...
eigh has given me some code to allow me to create duplicate records from a subform. However, my data entry chap has discovered that if he accidentally hits this button without selecting a record, it causes a runtime error and closes the database (a packaged solution).
I want to have a msgbox that advises that no record has been selected, but because I'm a few percent short of understanding exactly how the code works, I can't work out how to call the box, and what to do with the response. The code is here:
Function fCopyRecord(strTable As String, varPKVal) As Long
'Copies a record in a specified table
'Accepts table name and Primary key value.
'Currently assumes a single PK field
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim strPKName As String
Dim strFields As String

Set db = CurrentDb
Set tdf = db(strTable)

For Each idx In tdf.Indexes
If idx.Primary Then
strPKName = idx.Fields(0).Name
Exit For
End If

For Each fld In tdf.Fields
If fld.Name <> strPKName Then
strFields = strFields & ",[" & fld.Name & "]"
End If
strFields = Mid(strFields, 2)

Set qdf = db.CreateQueryDef("")

qdf.SQL = "INSERT INTO [" & strTable & "] (" & strFields & ") " & _
"SELECT " & strFields & " FROM [" & strTable & "] " & _
"WHERE [" & strPKName & "]= " & varPKVal
If qdf.RecordsAffected > 0 Then
With db.OpenRecordset("SELECT @@Identity")
fCopyRecord = .Fields(0)
End With
End If

Set db = Nothing
Set qdf = Nothing
Set tdf = Nothing
Set idx = Nothing
Set fld = Nothing
End Function
Any ideas?
Many Thanks
Bob G
can you set the button to enabled = false or something similar and enabled = true when a record is selected ?
Because this code and it's use are out of context with the discussion you had with Leigh, it is a bit difficult to discern wherein the problem lays. What error message was displayed before the crash? That is crucial.
urther, I suggest adding Error Handling to your code so you can acquire better messaging when such a crash occurs. Without error handling in a package solution, you will never get intelligent messafges avbout what caused the crash.
And guaging from what I decipher from the code, I would add a line or two to first check that the strTable As String, varPKVal variants have been supplied.
hope this helps
Thanks for your reply Bob.
Yes, that would be a neat solution. I fear you may over-estimate my coding skills though!
How would I do that?
Bob G
i believe what CyberCow has suggested would be the better solution.
Thanks for your reply (and many udders you have given me over the years!).
Herein lies a problem. I'm not sure if I am below the entry level on the forum sometimes. I really am not a coder of any type. All I know I have learned the hard way and usually I have to crib and tweak other chunks of code. It's rather foreign to me, and although my mind is logical enough to follow the basic ideas of the code, I quickly get very lost. I do understand the need for error handling, but have no idea where to even start. The F1 help on error handling gives me examples that I can't even think where to put, and includes terms such as "insert code here to handle the error". Erm...
I guess as I am requiring a deeper level of assistance, I need to make it a bit clearer what the problem is!
The previous thread related to creating a duplicate record for one on a datasheet subform. I originally thought of using a footer and using the wizard to duplicate a record, but realised that a datasheet subform does not allow a footer to be shown. So we arrived at Leigh's code! I realise there is no error handling, but I can't work out where the code looks for a record.
From Bob G's reply, I'm not sure how I see if a record is selected. I can understand how to disable the control, just no how to recognise the conditions that made it necessary!
Sorry again, I DO have some skills, but not in VBA!
Graham - with all due respect, this is your project and you really need to get up to speed on VBA. We have all "learned the hard way"; either by trial and error or lots of reading and then trial and error. VBA is the developer's crucial tool for Access desktop/client apps. And yes, it's a pain, time consuming and is a real learning curve, but that is the same with any development IDE or language. Doing it for you defeats the purpose of what UtterAccess is all about. UA is about setting Access developers on the path to learning the craft of using Access to the fullest extent using "Best Practices".
Just like you, I started out beating my brains out over VBA, but I eventually got it. You can too. There are links to VBA tutorials in the Newcomer's Reading List that will greatly facilitate your VBA learning path.
I'm not trying to put a stopper on this thread or helping solve your issue, I am trying to encourage you to dig your heals in in and get up to speed with VBA. With no idea how far along in your project you are, I always plug the concept of Error Handling at the starting point of any project. Good error handling is a potent tool for developers in that good Error Handling can pin-point where problems exist or are found. There is no substitute for logic, so Error Handling will get you only so far.
It is just to easy to throw out a fish to a hungry person. But that just encourages that person to come back for more fish. Teaching that person 'how to fish' is our goal. Otherwise, that person learns nothing and UtterAccess becomes the author of the project or section thereof.
So, that takes me back to the Error Handling. I always start new projects with the Error Handling routines and set them into EVERY module. For hand-me-down projects, I add the Error Handling routines if none or insufficient routines exist. The Error Handling link above was written in such a way as to allow a developer to pull in the elements into a new or existing project. What cannot be stressed enough is reading.gif, reading.gif & reading.gif - followed by much trial & error. Without a fundamental knowledge of VBA, you are left with a strong dependency on other developers to do what you can learn to do.
Meanwhile, back to your quandary . . . . Do you know how to validate whether or not the variable expressions of a procedure/function have been supplied? In your case: strTable and varPKVal (?)
I'm sorry CyberCow, but I have to disagree. I'm sure that's the original intent of UtterAccess, but that assumes I am a developer. I'm not, I'm a manager. If it get's to the point where I have to invest so much time, I simply won't be doing the developing!
If I'm on the wrong forum, I really do apologise as many people have been very helpful to me (yourself included). But I am not, and won't ever consider myself to be an Access developer. I'm merely a manager who has just enough skills to solve problems using access.
I do understand your points, and accept them. I am also aware that I produce imperfect and probably flakey solutions. But solutions I do usually produce! And I don't usually ask for the whole thing to be written, but just those aspects where I get stuck. Truth is, it's more so with A2007 as it doesn't produce code from the wizards...
Back to the question:
Meanwhile, back to your quandary . . . . Do you know how to validate whether or not the variable expressions of a procedure/function have been supplied? In your case: strTable and varPKVal (?)
No, I don't. This is an area where I am quickly lost. I think this is the fundamental of my problem
OK, I have some sort of solution:
I have inserted this error handler:
' Display error information.
' Resume with statement following occurrence of error.
If Err.Number = 3075 Then
MsgBox "You need to select a record to copy", 0, "Error"
End If

Resume Next
End Function
I actually had this set to report the error numbers, but as it is only one message that concerns me, I have simply set it to look for that error. This does pretty much what I need.
Thanks for your help
Graham - excellent! You just did what developers do. (so, that tends to make you a developer big_grin.gif ) You found the specific error code, trapped for that and moved on. Good for you! And as al
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.