Showing a list of documents in an unbound list box

[Mr_Si]

Hi there,

In my database, (an order and enquiry and project tracking database), I have a page on the main form where I'd like to be able to show the items located in a file path (set in a field next to it). This needs to be the case for the invoices folder, the quotes folder and the general project files folder.

I know there is a way to do this but I'm not sure how.

I have a table "tblAssocDocs" with:
EnquiryID, (foreign pk)
chrQuotePath,
chrInvoicePath,
chrProjectPath

My form looks like this (at the moment)

There are currently no unbound list boxes or OLEObject thingies, but they will be placed in the sunken frames under each heading. The idea is that on clicking an item in the unbound box a user can click an "open" button (not yet added) to open the selected word document or excel file etc.

I couldn't find out how to do this in my Access 2003 Bible and now that's been stolen from a van I was in last week, I have no chance of finding info in there!

I'd very much appreciate your help as I'm at a loss as to how to do it except I do know I'll be needing to use some variables in my code.


I remember reading about someone having a table with a file path in it, which linked to photos of employees or something, but this seems to me to be a step further still (listing all files in a folder)


Blessings all, and keep up your wonderful work on here. One day, I might be able to contribute back.

Si

 

[namliam]

There are currently no unbound list boxes or OLEObject thingies, but they will be placed in the sunken frames under each heading. The idea is that on clicking an item in the unbound box a user can click an "open" button (not yet added) to open the selected word document or excel file etc.

You can use the "FollowHyperlink" method to open any document.
Look it up in the help to find out more details about it.

I'd very much appreciate your help as I'm at a loss as to how to do it.
I remember reading about someone having a table with a file path in it, which linked to photos of employees or something, but this seems to me to be a step further still (listing all files in a folder)

You can use the "dir" command in a loop to list all the files in a folder.
Look it up in the help to find out more details about it.

Post back any questions, if you have them, here. We can help you further when you need it

Good Luck !

 

[Mr_Si]

Many thanks Mailman, I appreciate it. I shall do just that.

 

[Mr_Si]

Hi all,

Ok, so I've read about DIR so far (in the help file and the odd google search too). Haven't been able to find anything in the Access Help Files about FollowHyperlink

Currently, I'm only just beginning to get around to writing the code (my main job is Acoustics consultancy, which unfortunately has to take the primary role so I'm doing this db on and off when I get the chance).

I'm currently trying to make sure that the variable is getting its data by trying to get the msgbox to display the contents of the variable but I'm not having much success!

here's my code:

(I've made the variable a public one, as I think I need to use it in other subs too.)

[B][I]Option Compare Database

Public strQuotePath As String



Private Sub QuotePath_AfterUpdate()

'Set variable to equal nothing
strQuotePath = Nothing

'Read QuotePath into variable "strQuotePath"
'If there is a directory path in the quotepath field, then copy the path into the variable "strQuotePath"
'and print the contents of the variable in a messagebox
'Asterix used as wildcard?

If Me.QuotePath = "*" Then 
    strQuotePath = Me.QuotePath
    MsgBox (strQuotePath), VbMsgBoxStyle = vbOKOnly
End If


End Sub
[/I][/B]

As you can see, I'm just starting! But I'd appreciate much much help from you wise people.

Thank you in advance.

Below is a picture of the latest layout of the form which is to be controlled by this VBA code.

My next issue is where to put the DIR code, in the same sub as the QuotePath_AfterUpdate() ? or in the actual unbound listbox?

Cheers,

Simon

 

[GaryPanic]

you need to look at Ghudsons d/base -
this is awesome ... it has aboutr 4 functions within it and youcan cherry pick the bits you need - I have not got round to doing this yet ...
checkt he samples and look for the right d/base

 

[DCrake]

Simple Software Solutions

Here is an example of using the find files function you will need to tweek it to suit your needs

CodeMaster:

 

[Mr_Si]

Thanks CDrake!

I have just been looking at your code and got my little check to work with the following code:

Option Compare Database

Public strQuotePath As String



Private Sub QuotePath_AfterUpdate()

'Set variable to equal nothing
strQuotePath = ""

'Read QuotePath into variable "QuotePath
'If there is a directory path in the quotepath field, then copy the path into the variable "strQuotePath"
'and print the contents of the variable in a messagebox


If IsNull(QuotePath) Then

    MsgBox "No data is in the variable", vbOKOnly
    
Else
    strQuotePath = Me.QuotePath
    MsgBox "The following data ' " & strQuotePath & " ' is contained in the variable strQuotePath ", vbOKOnly

End If


End Sub

Now I know the variable is collecting data, I will play deeper.

Thanks. (for now anyway )


Edit 2:

Ok, I changed my code to the following (just a small change really),

Option Compare Database
Option Explicit

Public strQuotePath As String
Public strInvoicePath As String

Private Sub btnInvoiceBrowse_Click()
On Error GoTo Err_btnInvoiceBrowse_Click
   
    strInvoicePath = BrowseDirectory("Find and select where to store the invoice files.")
    Me.InvoicePath = strInvoicePath
    Me.ProjectPath.SetFocus
    InvoicePath_LostFocus
                
Exit_btnInvoiceBrowse_Click:
    Exit Sub
    
Err_btnInvoiceBrowse_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_btnInvoiceBrowse_Click
    

End Sub

Private Sub btnQuoteBrowse_Click()
On Error GoTo Err_btnQuoteBrowse_Click
   
    strQuotePath = BrowseDirectory("Find and select where to store the quote files.")
    Me.QuotePath = strQuotePath
    Me.lstboxQuoteFiles.SetFocus
    QuotePath_LostFocus
                
Exit_btnQuoteBrowse_Click:
    Exit Sub
    
Err_btnQuoteBrowse_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_btnQuoteBrowse_Click
    

End Sub

Private Sub InvoicePath_LostFocus()

'Read InvoicePath into variable "strInvoicePath"
'If there is a directory path in the Invoicepath field, then copy the path into the variable "strInvoicePath"
'and print the contents of the variable in a messagebox

If IsNull(InvoicePath) Then
    strInvoicePath = "" 'set variable to equal nothing
    MsgBox "No data is in the invoice path variable and to prove it, look, here it isn't! '" & strInvoicePath & " ' SO THERE ", vbOKOnly
    
Else
    strInvoicePath = Me.InvoicePath
    MsgBox "The following data ' " & strInvoicePath & " ' is contained in the variable strInvoicePath ", vbOKOnly
    Me.lstboxInvoiceFiles.Enabled = True
End If


End Sub

Private Sub QuotePath_LostFocus()

'Read QuotePath into variable "strQuotePath"
'If there is a directory path in the quotepath field, then copy the path into the variable "strQuotePath"
'and print the contents of the variable in a messagebox

If IsNull(QuotePath) Then
    strQuotePath = "" 'set variable to equal nothing
    MsgBox "No data is in the quote path variable and to prove it, look, here it isn't! '" & strQuotePath & " ' SO THERE ", vbOKOnly
    
Else
    strQuotePath = Me.QuotePath
    MsgBox "The following data ' " & strQuotePath & " ' is contained in the variable strQuotePath ", vbOKOnly

End If


End Sub

 

[Mr_Si]

Argh! I can't understand where to place the code to tell the listbox to list the files that are contained in the textbox "QuotePath" or "invoicepath" etc.

I'm thinking I need to place it within "Private Sub QuotePath_LostFocus()" in the Else statement after the line "strQuotePath = Me.QuotePath"

I shall be removing the MsgBox Statements soon, as they're no longer needed (error checking only)

Help is much appreciated. I feel so dumb!

 

[DCrake]

Simple Software Solutions

Ok

It would be easier to re model the sample than to take you through the changes. Will post the revision later.

David

 

[Mr_Si]

I look forward to it, thank you!

 

[DCrake]

Simple Software Solutions

Here is the remodeled version for you.

Remember to look at the code behind the Enquiry id text box

David

 

[Mr_Si]

Thank you loads! (in advance of looking at the code)
And thanks so much for taking the time to do code for me!


I will download this file at work, as I don't yet have Access on my machine at home.

 

[Mr_Si]

Hi David,

Thank you for the code, I've been looking through it, and have been trying to work out what it all means. Only understand a few bits at the moment.

Anyway, I've copied the code into my form, and I've made the same table as you had, so it references that too. I've edited a couple of bits so that they should integrate with the variables I had, rather than using the constants as you did in the demo.

I'm getting an error to do with the "With Application.FileSearch section as per the 2 pictures below, and as such wondering if I am doing anything wrong...

Form View:

Code View:

Thanks,

Simon

 

[DCrake]

Simple Software Solutions

Ok

You need to check your references to make sure you have Microsoft Scripting for Runtime object referenced.

David

 

[Mr_Si]

I've turned it on, but I'm still getting the error

 

[DCrake]

Simple Software Solutions

Si

Here is a image of all ther references that I am using on my pc for thei demo

make sure you have them all. If it still errors yhre question is what version of Aceess are you using?

 

[Mr_Si]

We're MS Partners and as such, have 10 licenses for 2007. So I'm using that.

I can't add the DAO 3.6 Reference, as it says there's a naming conflict with an object library I have selected.

I have Object Library 12.0, - 11.0 is not available.

 

[DCrake]

Simple Software Solutions

Sorry, This is where my KB finishes. I am not using A2007 so any issues relating to this version are beyond me. Hopefully anyone out there can shed some light on this.

David

 

[Mr_Si]

ah okay.

Well I greatly appreciate your help David, really do.

Si

Edit:

I did a google search and found in the UtterAccess forums, this page

I think it might explain the issue down at the bottom of the page - Application.FileSearch has been removed from the Object Library in version 12.0

 

[Mr_Si]

Just to keep all informed,

I've used Allen Browne's code for doing this and it works a treat. Wish I'd found his website sooner.

LINKY

Now the only niggle with this code is that it shows the whole directory path before the file name, rather than just the file name. This is only annoying because the listbox won't allow me to give it a lefty-righty scroll. It only scrolls up and down so if the file path is too long, one can't see the file name.

Also, is there anyway to display the file extension? this code doesn't allow it to be shown, and I can't see anything to delete the last 4 characters from the end of the file name anywhere.

Here're a couple of examples:

1. The file paths are short enough to display in a listbox

2. The file paths are too long to display all in a list box and I cannot scroll

Many thanks in advance

Simon