Solved

syntax error in access browse button code

Posted on 2009-05-04
2
405 Views
Last Modified: 2012-05-06
I am creating a browse button in access to put a path into a text box as a hyperlink.  I'm having a syntax error on the

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)
 msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar)
- 1)
 msaof.strFileNameReturned = of.lpstrFileTitle
 msaof.intFileOffset = of.nFileOffset
 msaof.intFileExtension = of.nFileExtension
End Sub

thanks!  Code is the full module
Option Explicit

Option Compare Database
 

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _

 "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _

 "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
 

Type MSA_OPENFILENAME

 strFilter As String

 lngFilterIndex As Long

 strInitialDir As String

 strInitialFile As String

 strDialogTitle As String

 strDefaultExtension As String

 lngFlags As Long

 strFullPathReturned As String

 strFileNameReturned As String

 intFileOffset As Integer

 intFileExtension As Integer

End Type
 

Const ALLFILES = "All Files"
 

Type OPENFILENAME

 lStructSize As Long

 hwndOwner As Long

 hInstance As Long

 lpstrFilter As String

 lpstrCustomFilter As Long

 nMaxCustrFilter 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

 lCustrData As Long

 lpfnHook As Long

 lpTemplateName As Long

End Type
 

Const OFN_ALLOWMULTISELECT = &H200

Const OFN_CREATEPROMPT = &H2000

Const OFN_EXPLORER = &H80000

Const OFN_FILEMUSTEXIST = &H1000

Const OFN_HIDEREADONLY = &H4

Const OFN_NOCHANGEDIR = &H8

Const OFN_NODEREFERENCELINKS = &H100000

Const OFN_NONETWORKBUTTON = &H20000

Const OFN_NOREADONLYRETURN = &H8000

Const OFN_NOVALIDATE = &H100

Const OFN_OVERWRITEPROMPT = &H2

Const OFN_PATHMUSTEXIST = &H800

Const OFN_READONLY = &H1

Const OFN_SHOWHELP = &H10
 
 
 

'FUNCTION TO OPEN THE DIALOG WITHOUT USING THE ACTIVE-X
 

Function fnLocateDB(strSearchPath) As String
 

Dim msaof As MSA_OPENFILENAME

msaof.strDialogTitle = "Open File: " & Year(Date) & ".mdb"

msaof.strInitialDir = Trim(strSearchPath)

msaof.strFilter = MSA_CreateFilterString("All files", "*.*")

MSA_GetOpenFileName msaof

fnLocateDB = "#" & Trim(msaof.strFullPathReturned) & "#"
 

End Function
 

Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String

 Dim strFilter As String

 Dim intRet As Integer

 Dim intNum As Integer
 

 intNum = UBound(varFilt)

 If (intNum <> -1) Then

     For intRet = 0 To intNum

         strFilter = strFilter & varFilt(intRet) & vbNullChar

     Next

     If intNum Mod 2 = 0 Then

         strFilter = strFilter & "*.*" & vbNullChar

     End If

     

     strFilter = strFilter & vbNullChar

 Else

     strFilter = ""

 End If
 

 MSA_CreateFilterString = strFilter

End Function
 
 
 
 

Function MSA_ConvertFilterString(strFilterIn As String) As String

 Dim strFilter As String

 Dim intNum As Integer, intPos As Integer, intLastPos As Integer
 

 strFilter = ""

 intNum = 0

 intPos = 1

 intLastPos = 1

 Do

     intPos = InStr(intLastPos, strFilterIn, "|")

     If (intPos > intLastPos) Then

         strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos -

intLastPos) & vbNullChar

         intNum = intNum + 1

         intLastPos = intPos + 1

     ElseIf (intPos = intLastPos) Then

         intLastPos = intPos + 1

     End If

 Loop Until (intPos = 0)

     

 intPos = Len(strFilterIn)

 If (intPos >= intLastPos) Then

     strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos +

1) & vbNullChar

     intNum = intNum + 1

 End If

 

 If intNum Mod 2 = 1 Then

     strFilter = strFilter & "*.*" & vbNullChar

 End If
 

 If strFilter <> "" Then

     strFilter = strFilter & vbNullChar

 End If

 

 MSA_ConvertFilterString = strFilter

End Function
 
 

Private Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer

 

 Dim of As OPENFILENAME

 Dim intRet As Integer
 

 MSAOF_to_OF msaof, of

 of.Flags = of.Flags Or OFN_HIDEREADONLY

 intRet = GetSaveFileName(of)

 If intRet Then

     OF_to_MSAOF of, msaof

 End If

 MSA_GetSaveFileName = intRet

End Function
 
 
 
 
 

Function MSA_SimpleGetSaveFileName() As String
 

 Dim msaof As MSA_OPENFILENAME

 Dim intRet As Integer

 Dim strRet As String

 

 intRet = MSA_GetSaveFileName(msaof)

 If intRet Then

     strRet = msaof.strFullPathReturned

 End If

 

 MSA_SimpleGetSaveFileName = strRet

End Function
 
 
 
 

Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
 

 Dim of As OPENFILENAME

 Dim intRet As Integer
 

 MSAOF_to_OF msaof, of

 intRet = GetOpenFileName(of)

 If intRet Then

     OF_to_MSAOF of, msaof

 End If

 MSA_GetOpenFileName = intRet

End Function
 
 
 
 

Function MSA_SimpleGetOpenFileName() As String

 Dim msaof As MSA_OPENFILENAME

 Dim intRet As Integer

 Dim strRet As String

 

 intRet = MSA_GetOpenFileName(msaof)

 If intRet Then

     strRet = msaof.strFullPathReturned

 End If

 

 MSA_SimpleGetOpenFileName = strRet

End Function
 
 

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)

 msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar)

- 1)

 msaof.strFileNameReturned = of.lpstrFileTitle

 msaof.intFileOffset = of.nFileOffset

 msaof.intFileExtension = of.nFileExtension

End Sub
 
 
 

Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)

Dim strFile As String * 512

 of.hwndOwner = Application.hWndAccessApp

 of.hInstance = 0

 of.lpstrCustomFilter = 0

 of.nMaxCustrFilter = 0

 of.lpfnHook = 0

 of.lpTemplateName = 0

 of.lCustrData = 0

 

 If msaof.strFilter = "" Then

     of.lpstrFilter = MSA_CreateFilterString(ALLFILES)

 Else

     of.lpstrFilter = msaof.strFilter

 End If

 of.nFilterIndex = msaof.lngFilterIndex

 

 of.lpstrFile = msaof.strInitialFile _

     & String(512 - Len(msaof.strInitialFile), 0)

 of.nMaxFile = 511
 

 of.lpstrFileTitle = String(512, 0)

 of.nMaxFileTitle = 511
 

 of.lpstrTitle = msaof.strDialogTitle
 

 of.lpstrInitialDir = msaof.strInitialDir

 

 of.lpstrDefExt = msaof.strDefaultExtension
 

 of.Flags = msaof.lngFlags

 

 of.lStructSize = Len(of)

End Sub

Open in new window

0
Comment
Question by:KimWilliamson
2 Comments
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 24299424
The problem was there was a new line where the line should have been continuous. Actually there were several of them, which I have fixed in the snippet.

 msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar)
- 1)

should be

 msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar)- 1)
Option Compare Database

Option Explicit

 

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _

 "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _

 "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

 

Type MSA_OPENFILENAME

 strFilter As String

 lngFilterIndex As Long

 strInitialDir As String

 strInitialFile As String

 strDialogTitle As String

 strDefaultExtension As String

 lngFlags As Long

 strFullPathReturned As String

 strFileNameReturned As String

 intFileOffset As Integer

 intFileExtension As Integer

End Type

 

Const ALLFILES = "All Files"

 

Type OPENFILENAME

 lStructSize As Long

 hwndOwner As Long

 hInstance As Long

 lpstrFilter As String

 lpstrCustomFilter As Long

 nMaxCustrFilter 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

 lCustrData As Long

 lpfnHook As Long

 lpTemplateName As Long

End Type

 

Const OFN_ALLOWMULTISELECT = &H200

Const OFN_CREATEPROMPT = &H2000

Const OFN_EXPLORER = &H80000

Const OFN_FILEMUSTEXIST = &H1000

Const OFN_HIDEREADONLY = &H4

Const OFN_NOCHANGEDIR = &H8

Const OFN_NODEREFERENCELINKS = &H100000

Const OFN_NONETWORKBUTTON = &H20000

Const OFN_NOREADONLYRETURN = &H8000

Const OFN_NOVALIDATE = &H100

Const OFN_OVERWRITEPROMPT = &H2

Const OFN_PATHMUSTEXIST = &H800

Const OFN_READONLY = &H1

Const OFN_SHOWHELP = &H10

 

 

 

'FUNCTION TO OPEN THE DIALOG WITHOUT USING THE ACTIVE-X

 

Function fnLocateDB(strSearchPath) As String

 

Dim msaof As MSA_OPENFILENAME

msaof.strDialogTitle = "Open File: " & Year(Date) & ".mdb"

msaof.strInitialDir = Trim(strSearchPath)

msaof.strFilter = MSA_CreateFilterString("All files", "*.*")

MSA_GetOpenFileName msaof

fnLocateDB = "#" & Trim(msaof.strFullPathReturned) & "#"

 

End Function

 

Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String

 Dim strFilter As String

 Dim intRet As Integer

 Dim intNum As Integer

 

 intNum = UBound(varFilt)

 If (intNum <> -1) Then

     For intRet = 0 To intNum

         strFilter = strFilter & varFilt(intRet) & vbNullChar

     Next

     If intNum Mod 2 = 0 Then

         strFilter = strFilter & "*.*" & vbNullChar

     End If

     

     strFilter = strFilter & vbNullChar

 Else

     strFilter = ""

 End If

 

 MSA_CreateFilterString = strFilter

End Function

 

 

 

 

Function MSA_ConvertFilterString(strFilterIn As String) As String

 Dim strFilter As String

 Dim intNum As Integer, intPos As Integer, intLastPos As Integer

 

 strFilter = ""

 intNum = 0

 intPos = 1

 intLastPos = 1

 Do

     intPos = InStr(intLastPos, strFilterIn, "|")

     If (intPos > intLastPos) Then

         strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos) & vbNullChar

         intNum = intNum + 1

         intLastPos = intPos + 1

     ElseIf (intPos = intLastPos) Then

         intLastPos = intPos + 1

     End If

 Loop Until (intPos = 0)

     

 intPos = Len(strFilterIn)

 If (intPos >= intLastPos) Then

     strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos + 1) & vbNullChar

     intNum = intNum + 1

 End If

 

 If intNum Mod 2 = 1 Then

     strFilter = strFilter & "*.*" & vbNullChar

 End If

 

 If strFilter <> "" Then

     strFilter = strFilter & vbNullChar

 End If

 

 MSA_ConvertFilterString = strFilter

End Function

 

 

Private Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer

 

 Dim of As OPENFILENAME

 Dim intRet As Integer

 

 MSAOF_to_OF msaof, of

 of.Flags = of.Flags Or OFN_HIDEREADONLY

 intRet = GetSaveFileName(of)

 If intRet Then

     OF_to_MSAOF of, msaof

 End If

 MSA_GetSaveFileName = intRet

End Function

 

 

 

 

 

Function MSA_SimpleGetSaveFileName() As String

 

 Dim msaof As MSA_OPENFILENAME

 Dim intRet As Integer

 Dim strRet As String

 

 intRet = MSA_GetSaveFileName(msaof)

 If intRet Then

     strRet = msaof.strFullPathReturned

 End If

 

 MSA_SimpleGetSaveFileName = strRet

End Function

 

 

 

 

Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer

 

 Dim of As OPENFILENAME

 Dim intRet As Integer

 

 MSAOF_to_OF msaof, of

 intRet = GetOpenFileName(of)

 If intRet Then

     OF_to_MSAOF of, msaof

 End If

 MSA_GetOpenFileName = intRet

End Function

 

 

 

 

Function MSA_SimpleGetOpenFileName() As String

 Dim msaof As MSA_OPENFILENAME

 Dim intRet As Integer

 Dim strRet As String

 

 intRet = MSA_GetOpenFileName(msaof)

 If intRet Then

     strRet = msaof.strFullPathReturned

 End If

 

 MSA_SimpleGetOpenFileName = strRet

End Function

 

 

Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)

 msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar) - 1)

 msaof.strFileNameReturned = of.lpstrFileTitle

 msaof.intFileOffset = of.nFileOffset

 msaof.intFileExtension = of.nFileExtension

End Sub

 

 

 

Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)

Dim strFile As String * 512

 of.hwndOwner = Application.hWndAccessApp

 of.hInstance = 0

 of.lpstrCustomFilter = 0

 of.nMaxCustrFilter = 0

 of.lpfnHook = 0

 of.lpTemplateName = 0

 of.lCustrData = 0

 

 If msaof.strFilter = "" Then

     of.lpstrFilter = MSA_CreateFilterString(ALLFILES)

 Else

     of.lpstrFilter = msaof.strFilter

 End If

 of.nFilterIndex = msaof.lngFilterIndex

 

 of.lpstrFile = msaof.strInitialFile _

     & String(512 - Len(msaof.strInitialFile), 0)

 of.nMaxFile = 511

 

 of.lpstrFileTitle = String(512, 0)

 of.nMaxFileTitle = 511

 

 of.lpstrTitle = msaof.strDialogTitle

 

 of.lpstrInitialDir = msaof.strInitialDir

 

 of.lpstrDefExt = msaof.strDefaultExtension

 

 of.Flags = msaof.lngFlags

 

 of.lStructSize = Len(of)

End Sub

Open in new window

0
 

Author Closing Comment

by:KimWilliamson
ID: 31577746
thanks!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now