Going from 32 bit to 64 bit has messed up some Public Functions

[kathiepreston]

I have been working with the below function since about 2008, and it has worked fine on the 32bit laptops that I have had. I have recently changed to a 64bit laptop and now it doesn't work. The bold line seems to be what is messing up. I can't get past the Then statement of the If/Then (underlined). What can I do to get this working. I have several functions that are basically the same and no work. Since they all are very important to the functionality of the databases to get reports and bring in data, I am desperate to get them working before the end of the month. Any and all assistance is greatly appreciated.

Public Function ImportWO() As Boolean
Dim db As DAO.Database
Set db = CurrentDb
Dim xFname As Variant
xFname = Screen.ActiveForm.Name
Forms(xFname)!OFName = BrowseOpen()
DoCmd.Hourglass Yes
DoCmd.SetWarnings No
If Forms(xFname)!OFName = "" Then
MsgBox "Cancel Selected", vbOKOnly, "Cancel Selected"
Else
DoCmd.CopyObject , "WORaw", acTable, "WOrawStructure"
' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "SRaw", Forms(xFname)!OFName, No
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "WORAW", Forms(xFname)!OFName, No
Call WO1
MsgBox "Import Completed", vbOKOnly, "Import Completed"
End If
DoCmd.Hourglass No
End Function

[isladogs]

I can't see anything obvious in that code that would prevent it working in 64-bit.
However some questions

Why are you using variant for XFName? Wouldn't String be better?
What is the code for the BrowseOpen function? OFName is presumably a control such as a textbox
Also, we have no idea what is contained in WO1 which you call later.

Have you updated all API declarations to 64-bit?

[kd2017]

I agree with isladogs, we need to see the code for the BrowseOpen() function.

In addition, ImportWO is a boolean function but doesn't return anything? you could probably change this to a sub. You forgot to turn the warnings back on at the end of the function. Finally, you create a DAO.Database object but never use it or close it.

[EDIT]
Oh and maybe it's just me but I have to use True/False instead of Yes/No

[kathiepreston]

I can't see anything obvious in that code that would prevent it working in 64-bit.
However some questions

Why are you using variant for XFName? Wouldn't String be better?
What is the code for the BrowseOpen function? OFName is presumably a control such as a textbox
Also, we have no idea what is contained in WO1 which you call later.

Have you updated all API declarations to 64-bit?

Well this was written by someone else that I can't reach, so I don't know why he used a variant instead of string. The OFName is an unbound text box on the from with the button that calls the IMPORTWO() function. I don't know what is supposed to go into the box after the button is pushed.
The WO1 is another function that updates the tables based on the column names.

I don't know what the API declarations to 64-bit are. If you could direct me on how to do that, I will get that done.

Thanks for the help.

[kathiepreston]

I didn't write this code. What I posted previously is all I have to go on. Could BrowseOpen() function be a private function inside my database?
What I need is a way to open the File Browser window so I can upload a selected spreadsheet based on the WO1 code. There are other functions that do the same thing that called different functions to update tables.

[kathiepreston]

I think I found the function for the BrowseOpen(). hope this helps.
Public Function BrowseOpen() As String
Dim OFName As OPENFILENAME
OFName.lStructSize = Len(OFName)
OFName.lpstrFilter = "Excel Files (*.xlsx)" + Chr$(0) + "*.xlsx" '"Excel Files (*.csv)" + Chr$(0) + "*.csv" '+ Chr$(0) ' +
OFName.lpstrFile = Space$(999)
OFName.nMaxFile = 1000
OFName.lpstrFileTitle = Space$(999)
OFName.nMaxFileTitle = 1000
OFName.lpstrInitialDir = "..\Desktop"
OFName.lpstrTitle = "File Open Browser"
OFName.flags = 0
If GetOpenFileName(OFName) Then
BrowseOpen = Trim$(OFName.lpstrFile)
' BrowseOpen = Trim$(Left$(OFName.lpstrFile, Len(OFName.lpstrFile) - 2))
' buff = Trim$(Left$(OFN.sFile, Len(OFN.sFile) - 2))
Else
BrowseOpen = ""
End If
End Function

[Micron]

I suspect the bitness is of no concern because you say (I think) that the code fails on a particular line. IIRC, any issues with 64 vs 32 would arise as soon as the "unsafe" API's are referenced or compiled? You could use the standard method of troubleshooting - put a break point on the start of the code and initiate the event that runs it (e.g. button click). Then you step through line by line (F8) and follow the process, checking variables and/or the results of calculations/checks as you go. You can simply mouse over a variable to see its value. Note that when the execution is on a line (it is highlighted) it has not been processed so any variable will still have the value it had up to that point. You need to move off the line to see the result of whatever that line does. The code you eventually found seems to branch off again (GetOpenFileName) so I think no one is any farther ahead yet.

Another thing you can do is check your references. If you switch from 32 to 64, you likely are now using a higher version of Office or at least Access and there could be a missing reference now.

P.S. would be much appreciated if you would post code between code tags (# on posting toolbar) with proper indentation to make it easier to read. Example:

Public Function BrowseOpen() As String

Dim OFName As OPENFILENAME

OFName.lStructSize = Len(OFName)
OFName.lpstrFilter = "Excel Files (*.xlsx)" + Chr$(0) + "*.xlsx" '"Excel Files (*.csv)" + Chr$(0) + "*.csv" '+ Chr$(0) ' +
OFName.lpstrFile = Space$(999)
OFName.nMaxFile = 1000
OFName.lpstrFileTitle = Space$(999)
OFName.nMaxFileTitle = 1000
OFName.lpstrInitialDir = "..\Desktop"
OFName.lpstrTitle = "File Open Browser"
OFName.flags = 0

If GetOpenFileName(OFName) Then
   BrowseOpen = Trim$(OFName.lpstrFile)
   'BrowseOpen = Trim$(Left$(OFName.lpstrFile, Len(OFName.lpstrFile) - 2))
   'buff = Trim$(Left$(OFN.sFile, Len(OFN.sFile) - 2))
Else
   BrowseOpen = ""
End If

End Function

[kathiepreston]

Well if the problem is not the 32 bit to 64 bit. I have added the PtrSafe to all the declarations in all the functions, so I'm not sure where to go from here.
I'll keep trying.
Thanks for the help!

[Micron]

I'm not sure where to go from here.

I gave you two suggestions?
1) step through and validate. If not obvious, at least you could report if the called functions perform as expected and prove that the problem line is what you reported.
2) check your references

to which I'll add
- verifying that your code will compile
- performing a compact/repair

and then testing after you've implemented any suggestions then reporting back.

[kathiepreston]

I did compile the code and did a compact /repair and did try to run the code and it still got the "Canceled Selection" response.
I tried the step through but would get an error that I couldn't run the code from there. I assume that means with function open.
Since I have functions with in functions I'm not sure how to step through multiples.

[Micron]

and now it doesn't work. The bold line seems to be what is messing up.

Perhaps we should have asked you what that meant as it seems to have been misinterpreted as an error message when in fact, it's your message box being raised. "Doesn't work" doesn't help. You can't usually run a form module level function directly - put the break on it and use the event that triggers it as I said. In your case, I gather that is a button click. As for stepping through multiples, you don't have to worry about that as execution will pass from one to the other and back again. All I can deduce from that code is that BrowseOpen is returning "" thus your message box is raised because that's what the code is supposed to do. If you think it should be returning some other value, then you need to step through and find out why.

Last but not least I have never used OpenFileName, which is documented as a Win32 function but perhaps others can comment of whether or not that could be an issue here. For Access, I would use one the msoFileDialog choices instead as I've never seen anyone report an issue with it regardless of bitness as it's part of the Office library and doesn't seem to be bit dependent AFAIK. Your choices would be msoFileDialogFilePicker or msoFileDialogFolderPicker depending on what your current API is used for.

I suppose you could post a copy of your db (compacted and zipped) here but I suspect there would be some issues regarding code that depends on files existing somewhere.

[moke123]

A search for GetOpenFileName returned this:

Application.GetOpenFileName

This method can be used to obtain a valid filename and its full path.
This should not be used but is still available for backwards compatibility.
To select a file you should use the Application.FileDialog - FilePicker method.

Not quite sure I understand its implimentation in your code.
Not sure if it's an access method or excel method.

[kathiepreston]

Well, I have been trying to step through the IMPORTWO and it seems to be failing because the OFNAME is returning a Null. Below is the function I believe is the problem.
I have no idea what to do. OPENFILENAME is a Private Type function. I have added it below the BrowseOpen(). Maybe that will help.
Public Function BrowseOpen() As String

Dim OFName As OPENFILENAME

OFName.lStructSize = Len(OFName)
OFName.lpstrFilter = "Excel Files (*.xlsx)" + Chr$(0) + "*.xlsx" '"Excel Files (*.csv)" + Chr$(0) + "*.csv" '+ Chr$(0) ' +
OFName.lpstrFile = Space$(999)
OFName.nMaxFile = 1000
OFName.lpstrFileTitle = Space$(999)
OFName.nMaxFileTitle = 1000
OFName.lpstrInitialDir = "..\Desktop"
OFName.lpstrTitle = "File Open Browser"
OFName.flags = 0

If GetOpenFileName(OFName) Then
BrowseOpen = Trim$(OFName.lpstrFile)
'BrowseOpen = Trim$(Left$(OFName.lpstrFile, Len(OFName.lpstrFile) - 2))
'buff = Trim$(Left$(OFN.sFile, Len(OFN.sFile) - 2))
Else
BrowseOpen = ""
End If

End Function



Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

[Micron]

I would say that last piece of code is the problem. I didn't think bitness was the issue at first, thinking that any 32 bit variables would be declared as is normal, but they are 'typed'. Not sure but suspect that would allow code to compile but still cause run time errors. So you have done what is required wrt declaring ptrsafe and it looks like the example here (scroll down until you see the code for GetOpenfileName and ignore that it is an Excel site)

https://jkp-ads.com/articles/apideclarations.asp

I suspect isladogs has good info at his site as well and will provide a link to it if that is the case.

[Welshgasman]

Post the GetOpenFilename code please