file information

[Guest]

Hello

I have a database that has unique numbers that relate to a certain device.
Each of these devices have subsequent digital photos.
I wish to create a form that lists all information about the devices, but
also provides a command button that views each individual photo

Now rather than populate a table that has a one to many link between the
device unique number and a file name/photo number, i wish to generate a
subform that lists the files that I have put in a subfolder that has the same
corresponding device numbers.

i.e. so I have a device PJ020, it has a folder in the database home
directory called PJ020, and the folder has three files.

I figure I will use the .filesearch object in the Office library, but I am
not sure how to get the information onto a form.
Would the best method be to use an ADO recordset then populate with the
filesearch information and then 'upload' the recordset into a form?

if not, what would be the best method??

 

[Dirk Goldgar]

Hello

I have a database that has unique numbers that relate to a certain
device. Each of these devices have subsequent digital photos.
I wish to create a form that lists all information about the devices,
but also provides a command button that views each individual photo

Now rather than populate a table that has a one to many link between
the device unique number and a file name/photo number, i wish to
generate a subform that lists the files that I have put in a
subfolder that has the same corresponding device numbers.

i.e. so I have a device PJ020, it has a folder in the database home
directory called PJ020, and the folder has three files.

I figure I will use the .filesearch object in the Office library, but
I am not sure how to get the information onto a form.
Would the best method be to use an ADO recordset then populate with
the filesearch information and then 'upload' the recordset into a
form?

if not, what would be the best method??

You may be able to get the information into an ADO recordset, either by
building the recordset in code or by querying some WMI schema (which I
don't actually know is possible). As an alternative, it's quite easy to
load a list of files into a list box, using nothing but built-in
functions. Would that suit your purpose?

 

[Guest]

loading a list of files into a list box would be the easiest option, as it
would allow the user to select the photo they wish to open.
How would you go about doing that?

PS my knowledge of TLAs is limited. What is WMI?

 

[Dirk Goldgar]

loading a list of files into a list box would be the easiest option,
as it would allow the user to select the photo they wish to open.
How would you go about doing that?

You create a user-defined RowSourceType function, as described in the
help file under "RowSourceType Property" and "RowSourceType Property
(User-Defined Function) - Code Argument Values". Then you set the list
box's RowSourceType property to the name of this function, and leave the
RowSource property blank.

Here's an example of a RowSourceType function that fills the list box
with up to 1000 files from a folder specified by the text box txtFolder,
whose names match the pattern specified by txtPattern (defaulting to
"*.*"). The list is sorted using a helper procedure, QSArray(), which I
got off the 'net somewhere.

'----- start of code -----
Function FillFileList( _
fld As Control, _
ID As Variant, _
row As Variant, _
col As Variant, _
code As Variant) _
As Variant

Static avarFiles(1000) As Variant
Static intEntries As Integer
Dim ReturnVal As Variant

ReturnVal = Null
Select Case code
Case acLBInitialize ' Initialize.
intEntries = 0
avarFiles(intEntries) = _
Dir(Me.txtFolder & "\" & Nz(Me.txtPattern, "*.*"))
Do Until avarFiles(intEntries) = vbNullString _
Or intEntries >= 1000
intEntries = intEntries + 1
avarFiles(intEntries) = Dir
Loop
If intEntries > 1 Then
QSArray avarFiles, LBound(avarFiles), intEntries - 1
End If
ReturnVal = intEntries
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = intEntries
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1
Case acLBGetValue ' Get data.
ReturnVal = avarFiles(row)
Case acLBEnd ' End.
Erase avarFiles
End Select

FillFileList = ReturnVal

End Function

Private Sub QSArray( _
arrIn() As Variant, _
ByVal intLowBound As Integer, _
ByVal intHighBound As Integer)

Dim intX As Integer
Dim intY As Integer
Dim varMidBound As Variant
Dim varTmp As Variant

If intHighBound > intLowBound Then
varMidBound = arrIn((intLowBound + intHighBound) \ 2)
intX = intLowBound
intY = intHighBound
Do While intX <= intY
If arrIn(intX) >= varMidBound _
And arrIn(intY) <= varMidBound _
Then
varTmp = arrIn(intX)
arrIn(intX) = arrIn(intY)
arrIn(intY) = varTmp
intX = intX + 1
intY = intY - 1
Else
If arrIn(intX) < varMidBound Then
intX = intX + 1
End If
If arrIn(intY) > varMidBound Then
intY = intY - 1
End If
End If
Loop
Call QSArray(arrIn(), intLowBound, intY)
Call QSArray(arrIn(), intX, intHighBound)
End If
End Sub
'----- end of code -----

PS my knowledge of TLAs is limited. What is WMI?

Windows Management Instrumentation. But that's about all I know about
it.

 

[Larry Linson]

Hello

I have a database that has unique numbers that relate to a certain device.
Each of these devices have subsequent digital photos.
I wish to create a form that lists all information about the devices, but
also provides a command button that views each individual photo

Now rather than populate a table that has a one to many link between the
device unique number and a file name/photo number, i wish to generate a
subform that lists the files that I have put in a subfolder that has the same
corresponding device numbers.

It is rarely ever productive, if what you are doing is for the user, to
force the user to select rather than have the link automated; if your form
is for creating the records, then you are on the right track. You may need
some code to obtain the file name and extension of the images in a
particular folder, in order to populate the list box.

The code to pick up the file names seems a straightforward use of the Dir
function, after using the Windows API to allow the user to browse for and
choose the folder (see http://www.mvps.org/access/api/api0002.htm). Once you
have populated the List Box, then you can handle the images with one of the
three approaches shown in the Imaging examples at
http://accdevel.tripod.com.

Larry Linson
Microsoft Access MVP

 

[Guest]

OK I think I've got the jist of how this code works. But now I have a
timing(?) issue The listbox should populate when the form opens. However, it
is trying to populate before other information that it requires is present.
i.e. it relys on the unique part number - as well as other - to determine
which path to search for the relvevant files.

I get a runtime error '2424'
The expression you entered has a field, control, or property name that
Access can't find


Maybe my code might indicate the problem

note: frm_information is the current form and the one containing the list box
all the combo boxes referred to are also on this form


--- start code ---

Function ListPhoto(fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant

'this function supports the file that will be entered
'into the list box on the information form

Static Photos(100) As String, PhotoNum As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Dim Path As String

Path = PathName(Forms!frm_Information.Combo_Plant.Value)


Select Case code
Case acLBInitialize ' Initialize.
PhotoNum = 0
Photos(PhotoNum) = Dir(Path)
Do Until Photos(PhotoNum) = "" Or Entries >= 100
Entries = Entries + 1
Photos(PhotoNum) = Dir
Loop
ReturnVal = PhotoNum
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = PhotoNum
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1
Case acLBGetValue ' Get data.
ReturnVal = Photos(row)
Case acLBEnd ' End.
Erase Photos
End Select
ListPhoto = ReturnVal
End Function


Function PathName(Plant As String) As String
'this function returns the pathname that is required to populate
'the list box on the information form with photo information

If Plant = "RIVERSIDE" Then

PathName = Application.CurrentProject.Path & "\Photos\DB\RIVERSIDE\"
& _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"

ElseIf Plant = "GOONYELLA" Then

If Forms!frm_Information.Combo_Plant_Location.Value =
"ADMIN/OFFICES" Then
PathName = Application.CurrentProject.Path &
"\Photos\DB\GOONYELLA\ADMINOFFICES" & _
"\" & Forms!frm_Information.Combo_Level.Value & "\" & _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"
Else
PathName = Application.CurrentProject.Path &
"\Photos\DB\GOONYELLA\" & _
frm_Information.Combo_Plant_Location.Value & _
"\" & Forms!frm_Information.Combo_Level.Value & "\" & _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"

End If

Else

End If

End Function

--- end code ---

 

[Dirk Goldgar]

OK I think I've got the jist of how this code works. But now I have a
timing(?) issue The listbox should populate when the form opens.
However, it is trying to populate before other information that it
requires is present. i.e. it relys on the unique part number - as
well as other - to determine which path to search for the relvevant
files.

I get a runtime error '2424'
The expression you entered has a field, control, or property name that
Access can't find


Maybe my code might indicate the problem

note: frm_information is the current form and the one containing the
list box all the combo boxes referred to are also on this form


--- start code ---

Function ListPhoto(fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant

'this function supports the file that will be entered
'into the list box on the information form

Static Photos(100) As String, PhotoNum As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Dim Path As String

Path = PathName(Forms!frm_Information.Combo_Plant.Value)


Select Case code
Case acLBInitialize ' Initialize.
PhotoNum = 0
Photos(PhotoNum) = Dir(Path)
Do Until Photos(PhotoNum) = "" Or Entries >= 100
Entries = Entries + 1
Photos(PhotoNum) = Dir
Loop
ReturnVal = PhotoNum
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = PhotoNum
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1
Case acLBGetValue ' Get data.
ReturnVal = Photos(row)
Case acLBEnd ' End.
Erase Photos
End Select
ListPhoto = ReturnVal
End Function


Function PathName(Plant As String) As String
'this function returns the pathname that is required to populate
'the list box on the information form with photo information

If Plant = "RIVERSIDE" Then

PathName = Application.CurrentProject.Path &
"\Photos\DB\RIVERSIDE\" & _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"

ElseIf Plant = "GOONYELLA" Then

If Forms!frm_Information.Combo_Plant_Location.Value =
"ADMIN/OFFICES" Then
PathName = Application.CurrentProject.Path &
"\Photos\DB\GOONYELLA\ADMINOFFICES" & _
"\" & Forms!frm_Information.Combo_Level.Value & "\" & _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"
Else
PathName = Application.CurrentProject.Path &
"\Photos\DB\GOONYELLA\" & _
frm_Information.Combo_Plant_Location.Value & _
"\" & Forms!frm_Information.Combo_Level.Value & "\" & _
Forms!frm_Information.TxtB_DB_Number.Value & "\*.jpg"

End If

Else

End If

End Function

--- end code ---

The main thing you're going to want to do is not set the list box's
RowSourceType property to the function name until there's a value in the
various controls that are used to determine the folder path. So leave
the RowSourceType property blank at design time, and then later, when
those controls have been filled in, assign to it:

Me.YourListboxName.RowSourceType = "ListPhoto"

(replacing "YourListboxName" with the name of your list box).

I also see what I believe is an error in your code. In these lines ...

Do Until Photos(PhotoNum) = "" Or Entries >= 100
Entries = Entries + 1
Photos(PhotoNum) = Dir

.... I believe that "Entries" should be "PhotoNum".

Further, though it isn't an actual error, your references to controls on
the current form will be more efficient if you replace
"Forms!frm_Information" with just "Me". You also have a place where you
write:

PathName = Application.CurrentProject.Path &
"\Photos\DB\GOONYELLA\" & _
frm_Information.Combo_Plant_Location.Value & _

That unqualified reference to "frm_Information" -- should have been
"Forms!frm_Information" -- will fail, so replace it wit "Me".

 

[Guest]

Hello to all who wrote in this thread.

this is a very interesting read, but a lot over my head. i believe i am
trying to do something similar and was hoping that i could ask some
questions. understand i have built databases, but i am one of those cut and
paste type amatuers.

i have a subform which is based on a table where i want to be able to have
links to files and pictures. i have the table and the form all ready to go,
but i need to create buttons to "browse" for the files and pictures.

i know how to create simple command buttons, but how do i pass the
information to the fields?

i have a table and a form called "IssueAttachments". the form is a subform
for the "Issues" form. in the "Issues" table there are units which will have
multiple issues over time and each issue might have files that relate to it.
so there is a one unit to multiple issues to multiple files relationship type
thing. for each "IssueAttachment" i have allowed for one file and one picture
to be added to the form. each set of attachments has a unique id.

so the field for the file to be attached is called "IssueFile" and the
picture field is named "IssuePicture". i am going to create 2 buttons one for
each attachment field.

where do i start? would the code you have posted here help? i walked into
the command button wizard but there is nothing that i see that would start me
moving towards attaching files. thanks in advance for any and all help.