Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: Building Database Help

    I am doing a Database or Church So they can keep a list of their music, instead of using overheads now they want to use a Computer.

    What I have done it created a Microsoft Access Database with a table that has the following colums:

    SongID (Text)
    SongName (Text)
    SongWord (Hyperlink)

    Each song has its own SongID such as ABC123 and its song has a file named the SongID that has the words in it such as ABC123.pdf

    Because they people who will be using it aren't that good on computers what I would like is something that when they add a new song, they type in the SongID, SongName, and instead of typing in the Path to the Document such as D:\ChurchMusic\Songs\ABC123.pdf manually, I would like a File Dialog or something like it where they can search for the file and once they have found it and clcik on it, it automatically puts the path to the document into SongWord Colum

    Can anybody help me out

  2. #2
    Join Date
    Nov 2003
    Copy the following code into a code module located within the Modules setion of your 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
    ' Filter string used for the File Open dialog filters.
    ' Use MSA_CreateFilterString() to create this.
    ' Default = All Files, *.*
        strFilter As String
    ' Initial Filter to display.
    ' Default = 1.
        lngFilterIndex As Long
    ' Initial directory for the dialog to open in.
    ' Default = Current working directory.
        strInitialDir As String
    ' Initial file name to populate the dialog with.
    ' Default = "".
        strInitialFile As String
        strDialogTitle As String
    ' Default extension to append to file if user didn't specify one.
    ' Default = System Values (Open File, Save File).
        strDefaultExtension As String
    ' Flags (see constant list) to be used.
    ' Default = no flags.
        lngFlags As Long
    ' Full path of file picked.  On OpenFile, if the user picks a
    ' nonexistent file, only the text in the "File Name" box is returned.
        strFullPathReturned As String
    ' File name of file picked.
        strFileNameReturned As String
    ' Offset in full path (strFullPathReturned) where the file name
    ' (strFileNameReturned) begins.
        intFileOffset As Integer
    ' Offset in full path (strFullPathReturned) where the file extension begins.
        intFileExtension As Integer
    End Type
    Const ALLFILES = "All Files"
        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_CREATEPROMPT = &H2000
    Const OFN_EXPLORER = &H80000
    Const OFN_FILEMUSTEXIST = &H1000
    Const OFN_NOVALIDATE = &H100
    Const OFN_READONLY = &H1
    Const OFN_SHOWHELP = &H10
    'Procedure BrowseForFile (Dialog Box)
    Function BrowseForFile(strSearchPath) As String
    ' Displays the open file dialog box for the user to locate a File.
    ' Returns the full path to the selected file.
        Dim msaof As MSA_OPENFILENAME
    ' Set options for the dialog box.
        msaof.strDialogTitle = "Browse For File...."
        If strSearchPath = "" Then msaof.strInitialDir = "C:\" Else msaof.strInitialDir = strSearchPath
        msaof.strFilter = MSA_CreateFilterString("Graphics Files (*.bmp; *jpg; *.gif; *.wmf; *.emf; *.dib)", "*.wmf;*.bmp;*.jpg;*.gif;*.emf;*.dib", "All Files (*.*)", "*.*")
    ' Call the Open File dialog routine.
        MSA_GetOpenFileName msaof
    ' Return the path and file name.
        BrowseForFile = Trim(msaof.strFullPathReturned)
    End Function
    'procedure MSA_CreateFilterString
    Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
    ' Creates a filter string from the passed in arguments.
    ' Returns "" if no args are passed in.
    ' Expects an even number of args (filter name, extension), but
    ' if an odd number is passed in, it appends *.*
        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) & Chr$(0)
            If intNum Mod 2 = 0 Then
                strFilter = strFilter & "*.*" & Chr$(0)
            End If
            strFilter = strFilter & Chr$(0)
            strFilter = ""
        End If
        MSA_CreateFilterString = strFilter
    End Function
    'procedure MSA_GetOpenFileName
    Public Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
    ' Opens the file open dialog.
        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
    'procedure MSA_GetSaveFileName
    Public Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer
    ' Opens the file save dialog.
        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
    'procedure MSAOF_to_OF
    ' This sub converts from the friendly MSAccess structure to the
    '  win32 structure.
        Dim strFile As String * 512
        ' Initialize some parts of the structure.
        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)
            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
    'procedure OF_to_MSAOF
    ' This sub converts from the win32 structure to the friendly MSAccess
    ' structure.
        msaof.strFullPathReturned = Left$(of.lpstrFile, _
            InStr(of.lpstrFile, Chr$(0)))
        msaof.strFileNameReturned = of.lpstrFileTitle
        msaof.intFileOffset = of.nFileOffset
        msaof.intFileExtension = of.nFileExtension
    End Sub
    Now...on your main data entry Form, in the On Click even of a Command button to display the dialog box, enter the following code:
        Dim Strg As String
        Strg = ""
        If Not IsNull(Me.HomePath) Then a = Me.HomePath
        Dim msaof As MSA_OPENFILENAME
        Dim strStartPath As String
        ' Set options for the dialog box.
        msaof.strDialogTitle = "Locate Song Name......"
        If strStartPath = "" Then msaof.strInitialDir = "C:\" Else msaof.strInitialDir = strStartPath
        msaof.strFilter = MSA_CreateFilterString("Song Files (*.pdf)", "*.pdf", "All Files (*.*)", "*.*")
        msaof.strInitialFile = "_"
        ' Call the Open File dialog routine.
        MSA_GetSaveFileName msaof
        ' Return the path and file name.
        If Trim(msaof.strFullPathReturned) <> "" Then Strg = Trim(msaof.strFullPathReturned)
        If Strg = "" Then Exit Sub Else  Strg = Trim(Strg)
       Me.SongWord = a
    There ya go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts