Ghudson fScanDirSelectFileFromCombo example (1 Viewer)

[DaaAgent]

I have been trying to change ghudson great example to fit in my database

My plan was to:-
Change Combox to ListBox...Done
OpenFile from Combo (AfterUpdate) Changed to OnClick...Done
Change C: to Display Files in Current records folder in the server

Sooo my missing part of the puzzle is get the correct folders files to appear with the correct clients ID

The folder location and folder name are in a textbox in the form already txtMakeFldPath which is used in the first place to make the folder and name it with the clients record id

I did find in the forms code a line Const cPath = “C:\” which I changed manual to a test a folder in the server and worked perfectly but when I change the Const cPath = “C:\” to look at the form I get an error message tell me cant find the directory
I tried the combinations below
Const cPath = Me.txtMakeFldPath
Const cPath = “MakeFldPath”
Const cPath = “[txtMakeFldPath]”
Const cPath = [MakeFldPath]

I cant seem to tell Access to look at the contents of the txtMakeFldPath I can only get it to look for the folder txtMakeFldPath I think!!!

Help Help

Thanking you in advance for your help in this matter

DaaAgent

 

[boblarson]

You can't set a constant to be something that is variable. You CAN set a variable (ie: strPath As String) to something variable, but you have to define the constant as something that is constant, hence the term Constant.

 

[GaryPanic]

once you've figured this out post it up - i been tinkering with this for ages -and i haven't figured it out yet (Must admit i keep putting it on the back burner)

 

[DaaAgent]

Thanks for the Const advice. i also tried replacing the Const line with some string combinations below but still no luck, I get the same message “Invalid outside procedure”

Dim cPath As String
cPath = "C:\" & Forms![frmListBox]![CLIENTID] & "\"

I have uploaded a cut down .mdb example based on the folders being on the C drive also below is Ghudson original form code


Option Compare Database
Option Explicit

'Combo box named [cbSelectFile]
'Row Source Type set to [Value List]

Const cPath = "C:\" 'cPath must end with a back slash i.e. C:\Temp\

Private Sub cbSelectFile_AfterUpdate()
On Error GoTo Err_cbSelectFile_AfterUpdate

Me.tbHidden.SetFocus

Application.FollowHyperlink cPath & "\" & cbSelectFile

Exit_cbSelectFile_AfterUpdate:
Exit Sub

Err_cbSelectFile_AfterUpdate:
If Err.Number = 432 Then 'File name or class not found during Automation operation
MsgBox "The '" & cbSelectFile & "' file can not be opened.", vbCritical, "Invalid File Type"
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_cbSelectFile_AfterUpdate
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

DoCmd.Restore

If Dir(cPath, vbDirectory) = "" Then
MsgBox "The directory " & cPath & " does not exist or you do not have access to the " & cPath & " directory.", vbCritical, "Directory Access Error"
Else
'do nothing
End If

Me.lcbSelectFile.Caption = "Select a file to open from " & cPath

Dim lngCount As Long
lngCount = Me.cbSelectFile.ListCount

Call Update_cbSelectFile

Exit_Form_Open:
Exit Sub

Err_Form_Open:
If Err.Number = 76 Then 'path not found
MsgBox "You do not have access to the " & cPath & " directory.", vbCritical, "Directory Access Error"
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Form_Open
End If

End Sub

Public Function Update_cbSelectFile()
On Error GoTo Err_Update_cbSelectFile

Dim sPath As String
Dim sFileList As String
Dim sFileName As String

sFileList = ""
sFileName = Dir(cPath)
Do While sFileName <> ""
sFileList = sFileList & sFileName & ";"
sFileName = Dir
Loop

Me.cbSelectFile.RowSource = sFileList
Me.cbSelectFile.Requery

Exit_Update_cbSelectFile:
Exit Function

Err_Update_cbSelectFile:
If Err.Number = 2176 Then 'The setting for this property is too long
MsgBox "Combo box can not contain more than 255 records. No records will be displayed.", vbCritical, "Record Source Error"
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Update_cbSelectFile
End If

End Function

 

[boblarson]

First of all, put your Constants in a STANDARD MODULE, not a form module.

Second, you should explicitly define what the constant is:

Const cPath = "C:\"

SHOULD BE

Const cPath As String = "C:\"