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

 

[ashleymac]

Ok. I did that on the like "On Error GoTo errhandler" Line turned green. Saved. Reloaded the form. Tried to run the test and got a Run Time Error this time "Object variable or With block variable not set"

 

[RoryA]

On which line? Also, what does your form code look like now?

 

[ashleymac]

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]

You're still using Userform1_Initialize, which won't run automatically. Change it to Userform_Initialize. You also don't in fact need the conditional compilation there, so you just need:

Private Sub UserForm_Initialize()
 
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

 

[ashleymac]

Private Sub UserForm_Initialize()


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

This is what my code looks like now in that section. I took out the "1" and the "If" statement as instructed. But when I do that I get an error that says:

Run- time error '3044': 'f:\common\Production\Power\Power Software - (PR)\Current Power Software List - Test - Can Add.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Which makes absolutely no sense at all since the only thing that was changed was the "1" and the "If statement." There was no location swap, no server change, everything else is exactly the same.

 

[RoryA]

Yes, but when it said Userform1_Initialize, that code wasn't actually running at all. I suggest you check the path and filename (including extension) carefully.

 

[ashleymac]

Thank you for saying that! Between flipping back and forth between and Win7 and Win10 PC the "f:" drive was not mapped properly on the test win10 PC I was working on.

Fixing that did lead to another error on the Module page and I was able to get that sorted. Now *fingers crossed* everything seems to be working correctly on both set ups.

I really appreciate the help!

 

[RoryA]

Glad it's working.