Coding for pull-down menu

[PALman]

I have coding behind a pull-down menu which finds and displays a drawing which is actually an adobe pdf file which has been saved in a particular folder.
The code runs well and finds the drawing which is named with a Job Number followed by -DWG for example 222555-1-DWG for first drawing and 222555-2-DWG for second drawing. The 222555 is the Job Number and is taken from the textbox, named txtJobNo from the current record of the form.
When I click on Drawings in the pull down menu I would like to display a list of one or more drawings found in the folder and then be able to open each one from the list. The code I have at the moment automatically opens the first drawing found only...

Private Sub mnuDrawings_Click()
'Pulldown Menu to display Drawing/s

Dim Path As String, dwgFile As String, JobNo As String

JobNo = txtJobNo.Text
Path = "C:\JobsFolder\" & JobNo & "\"
dwgFile = JobNo & "-" & "DWG.pdf"

'Requires reference to Microsoft Scripting Runtime to use the FileSystemObject.
With New FileSystemObject
If .FileExists(Path & dwgFile) Then
ExecuteWait Path & dwgFile
Else
MsgBox "File " & Path & dwgFile & " does not exist."
End If
End With
End Sub

Any help is much appreciated!

[RhinoBull]

How is it related to database?

[Hack]

Moved

[PALman]

Sorry,
It is related to Access 2000 table using ADODB.

[RhinoBull]

Can you please ellaborate presizely what you need?

[PALman]

I have a form with textboxes and checkboxes linked by ADODB to the fields of an Access 2000 table named Enquiry.mdb.
The key/primary field is called JobNo and is to be found in a ComboBox called cboJobNo which when clicked displays all the records by Job Numbers.
Also above this I have a series of pull-down menus I created by usig Menu Editor (CTRL+E). One of these menus (Customer docs) drops down a list of items like Specifications BOMS and Drawings.
I want code behind the Drawing option...
Private Sub mnuDrawings_Click()
which will look at the current JobNo on the form
go to the folder which holds the drawings for that Job and
display a list of Drawings which it finds there
and then allows user to open any one of the drawings on that list.
Essentially its a bit like a combobox linked to a field of a table but I need something similar to that from a pull-down menu.
As I said in my initial post the code I have works but only shows one drawing where there might be more. If I use * for all drawings then it displays them all one after the other. What I need is to be able to select one drawing at a time. Please note drawings are Adobe pdf files.
I hope this is abit clearer if not I shall try to reword later.

[RhinoBull]

Private Sub mnuDrawings_Click()
which will look at the current JobNo on the form
go to the folder which holds the drawings for that Job and
display a list of Drawings which it finds there
and then allows user to open any one of the drawings on that list.

And you want the list to be displayed in the menu? Or am I wrong?
If am correct then ordinary Listbox (or combobox for that matter) would work better.
My concern is that number of drawings might be unknown for the selected job so the list could be pretty lengthy.

[Hack]

Do you want to dynamically add menu items to your drop down menu which correspond to each file name in the folder?

[PALman]

RihnoBull
Yes I would prefer to have list within the pull-down menu rather than Listbox or Combobox. There would never be any more than 4 or 5 drawings.
Hack,
Yes I want to dynamically add menu items to the drop down menu which correspond to each file name in the folder.
Thanks guys!

[PALman]

Is it possible to have a pull-down menu dynamically display a list of files in a folder.
So to display a "Adobe.pdf" file user would click on pull-down menu option Customer Documentation which would drop down...
Purchase Order
Bill of Materials
Drawings
Specifications
Then by clicking Drawings this would present user with a list of 4 or 5 drawings like...
222555-1-DWG.pdf
222555-2-DWG.pdf
to
222555-5-DWG.pdf
Which would then allow the user to display what drawing he chooses for the list.
Any help as always would be much appreciated.

[RhinoBull]

It is possible and the simplest way would be to utilize control array.
Here are the steps to follow:
- create menu item (say File and name it mnuFile)
- create submenu for that menu and name it mnuFiles
- change Index (also in the menu editor) for mnuFiles to 0 (zero)
- leave Caption property blank
- copy and paste code below to anywhere in your project and run it:

Private Sub Command1_Click()
Dim sfolder$, sfile$

    sfolder = "c:\"
    sfile = Dir(sfolder, vbNormal)
    
    Do While sfile <> ""
        If sfile <> "." And sfile <> ".." Then
            If Right(LCase(sfile), 3) = "txt" Then
                mnuFiles(mnuFiles.UBound).Caption = sfile
                Load mnuFiles(mnuFiles.UBound + 1)
                mnuFiles(mnuFiles.UBound).Visible = True
            End If
        End If
        sfile = Dir
    Loop
    
    If mnuFiles.UBound > 0 And mnuFiles(mnuFiles.UBound).Caption = "" Then
        Unload mnuFiles(mnuFiles.UBound)
    End If

End Sub

[PALman]

Thanks RhinoBull
I changed some of your coding to fit in with my program but unfortunately I get error "Method or data member not found".
Coding is now as below...
Private Sub mnuDrawings_Click()
Dim sfolder$, sfile$, JobNo As String

sfolder = "C:\000-QualityControlProgram\JobsFolder\" & JobNo & "\"
sfile = Dir(sfolder, vbNormal)

Do While sfile <> ""
If sfile <> "." And sfile <> ".." Then
If Right(LCase(sfile), 3) = "txt" Then
mnuDrawings(mnuDrawings.UBound).Caption = sfile
Load mnuDrawings(mnuDrawings.UBound + 1)
mnuDrawings(mnuDrawings.UBound).Visible = True
End If
End If
sfile = Dir
Loop

If mnuDrawings.UBound > 0 And mnuDrawings(mnuDrawings.UBound).Caption = "" Then
Unload mnuDrawings(mnuDrawings.UBound)
End If
End Sub
Any further help much appreciated

[PALman]

I forgot to add the error is on line...
mnuDrawings(mnuDrawings.UBound).Visible = True
where program stops at .UBound

[Hack]

Is mnuDrawings in a menu array?

[RhinoBull]

Is mnuDrawings in a menu array?

I'm sure it isn't...

@PALman: did you initialize Index property for your mnuDrawings menu item as suggested in my previous post?

[PALman]

Yes RihnoBull
but when I did that, the code did not run, stopping with error...
“Procedure declaration does not match description of event or procedure having the same name”

[PALman]

Sorry Hack,
I think mnuDrawings is a menu array or if it's I am trying to set it up as such, but not quite sure just now how to achieve that.

[RhinoBull]

Yes RihnoBull
but when I did that, the code did not run, stopping with error...
“Procedure declaration does not match description of event or procedure having the same name”

That would happen if you had some code written in your mnuDrawings_Click event so VB doesn't add another argument (Index) automatically (I think it's a bug).
Solution:

- go to mnuDrawings_Click event and add Index argument to procedure header

It may look like this:

Private Sub mnuDrawings_Click(Index As Integer)
    'your code goes here...
End Sub

[PALman]

Thanks Rhinobull,
Menus are now working but the list is blank.
I have tried adding in a "hard" value for JobNo and did a debug.print and it returned in immediate window...
C:\000-QualityControlProgram\JobsFolder\222222\ 222222 DWG50108 p2.pdf
Which means I am almost there as it can see a pdf file in folder
Just how to get the list to fill up is my next step.
I need to go to bed now (its late here in UK). Try again tomorrow
Thanks RhinoBull

[RhinoBull]

Thanks Rhinobull,
Menus are now working but the list is blank.

That could be because my sample is searching TXT files and you need (as far as you posting goes) PDFs.
So if you replace what you have with this it should work:

If Right(LCase(sfile), 3) = "pdf" Then '<<< make sure file extension is correct

[PALman]

Thanks again RhinoBull
I already changed the "txt" to "pdf" before I went to bed last night. I think that's why it captured the file "DWG50108 p2.pdf" in the immediate window as per above.
I put the Debug.Print sFolder, JobNo, sFile line in BEFORE the DO WHILE LOOP.
Its 08:00 here just now and I shall return to project around 17:00 (9 hours)
Many thanks for your help.

[PALman]

RhinoBull,
Your coding is working but here is what I am getting...
Click on TOP pulldown menu "Customer Documentation" is OK and gives...
Bill of Materials
Drawings
Specification etc.
Click on Drawings gives a submenu with no caption/s
Click on Blank Caption submenu nothing happens but action seems to trigger list because when I repeat from Customer Documentation / Drawings then a list of PDF files is displayed.
Next problem is the PDF files listed show the same file at position 1 and again as the last on the list.
Next when user clicks on any of the displayed PDF list nothing happens... no error and no loading/displaying/opening of PDF file/document.
Any help is as always much appreciated.
Thanks

[PALman]

Any thoughts on where I am going wrong here.
When I click on TOP pulldown menu "Customer Documentation" this is OK and gives dropdown list of submenu's thus...
Bill of Materials
Drawings
Specifications
Purchase Order... etc.
When I click on Drawings this produces a submenu with NO caption
Click on Blank Caption submenu nothing happens but this action seems to trigger list because when I repeat from Customer Documentation / Drawings then a list of PDF files is displayed.
Next problem is the PDF files listed show the same file at position 1 and again as the last on the list.
Next when user clicks on any of the displayed PDF list nothing happens... no error and no displaying/opening of PDF document.
However I have just noticed that although clicking on the list doesn't open any file on the list but it does repeat the list so that the pulldown submenu grows by repeating itself over and over each time I try to select a PDF file to open.
The latest coding I have is...

Private Sub mnuDrawings_Click(Index As Integer)
Dim sfolder$, sfile$, JobNo As String
'Return JobNo from current record
recOne.Open
JobNo = txtJobNo
recOne.Close
 
    sfolder = "C:\000-QualityControlProgram\JobsFolder\" & JobNo & "\"
    sfile = Dir(sfolder, vbNormal)
Debug.Print sfolder, JobNo, sfile
    Do While sfile <> ""
        If sfile <> "." And sfile <> ".." Then
            If Right(LCase(sfile), 3) = "pdf" Then
                mnuDrawings(mnuDrawings.UBound).Caption = sfile
                Load mnuDrawings(mnuDrawings.UBound + 1)
                mnuDrawings(mnuDrawings.UBound).Visible = True
             End If
         End If
                sfile = Dir
        Loop
    
    If mnuDrawings.UBound > 0 And mnuDrawings(mnuDrawings.UBound).Caption = "" Then
        Unload mnuDrawings(mnuDrawings.UBound)
    End If
 
End Sub

Any help is as always much appreciated.
Thanks

[PALman]

In a Pull-down Menu is it possible to create a sub-menu using the Caption property in the Menu Editor (Ctrl+E) to hold a list of files AND then open one file when selected?
All above coding shows how to achieve the first part and create the list of files but the second part to open a file is got me wondering if I am trying to achieve something that cannot be done.
I would like to make things clearer but although much of this is down to the coding, it is difficult to show what I have and need to do in the Menu Editior.
Thanks in advance and for previous help.