cmd button - browse to/add filepath name to database

[Xunil]

Does anyone know how to set up a form so that I can use a command button to browse to a specific file (a la explorer) then append that filename to a customers records ?

I'm playing with a concept of saving Excel generated invoices to a specific folder C:\DB\Invoices for example. This is for single user standalone PC and will never be used over a network.

It would be nice if I could attach the file-path name to the customer records. This would keep the database very small (or such is my theory) but I would also need yet another command button to call up the file that belongs to whichever customer is being browsed in the database.

So the database will have the ability, via a form, to browse to and append a file-path name to a customers records and the last command button would look at whichever file-path name is being currently displayed on the customer details form and open that file when clicked.

Any thoughts, or am I completely nuts and severely over-complicating things ?

 

[chrissie1]

two methods depending on your access version
in 2002 'perhaps even 2000) application.filedailog
how simple can it be

for access 97 try the code at this site seems difficult but you just copy the code in a general module and then caal it from you commandbutton

http://www.mvps.org/access/api/api0001.htm

"What a wonderfull world" - Louis armstrong

 

[Xunil]

At the risk of sounding ungrateful (which I’m not since your suggestion at least sent me in the beginnings of the right direction) I’m still struggling to get to grips with this.

Using Access 2000 I can now get the Open/Save as and dialog box to appear on my form, but the bits I’m desperately fighting with are being able to add the filepath of a specific file.

Let’s say "inv1_19.1.03.xls" for example (where inv1_ denotes that it is invoice number 1 and the remaining numbers will be the date it was generated) is the file in question. I’d like to be able to browse to it and then add its filepath to a customers details form with a command button click, and then the subsequent ability to launch that file if it is showing in the customer details form.

Imagine the usual fields on a customer form:

Name
Surname
Address 1
Address 2
Town/City
County
PostCode
TelNo
FaxNo
Email
Notes

Now imagine another field called Invoice on the same form.

This Access application launches a complex Excel workbook where an invoice is generated from listbox transfers via a userform in Excel. What I want to be able to do is save the resulting Excel Invoice to a specific directory. Then, from the Customer form on the Access Database (where the Excel workbook is launched from to begin with) I’d like to be able to click and browse to the invoice just created and add its filepath to an Invoice field on the Customer form. This field value will be stored in an Orders table but the filed will appear on the Customer form.

Finally I would like a command button on the Customer form that would launch whichever filepath name is displayed in the Invoice field on the form.

Say Customer 1 has inv1_10.1.03.xls displayed in his/her Invoice field. Browsing to the next Customer 2, may have inv2_10.1.03.xls or perhaps a different date, depending on when the order was made. I’d like a command button that will launch whichever filename appears in the Invoice form textbox.

If this is simply too complicated or impractical, I’ll settle for the ability to browse to and open the file manually, but I can’t for the life of me figure out how to use a command button to actually append the filepath name of an invoice to the Customer form Invoice field to begin with.

Like I said, maybe I’ve lost the plot completely here.

Help !

 

[chrissie1]

the thinking is good the execution will be a little harder to explain but there are several ways to go about it none are easy to explain to say the least.
perhaps someone else with a better grasp of english could do it but if you give me your email adress i will send you an example perhaps even two if you can wait for a day or two.

"What a wonderfull world" - Louis armstrong

 

[susanhawk]

Here is code written for a Visual Basic user interface, but shouldn't be too hard to adapt for Access. I don't remember quite how you use the common dialog control in Access, but I think it's different. Hope this will help.

Private Sub GetFile()
On Error GoTo Oops
Dim stFilename As String, stPart As String, stPartLeft As String, stName As String, stLocation As String, stSQL As String
Dim intMark As Integer, intLength As Integer, intCount As Integer, intEnd As Integer
Dim varParsed As Variant
comDlg.DefaultExt = "*.xls"
comDlg.FileName = ""
comDlg.ShowOpen
If comDlg.CancelError = True Then
Screen.MousePointer = vbNormal
Exit Sub
End If
Screen.MousePointer = vbHourglass
stFilename = comDlg.FileName
If stFilename = "" Then
Screen.MousePointer = vbNormal
Exit Sub
End If
varParsed = Parse(stFilename)
StoreFile (varParsed)
Exit Sub
Oops:
Screen.MousePointer = vbNormal
MsgBox "Error code: " & cn.Errors(0).Number & vbCrLf & " Description: " & cn.Errors(0).Description & vbCrLf & "Source: " & cn.Errors(0).Source, vbOKOnly + vbCritical, "Error"
End Sub

Private Function Parse(stFilename As String)
'parse file name to remove directory
Dim stLocation As String, stName As String, stPartLeft As String, stPart As String
Dim intLength As Integer, intCount As Integer, intMark As Integer
intLength = Len(stFilename)
intCount = 1
stPartLeft = Mid(stFilename, intCount, intLength)
Do Until intCount = intLength - 3 'intLength - 3 because of xls extension at the end
stPart = Mid(stPartLeft, 1, 1)
If stPart = "\" Then
intMark = InStr(intCount, stFilename, "\&quot 'mark place of \ ... last one will be used
End If
intCount = intCount + 1
stPartLeft = Mid(stFilename, intCount, intLength)
Loop
Parse = Mid(stFilename, intMark + 1, intLength)
End Function

Private Sub StoreFile(stFilename As String)
Dim stPutFile As String
stPutFile = "C:\DB\Invoices\" & stFilename
Open stPutFile For Output As #1
DoCmd.Quit acSaveYes
Screen.MousePointer = vbNormal
MsgBox "Your file " & stFilename & " has been saved", vbOKOnly, "File saved"
End Sub

 

[chrissie1]

this is the actual code i used


Private Sub btn_browse_Click()
Dim dlgopen As FileDialog

Set dlgopen = Application.FileDialog(msoFileDialogFilePicker)

dlgopen.AllowMultiSelect = False
dlgopen.Filters.Add "Excell file", "*.xls", 1
dlgopen.Show

invoice = dlgopen.SelectedItems(1)
End Sub

Private Sub btn_open_Click()


Set xlobj = CreateObject("excel.application&quot
xlobj.Application.Visible = True

With xlobj
.Workbooks.Open FileName:=invoice
End With

End Sub
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[Xunil]

Wow.

Thank you both for your input. It's certainly very much appreciated.

I thought the logic behind my idea was sound but being relatively new to Access I am never certain until I see concept in practice.

I figured it would make an interesting way of combining Access and Excel while expanding my horizons by learning along the way.

I'll be busy most of this evening and tomorrow, but I hope to have the time tomorrow evening to look at both of the examples you so kindly provided.

Thanks once more and I'm looking forward to playing with the code samples you both suggested.

Best regards

 

[Xunil]

Well, being the inexperienced (at programming and code) chap that I am I'm afraid I can't get either of the examples offered to work.

I have absolutely no doubt at all that this is entirely my fault and not the fault of the code suggested above.

This is just to say thanks for offering your input.

It looks like I'll just have to admit defeat and try and think of another way around this one.

Best regards from a frustrated Access idiot.

 

[Xunil]

Sorry to have left this so long without any feedback. I've been out and about on business and just got back to the UK last night.

I finally came up with a simple solution for grabbing the desired filepath name and dumping it into a textbox - possibly the result of too much Southern Comfort during a snowstorm in Canada...

Code as follows:

*******
Private Sub cmdOpenFile_Click()
On Error GoTo err_cmdOpenFile_Click

Dim strFile As String

strFile = GetOpenFile_CLT("C:\BD\Invoices", "Select a File To Open&quot

Me!txtInvoice = strFile

exit_cmdOpenFile_Click:
Exit Sub

err_cmdOpenFile_Click:
MsgBox Err.Description
Resume exit_cmdOpenFile_Click

End Sub
*******

Obviously that browses to and transfers the selected filename to the txtInvoice textbox. A little inelegant, but it works.

I used a simple ShellExecute command to open the file stored in txtInvoice. Again, this may not be the best possible solution, but it works and saves having to mess about with registering controls on client machines and so on.

Thanks everyone for your help on it.

Best regards

 

[SpandexBobcat]

Hi Xunil,

I am trying to do the same as you but on using your code got a 'Sub or function not defined' error on GetOpenFile_CLT..... any clues for a novice?

 

[SpandexBobcat]

Just to let you know, I cracked it!

The function was called from the module supplied in the link chrissie1 supplied.

"A picture is not worth a 1000 words on the Internet. The information is in the text."