Solved: Open external file through access button

[Jason Miller]

I am a newbie trying to develop a Button in an access application that will open a file based on a path in the fields of an access table. The path format would need to look something like this.
S:/<field1>&<Field2>/<field3>& "-" & <Field4> & "-" & <field5>
Thanks for any help!!

[Norie]

What type of file are you trying to open?

[Jason Miller]

It is actually a Autocad *.dwf file.

[MOS MASTER]

Hi,

The easy method is to use the FollowHyperlink method!

So:[VBA]
Private Sub OpenMyFile_Click()
Dim sPath As String

sPath = "S:/" & Me.field1 & Me.field2 & "/" & Me.field3 & "-" & _
Me.field4 & "-" & Me.field5

'Make sure sPath is a qualified path
'sPath = "C:\BuiltInDocumentProperties.doc" 'for illustration

If Dir(sPath) <> "" Then
Application.FollowHyperlink Address:=sPath
Else
MsgBox "File: " & sPath & " does not exists", vbExclamation, "File check"
End If

End Sub[/VBA]
You have to make sure that the fields you are concenating are making up a qualified path! (Put in Dir function for you to check)

Play with it!

[Jason Miller]

I have got the path to work, but, it keeps pulling the values for the first line in the table. My unique identifier is in a field called accountno, but I am not sure exactly where to call that in my code. The code is below.

Private Sub Get_Map_Click()
Dim lpath As String
Dim spath As String
Dim quad As String

Dim realware As DataBase
Dim sectionrs As DAO.Recordset
Dim townshiprs As DAO.Recordset
Dim rangers As DAO.Recordset
Dim qtrrs As DAO.Recordset
Dim qtrqtrrs As DAO.Recordset
Dim sectionsql As String
Dim lsection As String
Dim townshipsql As String
Dim ltownship As String
Dim rangesql As String
Dim lrange As String
Dim qtrsql As String
Dim lqtr As String
Dim qtrqtrsql As String
Dim lqtrqtr As String
Set realware = CurrentDb()


'This will find and define the section
sectionsql = "select section from tbllegal"
Set sectionrs = realware.OpenRecordset(sectionsql)

If sectionrs.EOF = False Then
lsection = sectionrs("section")
Else
lsection = "not found"
End If
'MsgBox lsection
sectionrs.Close
Set sectionrs = Nothing
getsection = lsection



'This will find and define the township
townshipsql = "select township from tbllegal"
Set townshiprs = realware.OpenRecordset(townshipsql)

If townshiprs.EOF = False Then
ltownship = townshiprs("township")
Else
ltownship = "not found"
End If
'MsgBox ltownship
townshiprs.Close


'This will find and define the range
rangesql = "select range from tbllegal"
Set rangers = realware.OpenRecordset(rangesql)

If rangers.EOF = False Then
lrange = rangers("range")
Else
lrange = "not found"
End If
'MsgBox lrange
rangers.Close


'This will find and define the qtr
qtrsql = "select qtr from tbllegal"
Set qtrrs = realware.OpenRecordset(qtrsql)

If qtrrs.EOF = False Then
lqtr = qtrrs("qtr")
Else
lqtr = "not found"
End If
'MsgBox lqtr
qtrrs.Close


'This will find and define the qtrqtr
qtrqtrsql = "select qtrqtr from tbllegal"
Set qtrqtrrs = realware.OpenRecordset(qtrqtrsql)

If qtrqtrrs.EOF = False Then
lqtrqtr = qtrqtrrs("qtrqtr")
Else
lqtrqtr = "not found"
End If
'MsgBox lqtrqtr
qtrqtrrs.Close
MsgBox lsection & "-" & ltownship & "-" & lrange & "-" & lqtr & "-" & lqtrqtr

If lqtr = "NE" Then
quad = "Q1"
End If
If lqtr = "NW" Then
quad = "Q2"
End If
If lqtr = "SW" Then
quad = "Q3"
End If
If lqtr = "SE" Then
quad = "Q4"
End If

spath = "N:/" & ltownship & lrange & "/" & lsection & "-" & ltownship & "-" & lrange & "-" & quad & "-model.dwf"
lpath = "N:/" & ltownship & lrange & "/" & lsection & "-" & ltownship & "-" & lrange & "-" & quad & "-" & lqtrqtr & "-model.dwf"

If Dir(spath) <> "" Then
Application.FollowHyperlink Address:=spath
Else
If Dir(lpath) <> "" Then
Application.FollowHyperlink Address:=lpath
Else
MsgBox "File: " & spath & " does not exists", vbExclamation, "File check"
End If

End If


End Sub

[MOS MASTER]

Hi,

You're Welcome!

Do you mean you would like to see all the records in the record set one by one? (Because your saying you always get the first record)

if you want to you will have to loop the recordset with the MoveNext method of the recordset and let the loop run from 1 to sectionrs.RecordCount (you can do a moveFirst to make sure you start at the beginning)

But I first have to understand what you're trying to do...bit vage to me? (Slow understanding dutchmen over here)

[Jason Miller]

Thanks so much for your help!!

I need to pull all my values from a particular line each time identified by the field (ACCOUNTNO). This is a unique number and is different for each line in the table.

For instance if I am working with account # 1377, I want to pull the data for only that account into my path (spath) or (lpath). The problem is, without identifying the account that I am working with, it only pulls data from account # 1. I assume that the syntax would look something like fieldname(ACCOUNTNO). I am still newbie, so this may be very simple.

Thanks again!!!

[MOS MASTER]

Hai Jason,

This ID number is it also on the Form? (As a field?)

If it is we can use it to alter you're SQL-Statement to select only that Fieldrow in the table?

Please confirm?

I'm writing posts to another forum right now so I'll be back with you shortly!

[Jason Miller]

Exactly, it is in the same form as all the other fields.

Thanks,
Jason

[MOS MASTER]

Exactly, it is in the same form as all the other fields.

Thanks,
Jason

Hi,

This I don't understand?

All you're required fields are on the AccessForm....Can you explain to me why you still are using recordset's in you're code to look for that value in the Table?

If the values are correct in the form than you can do simple string concenation (of those field values) to build your (path) string.

Am I missing something here?

[Jason Miller]

Thanks again!!!

I agree that simple concenation should be easier, I am only having trouble getting the program to recognize the field names, so I tried something a little different(recordset). Perhaps I am wrong in my syntax, or am not defining my form correctly. Either way, if you can help, I really, really appreciate it!!!!

Jason

[MOS MASTER]

Hi Jason,

Sure will stick by you till the end!

I can do a lot off writing write now but I'm sure you'll benefit more from a simple example.

See the attached file for an easy method of concenating the textbox values in the Access form.

Enjoy!

[Jason Miller]

Thanks a lot!!

I got it working!!!

[MOS MASTER]

Thanks a lot!!

I got it working!!!

Hai Jason,
That's great! You're Welcome!