32 bit to 64 bit Macros

[ashleymac]

Let me start off by saying that I am very much a novice to VBA, Macros, Excel and Access. But here it goes.

At work we have an Access database and an Excelmacro sheet. They work together and they were both built in a 32 bitsystem. They are upgrading us to Win 10 64bit and Office 2016. Through a lot of googling I have been able to update the Access database and most of the Excel macro to work in both a 32 and 64 bit system. Below is the line of code that is stumping me and my google skills.

 Set rsdata = mydb.OpenRecordset("SELECT revisionID, SoftwareName FROMRevisionList WHERE (((RevisionList.FileSize)=" & totalsize &") AND ((RevisionList.DirectoryCount)=" & dirtotal & ")AND ((RevisionList.FileCount)=" & filetotal & "));")
            If Notrsdata.EOF Then
               Label9.Caption = rsdata![revisionid] & " - " &rsdata![softwarename]

It is working just fine in the 32 bit version but when I use Debug - Step In in 64 bit these lines get skipped completely

 

[Kyle123]

There's nothing related to 32 or 64 but there. HAve you got On Erorr Resume Next earlier in the code by any chance?

I can't see how that SQL would run in either version as it's not valid and is missing spaces

 

[ashleymac]

There isn't anything in that section of code that says "On Erorr Resume Next" I will post the entire code from the UserForm below. Again I'm like a super novice at this. This database and macros was created way before I ever started working at my company and I just started learning about this stuff last week to try and fix it. Thanks again for any help!

Option Explicit
Private dirtotal As Integer
Private filetotal As Integer
Private ChkDR As String
Private reset As Boolean
Private mydb As Database
 
 
Private Sub test()
    Dim rsdata As Recordset, totalsize As String, verfnum As String
    Dim fso As New FileSystemObject, dr As Scripting.Drive, fld As Scripting.Folder
    Label7.Caption = ""
    Label6.Caption = ""
    Label5.Caption = ""
    Label9.Caption = ""
    dirtotal = 0
    filetotal = 0
    On Error GoTo errhandler
    If fso.DriveExists(ChkDR) Then
        If fso.Drives(ChkDR).IsReady Then
            CommandButton1.Caption = "Busy..."
            CommandButton1.Enabled = False
            DoEvents
            dirsearch fso.Drives(ChkDR).RootFolder
            totalsize = fso.Drives(ChkDR).RootFolder.Size
            Label7.Caption = Format(totalsize, "#,##0") & " Bytes"
            Set rsdata = mydb.OpenRecordset("SELECT revisionID, SoftwareName FROM RevisionList WHERE (((RevisionList.FileSize)=" & totalsize & ") AND ((RevisionList.DirectoryCount)=" & dirtotal & ") AND ((RevisionList.FileCount)=" & filetotal & "));")
            If Not rsdata.EOF Then
                Label9.Caption = rsdata![revisionid] & " - " & rsdata![softwarename]
            Else
                Label9.Caption = "Unknown Software"
            End If
            Module1.ejectCD ChkDR
        Else
            MsgBox "No Disk Loaded."
        End If
    Else
        MsgBox "No drive exists."
    End If
errhandler:
End Sub
 
Private Sub dirsearch(tfld As Scripting.Folder)
    Dim fld As Scripting.Folder, fil As Scripting.File
    On Error Resume Next
    filetotal = filetotal + tfld.Files.Count
    Label5.Caption = Format(filetotal, "#,##0")
    For Each fld In tfld.SubFolders
        dirtotal = dirtotal + 1
        Label6.Caption = Format(dirtotal, "#,##0")
        dirsearch fld
        DoEvents
    Next fld
End Sub
 
Private Sub CommandButton1_Click()
Dim x As Integer, starttime As Single
    If reset Then
        For x = 58 To 18 Step -2
            CommandButton1.Top = x
            starttime = Timer + 0.01: Do Until Timer > starttime: DoEvents: Loop
        Next x
        reset = False
        Image1.Visible = True
        Label8.Visible = True
        Label2.Visible = True
        Label3.Visible = True
        Label4.Visible = True
        Label10.Visible = True
    End If
    CommandButton1.Caption = "Loading Disc..."
    CommandButton1.Enabled = False
    DoEvents
    test
    CommandButton1.Caption = "Start"
    CommandButton1.Enabled = True
End Sub
 
 
Private Sub OptionButton2_Click()
    ChkDR = "A"
End Sub
Private Sub optionbutton1_click()
    ChkDR = "D"
End Sub
 
Private Sub OptionButton3_Click()
    ChkDR = "E"
End Sub
Private Sub OptionButton4_Click()
    ChkDR = "G"
End Sub
#If  VBA7 Then
Private Sub UserForm1_Initialize()
#Else 
Private Sub UserForm_Initialize()
#End  If
 
reset = True
    CommandButton1.Left = 86.6
    CommandButton1.Top = 58.15
        Image1.Visible = False
        Label8.Visible = False
        Label2.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        Label10.Visible = False
    optionbutton1_click
    Set mydb = OpenDatabase("f:\common\Production\Power\Power Software - (PR)\Current Power Software List - Test - Can Add.mdb")
   
End Sub

 

[RoryA]

This part is wrong:

#If  VBA7 Then
Private Sub UserForm1_Initialize()
#Else  
Private Sub UserForm_Initialize()
#End   If

It's always just Userform_Initialize regardless of the name of the form.

 

[ashleymac]

When I take the "1" and the "If/Else" statements out I get a Runtime - Class Not Registered Error.

This part is wrong:

#If  VBA7 Then
Private Sub UserForm1_Initialize()
#Else  
Private Sub UserForm_Initialize()
#End   If

It's always just Userform_Initialize regardless of the name of the form.

 

[RoryA]

I didn’t suggest removing the If block.

What references do you have set in your project? If you have DAO3.6, that won’t work.

 

[ashleymac]

Leaving the If block and just removing the 1 to make the first line read "UserForm_Initialize()" still gives the run time error.

In the References - VBAProject box the following are checked:

Visual Basic For Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft Office 16.0 Object Library
Microsoft Scripting Runtime
Windows Media Player
Microsoft DAO 3.6 Object Library

That is the Priority order they are in as well. I have not added or removed any References since I started trying to figure this project out.

 

[RoryA]

As I said, DAO 3.6 won’t work with 64 bit. Remove that one and replace it with Microsoft Access 16.0 Database Engine object library

 

[ashleymac]

I have replaced DAO 3.6 and added

Microsoft Access 16.0 Database Engine object library as suggested. Does it matter what Priority it is set to?

It is still skipping the original two lines of code I was asking about.
​

 

[RoryA]

Comment out the On Error line and then reload the form.