Code has broken after upgrade to Access 2010

[Redbeard]

I have just upgraded to Access 2010 (64 bit) from Access 2007. There were a few minor issues in the database which I have resolved but this one piece of code just won’t work. I do not write VBA but have a rudimentary understanding of it. This code was not written by me and I am still trying to understand why it doesn’t work? I have posted the code below; this is what it is supposed to do: I have a main form that displays each record. The form is run from a query that combines one main table and 3 sub tables. The code is connected to a button on a pop-up form that when clicked, deletes the current record on the main form, from all the tables that it is run off. The problem is nothing happen? I don’t even get an error message and request to debug? I am hoping that it is a simple change that has to do with the upgrade as it worked in 2007. Any help is appreciated.

Thanks

'---------------------------------
    'Declaration of the variables
    '---------------------------------
 
    Dim rs As Recordset         'recordset use to get the objectId of the current record
    Dim QuerySelect As String   'represents the query to gather info
    Dim ObjectID As String      'represents the deleted objectId
 
    '--------------------------------
    'End of declaration
    '--------------------------------
 
    'Close the RecordControlBox
    DoCmd.Close acForm, "frmRecordControlBox"
 
    'Retrieving data from the record to be deleted
    QuerySelect = "Select ObjectID From Description where Description.[Accession Number] = '" & Forms!frmDescription!txtAccNum.Value & "'"
    Set rs = CurrentDb().OpenRecordset(QuerySelect)
 
    'going to the last record then come back to the first to make RS.RecordCount effective
    rs.MoveLast
    rs.MoveFirst
 
    'check if there is only 1 record, should not delete a whole bunch of record at the same time
    If rs.RecordCount = 1 Then
 
        ObjectID = CStr(rs![ObjectID])
 
        'building all the queries to delete every objectId in every table
        CurrentDb().Execute ("DELETE FROM [Condition Info] WHERE [Condition Info].[ObjectID]= " + ObjectID)
        CurrentDb().Execute ("DELETE FROM [Treatment Info] WHERE [Treatment Info].[ObjectID]= " + ObjectID)
        CurrentDb().Execute ("DELETE FROM [DigitalImageDetails] WHERE [DigitalImageDetails].[ObjectID]= " + ObjectID)
 
        Forms!frmDescription!rcdcontrol.SetFocus
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
    Else
        MsgBox "An error occured while retrieving ObjectID of the artifact. No record have been deleted"
    End If
 
Exit_rcdelete_Click:
    Exit Sub
 
Err_rcdelete_Click:
    'MsgBox Err.Description + "kgkgkg"
    Resume Exit_rcdelete_Click
 
End Sub

[Seth Schrock]

In your VBA editor, click in the gray area to the left of the code to insert a breakpoint at line 14 of your code and then try to run your code. If your code is being ran, the line will be highlighted in yellow (if colors are the default colors). If not, then the link between the button and its code is broken. Go into the design view of your popup form and view the properties of the button. Click on the ellipsis button (the three dots) and make sure that it goes to the correct procedure.

[Redbeard]

I have inserted the break point and nothing has changed. I also have checked it is running the right events procedure which it is. I should have said that the only think it does when I click the button is to close the pop-up form "frmRecordControlBox" and then nothing happens.

[Seth Schrock]

Okay, so the code is running. My guess is that the values aren't what you expect them to be. If you step through the code line by line, you can test your variables and properties in the immediate window.

[Redbeard]

I have found the immediate window but have not idea how to use it to "step through the code line by line, you can test your variables and properties"? Sorry I don't have a lot of experanice with VBA.

[Seth Schrock]

If you removed the breakpoint, add it again on line 14. When your code stops at that line, you can step through the code line by line by pressing the F8 key. Whichever line is highlighted is the one that will be executed next. To test your variables and properties you just type a question mark followed by the variable name or the property name. For example, you can find out what the record count is by typing the following into the immediate window when line 25 is highlighted:
?rs.RecordCount
Then press enter it will give you a number on the next line of the immediate window. With line 30 highlighted, type in ?ObjectID to verify that it is what you would expect.

I would also suggest adding an additional argument when you run your delete queries. For example, I would replace lines 30 -32 with

Dim db As DAO.Database
Set db = CurrentDb
 
db.Execute "DELETE FROM [Condition Info] WHERE [Condition Info].[ObjectID]= " + ObjectID, dbFailOnError
db.Execute "DELETE FROM [Treatment Info] WHERE [Treatment Info].[ObjectID]= " + ObjectID, dbFailOnError
db.Execute "DELETE FROM [DigitalImageDetails] WHERE [DigitalImageDetails].[ObjectID]= " + ObjectID, dbFailOnError
Set db = Nothing

[Redbeard]

Ok, so I did what you said and when I use F8 to go through each line in order it jumps from line 32 to 49 without running the rest of the code. So then I replaced lines 30 to 32 with the new code that you gave me and the same thing happened? I think this is the problem but don't know why it is doing that? or how to fix it? I have posted the updated code below. Any thoughts?

 Dim rs As Recordset         'recordset use to get the objectId of the current record
    Dim QuerySelect As String   'represents the query to gather info
    Dim ObjectID As String      'represents the deleted objectId
    Dim db As DAO.Database
    Set db = CurrentDb
    '--------------------------------
    'End of declaration
    '--------------------------------
 
    'Close the RecordControlBox
 
    DoCmd.Close acForm, "frmRecordControlBox"
 
    'Retrieving data from the record to be deleted
    QuerySelect = "Select ObjectID From Description where Description.[Accession Number] = '" & Forms!frmDescription!txtAccNum.Value & "'"
    Set rs = CurrentDb().OpenRecordset(QuerySelect)
 
    'going to the last record then come back to the first to make RS.RecordCount effective
    rs.MoveLast
    rs.MoveFirst
 
    'check if there is only 1 record, should not delete a whole bunch of record at the same time
    If rs.RecordCount = 1 Then
 
        ObjectID = CStr(rs![ObjectID])
 
        db.Execute "DELETE FROM [Condition Info] WHERE [Condition Info].[ObjectID]= " + ObjectID, dbFailOnError
        db.Execute "DELETE FROM [Treatment Info] WHERE [Treatment Info].[ObjectID]= " + ObjectID, dbFailOnError
        db.Execute "DELETE FROM [DigitalImageDetails] WHERE [DigitalImageDetails].[ObjectID]= " + ObjectID, dbFailOnError
        Set db = Nothing
 
        Forms!frmDescription!rcdcontrol.SetFocus
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
    Else
        MsgBox "An error occured while retrieving ObjectID of the artifact. No record have been deleted"
    End If
 
Exit_rcdelete_Click:
    Exit Sub
 
Err_rcdelete_Click:
    'MsgBox Err.Description + "kgkgkg"
    Resume Exit_rcdelete_Click
 
End Sub

[Seth Schrock]

Since you have error trapping at the end, I would assume that you have a line above what you have provided that says something like On Error GoTo .... Could you provide us with that line as well as the line where the sub is declared? In fact, everything above what you have provided would be helpful.

[Redbeard]

Sorry about that, here is the full code start to finish.

Private Sub rcdelete_Click()
On Error GoTo Err_rcdelete_Click
 
    '---------------------------------
    'Declaration of the variables
    '---------------------------------
 
    Dim rs As Recordset         'recordset use to get the objectId of the current record
    Dim QuerySelect As String   'represents the query to gather info
    Dim ObjectID As String      'represents the deleted objectId
    Dim db As DAO.Database
    Set db = CurrentDb
    '--------------------------------
    'End of declaration
    '--------------------------------
 
    'Close the RecordControlBox
 
    DoCmd.Close acForm, "frmRecordControlBox"
 
    'Retrieving data from the record to be deleted
    QuerySelect = "Select ObjectID From Description where Description.[Accession Number] = '" & Forms!frmDescription!txtAccNum.Value & "'"
    Set rs = CurrentDb().OpenRecordset(QuerySelect)
 
    'going to the last record then come back to the first to make RS.RecordCount effective
    rs.MoveLast
    rs.MoveFirst
 
    'check if there is only 1 record, should not delete a whole bunch of record at the same time
    If rs.RecordCount = 1 Then
 
        ObjectID = CStr(rs![ObjectID])
 
 
 
 
        db.Execute "DELETE FROM [Condition Info] WHERE [Condition Info].[ObjectID]= " + ObjectID, dbFailOnError
        db.Execute "DELETE FROM [Treatment Info] WHERE [Treatment Info].[ObjectID]= " + ObjectID, dbFailOnError
        db.Execute "DELETE FROM [DigitalImageDetails] WHERE [DigitalImageDetails].[ObjectID]= " + ObjectID, dbFailOnError
        Set db = Nothing
 
        Forms!frmDescription!rcdcontrol.SetFocus
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
    Else
        MsgBox "An error occured while retrieving ObjectID of the artifact. No record have been deleted"
    End If
 
Exit_rcdelete_Click:
    Exit Sub
 
Err_rcdelete_Click:
    'MsgBox Err.Description + "kgkgkg"
   ' Resume Exit_rcdelete_Click
 
End Sub

[zmbd]

Seth may have you on a good track here; however, let’s do a few housekeeping things:

Please take a look at the first few parts of the following link: >> Before Posting (VBA or SQL) Code.

In particular

For VBA code specifically :
Ensure you have Option Explicit set (See Require Variable Declaration).
Try to compile it. If it doesn't compile for any reason then fix it before posting. If you cannot manage that for any reason, then please explain that clearly - including the error message and which line of your code it appears on.

It is most important to get the minor syntax and other junk cleared up.

This is particularly important for you as If I read this correctly - you moved code from a 32-Bit install to a 64-Bit install of office AND from 2007 to 2010 - there have been some really weird reports about missing links to library references, or wrong links, etc... even when doing an upgrade from Office2010-32 to Office2010-64:

Please confirm that you have a 64Bit installation of Office/Access by doing the following: In the ribbon: [File] in left pane [Help] in the right pane, the installation information will be shown - you should see something like
"Version 14.0.6129.500(32-Bit)" for a 32 bit install.
- However, I don't think this is your issue.

I Know you’ve posted some code in post #9, so all of the following is going to reference that code…

Line 23:
Set rs = CurrentDb().OpenRecordset(QuerySelect)
Should be:
Set rs = DB.OpenRecordset(QuerySelect) as you've already set DB = the current database on line 12.
As you have it now, you have two different pointers to the same database.

Also, as best practice, you need to close your record sets and set them to nothing to release the resources correctly. As it is now, there is a potential for your record set to be locked, or memory leakage. (Closing Recordset Vs. Setting Nothing

From your other posts… it sounds like during your stepping that execution jumped from db.Execute "DELETE FROM [DigitalImageDetails](...) all the way to line 49 (which is blank?) in an error it should have failed all the way to the End Sub at line 57 given the posted code.

SO, let’s say you’ve cleared up any of the compiler errors as I’ve suggested then please:

Line 2: disable by placing a single quote " ' " in front of the line. This is for trouble shooting only, before the command.

Compile, Save, You can leave the VBA open.

Do whatever tasks you need to get this form to open in "normal" use, click on your button, when (if) it errors you should be taken to debug mode and I suspect that it will be on line db.Execute "DELETE FROM [DigitalImageDetails](...) (although, I halfway expect this to fail at db.Execute "DELETE FROM [Condition Info] – however, that is dependent upon your tables.

Tell us what error you get at that point. PLEASE, provide the EXACT line of code the error occures, the EXACT error number and the EXACT error text. Please, no shortcuts…

You might also want to take a look at setting up your table relationships with cascade update/delete. I typically don't allow cascade delete in my DB due to data integrity checks (once entered, always audited); however, it can be very useful in specific situations.

[Redbeard]

Thanks zmbd that fixed it! I followed you steps and was able to fix all the errors. After I changed Line 23 to the code you suggested and did a Compile, it gave me an error on Line 39, like you predicted. (Run-Time error 3601, Too few parameters Expect 1) The ObjectID field for my DigitalImageDetails table is DIObjectID and not ObjectID as it is for all the other ones. Once I fix that it all ran smoothly.
Thanks

[zmbd]

YEA!

Now, two simple things to finish up fixing your code:
Back to Post#9

First:
Line 2: Re-enable your error trapping (Appears I mentioned ";" should have said single quote.." ' " opps - too many programing languages under the old hat. In any case, you should re-enable the code.

Last:
Lines 53 thru 57
Currently read:

Err_rcdelete_Click: 
    'MsgBox Err.Description + "kgkgkg" 
   ' Resume Exit_rcdelete_Click 
 

Your error message/trap is "disabled" and what is there would be only mildly helpful; however, in this case, even with the minimum, it might have helped you find the error sooner if it had been enabled.
What I suggest is to change this code to something (IMHO) useful such as:

'DEFAULT ERROR TRAP
'
'make sure you have zj_errorlocation defined.
    MsgBox Now() & vbCrLf & _
        zj_errorlocation & vbCrLf & _
         Err.Number & vbCrLf & _
           Err.Description, vbExclamation, _
            "DEFAULT ERROR TRAP"
'
Resume exit_from_error_to_cleanup
'

That last resume line takes my code back to a section where I check that all recordsets are closed and pointers are released.
The "zj_errorlocation" is just a variable I set at the beginning of my modules so that when the users report error I can tell which module/form the error is occurring. It can be omitted.