Populate Listbox (WIN2000-Acc97)

Topic

New Reply

I have only worked with Access for a couple of years, and believe what I want to do is doable, it’s just that I lack the expertise to code it.

Scenario:
Users will have a folder on their “C” drive called Magazines. In this folder they have scanned the covers of the magazines into jpeg files and named them accordingly. They will continue to add to this folder daily or weekly.
What I would like is the following:

1) A listbox on a form whose record source is the list of files in the Magazine folder.
2) User clicks on the file name inside the listbox and, voila, the file opens with the scanned photo.
My guess is that
1) I need some sort of API call to get the list?
2) Use hyperlinks to open the file?

This is probably more complicated than I can imagine. I even believe something similar was addressed here in the lounge(if you can point to the thread(s) that would get me started.)

I did try the MSKB, but ya know, if you don’t punch in the right keywords…….. I tried populate list box, and didn’t get what I needed.

As always, thank you for reading this and thanks for your direction.
Michael Abrams

Reply | Quote

Replies

This really isn’t too difficult. The function below is from the Access help files. All you have to do is insert a module into your database and paste the ListJPGs function into it. You will have to change “yourfolderpath” to reflect the actual path of your JPEG folder.

Then in the “Row Source Type” property of the listbox put “ListJPGs”.

You can then add an image frame to your form and in the AfterUpdate event of your listbox a line of code to change the picture:

Me!imageframename.Picture = “jpegFolderPath” & Me!ListboxName

Each time you select a file from the listbox the image will be displayed in the image frame.

””””””””””””””””””””””””””
Function ListJPGs(fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant
Static dbs(127) As String, Entries As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Select Case code
Case acLBInitialize ‘ Initialize.
Entries = 0
dbs(Entries) = Dir(“yourfolderpath*.jpg”)
Do Until dbs(Entries) = “” Or Entries >= 127
Entries = Entries + 1
dbs(Entries) = Dir
Loop
ReturnVal = Entries
Case acLBOpen ‘ Open.
‘ Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ‘ Get number of rows.
ReturnVal = Entries
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 = dbs(row)
Case acLBEnd ‘ End.
Erase dbs
End Select
ListJPGs = ReturnVal
End Function

Reply | Quote

You are AWESOME !!!!!!!!!!!!!!!!!!!!
2 things –
Under what topic in the Access Help files
was this located?

When I try to place an image contol on the form, the “Insert Picture” Dialog box pops up.
I am using Access 97 if this makes a difference.
(Or am I using the wrong image control?)

cpod – Thank you SO much. I hope to return the favor someday.

Michael Abrams

Reply | Quote

I was mistaken when I said Access help files. It’s actually under “Row Source Type” in the Visual Basic help files.

No, you have the right control. You have to choose an image file when you add the control. This is the image that will appear when you open the form, before you select anything from the listbox. You could create a blank JPEG file for this purpose.

Reply | Quote

Thanks again cpod. Cyberspace is just amazing

Michael

Reply | Quote

That dialog box just provides a default path/filename for the Picture property. You end up changing this property (and the displayed picture) when the user selects a value in the list. In other words, it doesn’t really matter what file you pick.

You may want to initialize this property with the default listbox selection (e.g., the first one in the list) in the form’s On Load event — using the same code ‘cpod’ provided (or just call the listbox_AfterUpdate routine from the Form_Open event routine. That way the listbox and the image will be “synchronized” when the form is opened.

Hope this helps.

Reply | Quote

OK. I created the module.
Placed a listbox on the form.
Rowsource Type = ListJPGS

Open the form, and the 3 jpegs are listed in the list box.
So far – wonderful.

I placed an image control on the form. I used one of the files in the magazine folder as the default.
I open the form and the image is there but the list box is now blank!!!
I deleted the image control, compacted the db and the listbox is still blank.
I closed the db and reopened it, and the listbox is populated again. What am I missing?

Help !!!

Michael

Couple of more facts – I am using Windows 2000

The folder is located at;
C:Documents and SettingsabramsmMy DocumentsMagazines

In the code you gave me, all I put is dbs(Entries) = Dir(“Magazines*.jpg”)
and it worked.
It didn’t work if I used:
dbs(Entries) = Dir(“Documents and SettingsabramsmMy DocumentsMagazines*.jpg”)

Reply | Quote

Try putting the drive letter in:

Dir(“C:Documents and SettingsabramsmMy DocumentsMagazines*.jpg”)

Reply | Quote

I can now go home for the day !!!

THANK YOU VERY MUCH!!

Reply | Quote

I am using A2000 and have implemented this code and procedure for exactly the same procedure.
My list box fills with the JPG names from my designated folder, all ok.

When I click in the list box though, I get run-time 438 error.
I’ve had this prob before in other forms and stuff and been able to fix it.

However, I cannot find the problem in this instance. I presume it is the difference in A97 & A2000.

How can I remedy this 438 fault.

Dave

Reply | Quote

Dave,

I don’t have A2000, so I can’t really help you here. Error 438 is “Property or method not supported”, so you might be using the wrong kind of control. The image control on your form should be just that – an unbound image control, not one of the OLE controls – Object Frame or Bound Object Frame. See the attached screenshot of the relevant part of the Toolbox.

Regards,
Hans

Reply | Quote

Thanks for that, Perfect

Dave

Reply | Quote

If KeyCode = vbKeyReturn Then
DoCmd.GoToRecord , , acNewRec
Me.EstimateNo = Forms!frmdetails!EstimateNo
Me.supp = Forms!frmdetails!supp
Me.txtRegistration = Forms!frmdetails!Registration
Me.PicFile = Me.lstPreviewJpgs
Me.[imgPicture].Picture = [PicFile]
Me.lstPreviewJpgs.SetFocus

I use this on keydown with the list.

The problem is [PicFile] is a field on my form and requires the full path to allow [imgPicture] to work correctly.

At the moment lstPreviewJpgs will return (example) 123.jpg to [picfile] instead of L:Home123.jpg

Can any-one help with this.

Dave

Reply | Quote

If all jpg’s are stored in L:Home, try

Me.[imgPicture].Picture = “L:Home” & [PicFile]

If you have the pathname stored in a control or variable, you can use that instead of the literal string “L:Home”. Don’t forget to add a trailing backslash if necessary.

Reply | Quote

Slightly different but it now works

If KeyCode = vbKeyReturn Then
DoCmd.GoToRecord , , acNewRec
Me.EstimateNo = Forms!frmdetails!EstimateNo
Me.supp = Forms!frmdetails!supp
Me.txtRegistration = Forms!frmdetails!Registration
Me.PicFile = “L:Home” & Me.lstPreviewJpgs
‘Me.PicFile = Me.lstPreviewJpgs

Me.[imgPicture].Picture = [PicFile]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!frmImages.SetFocus
Forms!frmImages.lstPreviewJpgs.SetFocus
Else

Thanks.
Dave

Reply | Quote

The whole thing is working but I have a knawing problem with it.

Perhaps if I break it down, some-one will see whats happening.
Now, when I try to open the form, if there is no record, then the message box states the obvious, “You have no images”
———————————————————————————————————————————————
Private Sub Form_Open(Cancel As Integer)
DoCmd.Close acForm, “frmCreateEstimate”, acSaveYes
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox “You Have No Current Images For This File, Press OK”
Me.lstPreviewJpgs.SetFocus
End If
End Sub
—————————————————————————————–

Press ok and the form opens with all blank fields waiting for a selected picture.

The user has the focus in the list (lstPreviewJpgs) and uses the up / down arrow keys to go to a selection. This part is fine and the previewOLE (ignore OLE, its only a name) changes ok as it should.
——————————————————————————————
Private Sub lstPreviewJpgs_AfterUpdate()
Me!PreviewOLE.Picture = “L:Home” & Me!lstPreviewJpgs
Forms!frmImages.SetFocus
Forms!frmImages.lstPreviewJpgs.SetFocus
End Sub
—————————————————————————————————————————————

Now, here’s where the glitch is, on KeyDown the user press’s Enter when he has reached the picture of his selection.
————————————————————————————-
Private Sub lstPreviewJpgs_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
DoCmd.GoToRecord , , acNewRec
Me.EstimateNo = Forms!frmDetails!EstimateNo
Me.supp = Forms!frmDetails!supp
Me.txtRegistration = Forms!frmDetails!Registration
Me.PicFile = “L:Home” & Me.lstPreviewJpgs
‘Me.PicFile = Me.lstPreviewJpgs
Me.[imgPicture].Picture = [PicFile]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!frmImages.SetFocus
Forms!frmImages.lstPreviewJpgs.SetFocus
Else
End If
On Error GoTo errtrap
If KeyCode = vbKeyPageUp Then Me.txtRegistration.SetFocus
If KeyCode = vbKeyPageUp Then DoCmd.GoToRecord , , acPrevious
If KeyCode = vbKeyPageDown Then Me.txtRegistration.SetFocus
If KeyCode = vbKeyPageDown Then DoCmd.GoToRecord , , acNext
errtrap:
Exit Sub
End Sub
——————————————————————————————————————-

The code correctly assigns EstimateNo, Supp, Registration into a new record, saves it BUT, Looking at the navigation bar at the bottom of the form, the record number is now 2.
Its saved the first record with all ness details but moved over to record number 2.
If the form is closed and re-opened, the correct record number is at 1.

Below I have pasted all the code from the form in case I have missed something.
—————————————————————————-
—————————————————————————-

Option Compare Database
Option Explicit

Public Function Proper()
Screen.ActiveControl = StrConv(Screen.ActiveControl, vbProperCase)
End Function

Private Sub cmdClose_Click()
On Error Resume Next
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdErasePic_Click()
If Not IsNull([PicFile]) Then
If MsgBox(“The image will be removed from this record. Are you sure?”, vbYesNo + vbQuestion) = vbYes Then
[imgPicture].Picture = “”
[PicFile] = Null
[EstimateNo] = Null
[supp] = Null
[Registration] = Null
[ImageCreated] = Null
[Comment] = Null
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
SysCmd acSysCmdClearStatus
Forms!frmImages.Requery
End If
End If
End Sub

Private Sub cmdInsertPic_Click()
If Me.RecordsetClone.RecordCount = 0 Then MsgBox “You No Current Images For This File, Press OK”
Dim OFN As OPENFILENAME
On Error GoTo Err_cmdInsertPic_Click
DoCmd.GoToRecord , , acNewRec
Me.EstimateNo = Forms!frmDetails!EstimateNo
Me.supp = Forms!frmDetails!supp
Me.txtRegistration = Forms!frmDetails!Registration
‘ Set options for dialog box.
With OFN
.lpstrTitle = “Images”
If Not IsNull([PicFile]) Then .lpstrFile = [PicFile]
.lpstrInitialDir = “L:home”
.flags = &H1804 ‘ OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
.lpstrFilter = MakeFilterString(“Image files (*.bmp;*.gif;*.jpg;*.wmf)”, “*.bmp;*.gif;*.jpg;*.wmf”, _
“All files (*.*)”, “*.*”)
End With
If OpenDialog(OFN) Then
[PicFile] = OFN.lpstrFile
[imgPicture].Picture = [PicFile]
SysCmd acSysCmdSetStatus, “Image Loaded: ‘” & [PicFile] & “‘.”
Forms!frmImages.SetFocus
Me.EstimateNo.SetFocus
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
Exit Sub
Err_cmdInsertPic_Click:
MsgBox Err.Description, vbExclamation
End Sub

Private Sub cmdPreview_Click()
On Error GoTo HandleErr
Dim intButSelected As Integer, intButType As Integer
Dim strMsgPrompt As String, strMsgTitle As String
strMsgPrompt = “You Have Selected To Print, Do You Want To Continue !!”
strMsgTitle = “Printing ”
intButType = vbYesNo + vbDefaultButton2
intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)
If intButSelected = vbYes Then
If IsNull([EstimateNo]) Then
MsgBox “There is no data for this report. Canceling report…”, vbInformation
Else
RunCommand acCmdSaveRecord
DoCmd.OpenReport “Image Thumbs”, acNormal, , “[EstimateNo] = ” & [EstimateNo]
Forms!frmImages.SetFocus
Forms!frmImages!EstimateNo.SetFocus
If intButSelected = vbNo Then
Forms!frmImages.SetFocus
Forms!frmImages!EstimateNo.SetFocus
End If
End If
End If
Exit Sub
HandleErr:
MsgBox Err.Description, vbExclamation
End Sub

Private Sub Command64_Click()
‘setfocu back to the list box
Me.lstPreviewJpgs.SetFocus
End Sub

Private Sub Form_Close()
On Error GoTo errtrap
Forms!frmDetails.SetFocus
Forms!frmDetails!DummyEst.SetFocus
Exit Sub
errtrap:
End Sub

Private Sub Form_Current()
On Error GoTo HandleErr
If Not IsNull([PicFile]) Then
Me.[imgPicture].Picture = [PicFile]
SysCmd acSysCmdSetStatus, “Image: ‘” & [PicFile] & “‘.”
Else
‘[imgPicture].Picture = “”
SysCmd acSysCmdClearStatus
End If
Exit Sub
HandleErr:
If Err = 2220 Then
‘[imgPicture].Picture = “”
SysCmd acSysCmdSetStatus, “Can’t open image: ‘” & [PicFile] & “‘”
Else
MsgBox Err.Description, vbExclamation
End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyF5 Then Call cmdClose_Click
If KeyCode = vbKeyF3 Then Call cmdInsertPic_Click
If KeyCode = vbKeyF6 Then Call cmdPreview_Click
If KeyCode = vbKeyF2 Then Call Command64_Click
If KeyCode = vbKeyDelete Then Call cmdErasePic_Click
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Close acForm, “frmCreateEstimate”, acSaveYes
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox “You Have No Current Images For This File, Press OK”
Me.lstPreviewJpgs.SetFocus
End If
End Sub

Private Sub Command55_Click()
‘Opens the report
On Error GoTo Err_Command55_Click
Dim stDocName As String
stDocName = “Image Thumbs”
DoCmd.OpenReport stDocName, acNormal
Exit_Command55_Click:
Exit Sub
Err_Command55_Click:
MsgBox Err.Description
Resume Exit_Command55_Click
End Sub

Private Sub lstPreviewJpgs_AfterUpdate()
Me!PreviewOLE.Picture = “L:Home” & Me!lstPreviewJpgs
Forms!frmImages.SetFocus
Forms!frmImages.lstPreviewJpgs.SetFocus
End Sub

Private Sub lstPreviewJpgs_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
DoCmd.GoToRecord , , acNewRec
Me.EstimateNo = Forms!frmDetails!EstimateNo
Me.supp = Forms!frmDetails!supp
Me.txtRegistration = Forms!frmDetails!Registration
Me.PicFile = “L:Home” & Me.lstPreviewJpgs
Me.[imgPicture].Picture = [PicFile]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!frmImages.SetFocus
Forms!frmImages.lstPreviewJpgs.SetFocus
Else
End If
On Error GoTo errtrap
If KeyCode = vbKeyPageUp Then Me.txtRegistration.SetFocus
If KeyCode = vbKeyPageUp Then DoCmd.GoToRecord , , acPrevious
If KeyCode = vbKeyPageDown Then Me.txtRegistration.SetFocus
If KeyCode = vbKeyPageDown Then DoCmd.GoToRecord , , acNext
errtrap:
Exit Sub
End Sub
——————————————–
——————————————–

This really stumping me.
Dave

Reply | Quote

Hers pic2 showing the navigation bar and the fields at the top of the form

Reply | Quote

Dave,

Access will handle some keys, such as Enter and Tab, automatically, unless you prevent it. In the lstPreviewJpgs_KeyDown code, set KeyCode = 0 to prevent further handling of the key. Off course, you must do this *after* testing its original value, not right at the beginning. Otherwise, the tests If KeyCode = … Then will fail.

BTW, I’d like to suggest the use of Select Case for testing the KeyCode:

Select Case KeyCode
Case vbKeyReturn
‘ code for Enter goes here
…
KeyCode = 0
Case vbKeyPageUp
‘ code for PageUp goes here
…
Case …
End Select

Regards,
Hans

Reply | Quote

I have an additional question.

I would like to be able to use the solution, however, I need to point to a relative path. Not everyone’s path is going to be the same, but at the database file level and below it will be. For example:

One guy might have his my documents at C:My Documents and another at D:My Documents. In the examples in the thread, absolute paths are used:

Me.[imgPicture].Picture = “L:Home” & [PicFile]

For my example it would be something like:

Me.[imgPicture].Picture = “C:My DocumentsMagazineImages” & [PicFile]

This will not work because different people have different paths. However, every one should have the same relative path.

The database is in a folder called Magazine. That folder contains a subfolder called Images. So everyone should be able to show the pictures with a relative path like:

Me.[imgPicture].Picture = “../images/” & [PicFile] or Me.[imgPicture].Picture = “../../images/” & [PicFile]

Does anyone know if this is possible. If yes, how do you do it?

Thanks.

Reply | Quote

Check the code (cmdInsertPic) this uses a common dialog box.
This opens at a specified drive and folder but with the option to browse any drive & folder.
You will need the (on current) event also.

As you see mines a little bit of a hybrid to say the least. I will eventually remove the common dialog once I get it to work correctly.

I think the fault in my code is the (on Current) event conflicting.

Dave

Reply | Quote

You can get the full name of the database (including path) using CurrentDb.Name.

To extract the path, you can use the following function:

‘ Extracts the file path from the specified full name (without trailing backslash)

Function GetPath(strName As String) As String
Dim i As Integer
If InStr(strName, “”) = 0 Then Exit Function
i = Len(strName)
While Mid(strName, i, 1) “”
i = i – 1
Wend
GetPath = Left(strName, i – 1)
End Function

(This works in all versions of Access. In Access 2000 and up, a shorter version using InstrRev is possible)

So you might use a string variable strPath to hold the path of the images:

strPath = GetPath(CurrentDb.Name) & “Images”
Me.[imgPicture].Picture = strPath & [PicFile]

Reply | Quote

Hans

You caught me out again, Yes i’m a little scruffy where code is concerned.
I promise to use the Select Case in the future and I will write it out 100 times as punishment.

Edited. The select case did the trick.
Thanks Hans.
Any other recommendations would be appreciated on my code.
Dave

Reply | Quote

Dave,

I see nothing wrong with the OnCurrent event.

From your screenshot, I suspect that the list box is the last control in the tab order of the form. So, if you press Enter while the focus is on the listbox, the focus will shift to the next control – which by default is the first control of the next record.

You can do two things to prevent this:
(1) Set the Cycle property of the form to Current Record, -OR-
(2)Set KeyCode = 0 in the part of lstPreviewJpgs_KeyDown that handles the Enter key, as I suggested in the first part of my previous reply.

HTH,
Hans

Reply | Quote

Dave,

If you’ll allow more nitpicking: you have some code that has probably been created by the Command Button Wizard. Strangely enough, this wizard still creates antiquated AccessBasic code (for Access 95 and below) in Access 97, 2000 and even in XP. In the help files, Microsoft recommends not using this code any more. Apparently, its programmers don’t read the help files (but that doesn’t really surprise me).

You can replace

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

by

RunCommand acCmdSaveRecord

Similarly, you can replace

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

by

RunCommand acCmdDeleteRecord

Reply | Quote

I shall go along with your recommendations greatfully.

There is nothing wrong with nit picking..

Thanks

Dave

Reply | Quote