Input mask 1

[hoofit]

Is there an input mask that results in c:\ ? I want to force a file name that is entered in a text box by a user to be preceeded by c:\. Thus a file named test.bmp is entered by a user as such but results in c:\test.bmp.

Thank you kindly

 

[hoofit]

GShen,
yeah yeah yeah. It's been slow day day

 

[hoofit]

Only problem I see with gifs is when someone comes in with a camera loaded with images, all of them need to be converted from jpg to gif before they're useable. Is that correct? The browse idea seems better. I did not realize that this was going to be a major project today.

hoof

 

[GShen]

Okay. I do not want to interject as MajP seems to have you on a very good path (no pun intended). Let me know if you get stuck. I am sure it will work eventually.

Remember when... everything worked and there was a reason for it?

 

[hoofit]

So, in the sign table if there is a record for a no parking sign on a metal post on maple road in front of 6788 address, the image can only be a no parking sign (actual photo)...can't be a speed sign

 

[hoofit]

Nor can it be a no parking sign on another street. It must be a match

 

[MajP]

An image control should support JPG, BMP, GIF, WMF, EMF, Dib, ICO, CGM, EPS, PCT, PNG, WPG
It will not support a JPEG extension.

 

[hoofit]

Based on this, could'nt I still place the path in the sign table? The sign ID still uniquely identifies it.

hoof

 

[MajP]

1) If I understand correctrly, a record in the Signs table can have only one associated image and that image is not associated to any other signs. If that is the case then the path to the image should go directly in the Sign table. It is a unique property of a Sign

2) If a record in the Signs table could have multiple related images (one to many) then you need a table for the images and each image would have a foreign key relating it back to a sign in the Signs table. The paths would go in the image table.

3) If a record in the Signs table could have multiple images and these images could be also associated to other signs (many to many) you would need an image table without a foreign key and the paths go there. Then you would need a junction table that holds the primary keys from the Signs table and the image table. Ex

juncTblSigns_Images

signID ImageID
1 55
1 57
1 100
2 55
2 99

This would mean that Sign 1 is related to images (55,57,100) and sign 2 relates to (55,99)

 

[hoofit]

Good morning,
Your statement #1 is correct, the path is a unique property. So it appears that an image table is not necessary in this situation as the paths are stored in the sign table and the images are stored on the hd.?

 

[MajP]

Yes that is correct.

Assuming they know the file to pick then, just add the file browser code and you can simply store the path in the Sign table. With some code modification you can set the file browser to open in your default image directory and only show the acceptable image file types.

However, how does the user do their work? Do they know which image to browse to? Or do they need to browse the images and then choose the correct one?

If they need to see all the images in Access, then you could modify my original "Add from Folder" code. This can insert all the paths to image files contained in a folder into a table. Then you could browse all the images in Access and pick the correct one. Then simply write to the selected path the the Signs table. You can then mark the images as "Assigned" so that it cannot be assinged to another Sign. Most of this is in that demo. Obviously a little more work, but a nice feature.

 

[hoofit]

q. However, how does the user do their work? Do they know which image to browse to? Or do they need to browse the images and then choose the correct one?
a. The user will need to browse the images and then choose the correct one

"This can insert all the paths to image files contained in a folder into a table."
Yes, but would this not be how the original table arrangement was with the NamefOfImages table serving as a lookup table?

 

[MajP]

I probably was not clear on the question.
1) Is it good enough for a user to use a file browser dialog and pick the file by name? The user would not be able to see a thumbnail and would have to pick it by name. (They could see thumbnails by using windows explorer, prior to browsing.)

2) Or does the user need to see the images within access to determine which one is appropriate? In this case the file browser would not suffice, because the user would not know the correct file without seeing it.

If the answer is 2 then you would keep your look up table. But the question is how to populate it.
If you look at my demo I have the "add from folder" capability. Assume I had an image folder with 100s or 1000s of images. That function would read through the folder and insert into the table all possible image paths. Now I could show a thumbnail of every image on a form. The user could within access look at all the possible images based on the path in the image look up table. Then click on an image and basically write that path into the Signs table, which would associate that image to a specific sign record. At the same time in the lookup table you would want to identify the image as "assigned" so that it can not be reused. Showing thumbnails pre Access 2007 is difficult. You would instead have to scroll through the images one by one.

Just depends on how you want all of this to work.

 

[hoofit]

My original thought was to download images from a camera to a hd and individually name them. That's why I was looking for an input mask of format. I knew there would be typing involved and wanted to make it as easy as possible for the user. If a user could go to a field that is pre-populated (preceeded by) with something like "C:|", and succeeded by ".bmp" (default value)it would be one less thing to do. Then came the notion of using a browse button and this thread has evolved into what it is now drawing spectators from around the globe....well not really. Anyway, the browser idea is fine which requires the used to individually assign images as he/she scrolls through a list in a table, populated by files in a folder on a hd.It would be necessary for the user to actually see the image and match it although it does not need to be thumbnails. The question remains, how does the table get populated? Conclusively, physically typing it in can lose accuracy and is too much work so my original question has been resolved. The answer appears to be populating thru code (answer #2)

hoof

 

[MajP]

Ok if you want to download images to a folder and then add all of their paths to a table, here is some explanation of the code.

1) This code allows you to browse for a folder.
just call the function "BrowseFolder" and it returns a path to the folder in the folder dialog

Option Compare Database
Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CSIDL Constants of various folder names.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Public Const CSIDL_ADMINTOOLS As Long = &H30
    Public Const CSIDL_ALTSTARTUP As Long = &H1D
    Public Const CSIDL_APPDATA As Long = &H1A
    Public Const CSIDL_BITBUCKET As Long = &HA
    Public Const CSIDL_COMMON_ADMINTOOLS As Long = &H2F
    Public Const CSIDL_COMMON_ALTSTARTUP As Long = &H1E
    Public Const CSIDL_COMMON_APPDATA As Long = &H23
    Public Const CSIDL_COMMON_DESKTOPDIRECTORY As Long = &H19
    Public Const CSIDL_COMMON_DOCUMENTS As Long = &H2E
    Public Const CSIDL_COMMON_FAVORITES As Long = &H1F
    Public Const CSIDL_COMMON_PROGRAMS As Long = &H17
    Public Const CSIDL_COMMON_STARTMENU As Long = &H16
    Public Const CSIDL_COMMON_STARTUP As Long = &H18
    Public Const CSIDL_COMMON_TEMPLATES As Long = &H2D
    Public Const CSIDL_CONNECTIONS As Long = &H31
    Public Const CSIDL_CONTROLS As Long = &H3
    Public Const CSIDL_COOKIES As Long = &H21
    Public Const CSIDL_DESKTOP As Long = &H0
    Public Const CSIDL_DESKTOPDIRECTORY As Long = &H10
    Public Const CSIDL_DRIVES As Long = &H11
    Public Const CSIDL_FAVORITES As Long = &H6
    Public Const CSIDL_FLAG_CREATE As Long = &H8000
    Public Const CSIDL_FLAG_DONT_VERIFY As Long = &H4000
    Public Const CSIDL_FLAG_MASK As Long = &HFF00&
    Public Const CSIDL_FLAG_PFTI_TRACKTARGET As Long = CSIDL_FLAG_DONT_VERIFY
    Public Const CSIDL_FONTS As Long = &H14
    Public Const CSIDL_HISTORY As Long = &H22
    Public Const CSIDL_INTERNET As Long = &H1
    Public Const CSIDL_INTERNET_CACHE As Long = &H20
    Public Const CSIDL_LOCAL_APPDATA As Long = &H1C
    Public Const CSIDL_MYPICTURES As Long = &H27
    Public Const CSIDL_NETHOOD As Long = &H13
    Public Const CSIDL_NETWORK As Long = &H12
    Public Const CSIDL_PERSONAL As Long = &H5   ' My Documents
    Public Const CSIDL_MY_DOCUMENTS As Long = &H5
    Public Const CSIDL_PRINTERS As Long = &H4
    Public Const CSIDL_PRINTHOOD As Long = &H1B
    Public Const CSIDL_PROFILE As Long = &H28
    Public Const CSIDL_PROGRAM_FILES As Long = &H26
    Public Const CSIDL_PROGRAM_FILES_COMMON As Long = &H2B
    Public Const CSIDL_PROGRAM_FILES_COMMONX86 As Long = &H2C
    Public Const CSIDL_PROGRAM_FILESX86 As Long = &H2A
    Public Const CSIDL_PROGRAMS As Long = &H2
    Public Const CSIDL_RECENT As Long = &H8
    Public Const CSIDL_SENDTO As Long = &H9
    Public Const CSIDL_STARTMENU As Long = &HB
    Public Const CSIDL_STARTUP As Long = &H7
    Public Const CSIDL_SYSTEM As Long = &H25
    Public Const CSIDL_SYSTEMX86 As Long = &H29
    Public Const CSIDL_TEMPLATES As Long = &H15
    Public Const CSIDL_WINDOWS As Long = &H24
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional ByVal DialogTitle As String, _
    Optional RootCSIDL As Long = 0) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' BrowseFolder
' This displays the standard Windows Browse Folder dialog. It returns
' the complete path name of the selected folder or vbNullString if the
' user cancelled.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim uBrowseInfo As BrowseInfo
Dim szBuffer As String
Dim lID As Long
Dim lRet As Long


If DialogTitle = vbNullString Then
    DialogTitle = "Select A Folder"
End If

With uBrowseInfo
    .hOwner = 0
    .pidlRoot = RootCSIDL
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = DialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS ' + BIF_USENEWUI
    .lpfn = 0
End With
szBuffer = String$(MAX_PATH, vbNullChar)
lID = SHBrowseForFolderA(uBrowseInfo)

If lID Then
    ''' Retrieve the path string.
    lRet = SHGetPathFromIDListA(lID, szBuffer)
    If lRet Then
        BrowseFolder = Left$(szBuffer, InStr(szBuffer, vbNullChar) - 1)
    End If
End If

End Function

Now that you have the selected folder pass the string to the function "AllFiles". This returns an array of files inside the folder

Public Function AllFiles(ByVal FullPath As String) _
  As String()
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject

'PARAMETER: FullPath = FullPath to folder for
'which you want all files

'RETURN VALUE: An array containing a list of
'all file names in FullPath, or a 1-element
'array with an empty string if FullPath
'does not exist or it has no files

'REQUIRES: Reference to Micrsoft Scripting
'          Runtime

'EXAMPLE:

'Dim sFiles() as string
'dim lCtr as long
'sFiles = AllFiles("C:\Windows\System")
'For lCtr = 0 to Ubound(sFiles)
'  Debug.Print sfiles(lctr)
'Next

'REMARKS:  The FileSystemObject does not
'Allow for the use of wild cards (e.g.,
'*.txt.)  If this is what you need, see
'[URL unfurl="true"]http://www.freevbcode.com/ShowCode.asp?ID=1331[/URL]
'************************************************

Dim oFs As New FileSystemObject
Dim sAns() As String
Dim oFolder As folder
Dim oFile As File
Dim lElement As Long

ReDim sAns(0) As String
If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)
 
    For Each oFile In oFolder.files
      lElement = IIf(sAns(0) = "", 0, lElement + 1)
      ReDim Preserve sAns(lElement) As String
      sAns(lElement) = oFile.Name
    Next
End If

AllFiles = sAns
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function

So you would call this from a form. To pick the folder and write the folder's files to the image table

Private Sub cmdFolder_Click()
  Dim recordID As Long
  Dim strSql As String
  Dim strFolder As String
  Dim pathAndFile As String
  Dim aImages() As String
  Dim i As Integer
  If Me.NewRecord Then Me.Dirty = False
  recordID = Me.ID
  strFolder = BrowseFolder
  aImages = getFiles(strFolder)
  For i = LBound(aImages) To UBound(aImages)
    If isValidImage(getExtension(aImages(i))) Then
      If Not Right(strFolder, 1) = "\" Then strFolder = strFolder & "\"
      strSql = "Insert into tblPics (folderPath, fileName, ParentID) VALUES ('" & strFolder & "', '" & aImages(i) & "'," & recordID & ")"
     ' Debug.Print strSql
      CurrentDb.Execute strSql
    End If
  Next i
  Me.subFrmCtlPics.Form.Requery
End Sub

That is the basics. The demo has more code to tidy this up, but that is the jist. Looks like a lot of code, but most of this is just cut and paste.

 

[hoofit]

MajP
I have a delay in progress due to a hd reformatting issue.

hoof

 

[hoofit]

MajP

OK, I'm back. Glad I had a backup. I'm still trying to make heads or tails out of your recent code, way out of my league. I don't understand what goes where, what goes into a module, if I'm using any previous code, errors on compiling etc. Can you jump in again and point me north? I see where I need a table - tblPics and the various fields ( floderpath, filename etc)but after that I'm lost.

hoof

 

[MajP]

A lot of that code is over my head. i just look at the functions and see what inputs they require and what they return. Do not need to know how to build the watch just how to use it.

 

[hoofit]

MajP,
In your code does this represent a "browse command button" or a form?

"Private Sub cmdFolder_Click()"

thanx

hoof

 

[MajP]

That is the code behind a button on a form. It is what would call a folder browser and the rest of the code calls the other functions to load all of the image paths contained in the folder into a table.

 

[hoofit]

OK I see. Thank you. Had to do some adaptation. Seems that there may be a missing function. The following line results in 'sub or function not defined' error.



If isValidImage(getExtension(aImages(i))) Then

Any thoughts?