Please help on this problem

[christ2000]

hello guys, I was using the following code to check if some files exist in my windows folder or not. the problem is that the files must be check in a continuous subform and only the first records is update on the subform, so now I must click in each record to update the status,

is there any way to fix this and when form load all records are checked and and show exist or not?

this is the code I am using:

Private Sub MissDoc_Click()
docexist = "Doc. Exist"
missinvalue = "Doc. Missing"
cname = Forms![ContractorsDBForm]![Text442]
filename = Me.DocumentsName & ".pdf"
filepath = DLookup("[ContractorPath]", "querysetup") & cname & DLookup("[expr1]", "querysetup") & cname & " " & filename

If Dir(filepath) = "" Then
Me.MissDoc = missinvalue
Else
Me.MissDoc = docexist

End If

End Sub




thanks in advance, I could find any solution in any place for this:banghead:

 

[plog]

I wouldn't make this an event (_OnClick), but a stand alone function that you pass necessary information to and it returns a value that lets you know if it exists or not.

Then, you add an unbound text box to your form and have its source be a call to that function with the appropraite data.

ControlSource =get_MissingDocument(DocumentName, ContractorName)


Then the code would take that info and determine if that particular data is missing or not.

 

[christ2000]

Hello, I try this too but unbound field return same value for all records on subform

this is my module function code:

Public Function FileExists(ByVal pvFile) As Boolean
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FileExists = FSO.FileExists(pvFile)
Set FSO = Nothing
End Function
--------------------------------

and this is the code I put in my form to unbound field:


Private Sub Form_Current()
docexist = "Doc. Exist"
missinvalue = "Doc. Missing"
cname = Forms![ContractorsDBForm]![Text442]
filename = Forms![ContractorsDBForm]![Requirements]![DocumentsName]
filepath = DLookup("[ContractorPath]", "querysetup") & cname & DLookup("[expr1]", "querysetup") & cname & " " & filename

If FileExists(filepath) Then
Me.Text675 = missinvalue
Else
Me.Text675 = docexist
End If


End Sub



thanksss any idea?

 

[plog]

and this is the code I put in my unbound field:

Huh? The unbound field should have no code. You will not be using an event to get this data.

The unbound field will use its Control Source property to reference the function I talked about. The function will determine if your directory exists or not.

 

[christ2000]

Huh? The unbound field should have no code. You will not be using an event to get this data.

The unbound field will use its Control Source property to reference the function I talked about. The function will determine if your directory exists or not.

yes my typo, I put the code in form.load

 

[plog]

Again, this requires no event code. You make a function, you pass it the data it needs it returns a value to display on the screen.

You should only have one function--FileExists(). Then the control source of the unbound box becomes =FileExists([DataFunctionNeeds])

 

[christ2000]

Sorry for my ignorance on this

ok my function look as bellow?

Public Function FileExists(filepath As String) As Boolean

docexist = "Doc. Exist"
missinvalue = "Doc. Missing"
cname = Forms![ContractorsDBForm]![Text442]
filename = Forms![ContractorsDBForm]![Requirements]!DocumentsName & ".pdf"
filepath = DLookup("[ContractorPath]", "querysetup") & cname & DLookup("[expr1]", "querysetup") & cname & " " & filename



If Dir(filepath) = "" Then
Forms![ContractorsDBForm]![Requirements]![MissDoc] = missinvalue
Else
Forms![ContractorsDBForm]![Requirements]![MissDoc] = docexist

End If
End Function


is this correct? if yes how must be look my control source code?

thanks and sorry for bother u

 

[plog]

Nope. Your function will not reference anything on a form. It will take data, do its thing and then return a result.

Do you have a sample of your database? Can you zip and post it?

 

[christ2000]

hello, can I send you through email?

 

[Cronk]

If you want the text box Text675 on your continuous form to show "Exists" or "Missing", then the control source for that control should be

=FileExists([txtFileName], [txtFilePath])

where txtFileName is the control with the name of file, and txtFilePath is the path.

The function code would be

Function FileExists(FileName As String, FilePath As String) As String
   If Dir(FilePath & "\" & FileName) <> "" Then
      FileExists = "Exists"
   Else
      FileExists = "Missing"
   End If

End Function

BTW, this looks suspect

 Forms![ContractorsDBForm]![Requirements]!DocumentsName

 

[christ2000]

If you want the text box Text675 on your continuous form to show "Exists" or "Missing", then the control source for that control should be

=FileExists([txtFileName], [txtFilePath])

where txtFileName is the control with the name of file, and txtFilePath is the path.

The function code would be

Function FileExists(FileName As String, FilePath As String) As String
   If Dir(FilePath & "\" & FileName) <> "" Then
      FileExists = "Exists"
   Else
      FileExists = "Missing"
   End If

End Function

BTW, this looks suspect

 Forms![ContractorsDBForm]![Requirements]!DocumentsName

Hello, first thanks for help me, I really appreciate that, I do as you recommend me, and for test I put the file path less complicate, but the problem is that the all unbound fields show same value, I I move to the first all show exist, but is I move to the second one show all show do not exist,



any idea?

 

[christ2000]

Thankssss, I did it. very very thanks, your help was excellent

 

[Mark_]

Just to check, you are looking for some random value out of querysetup OR querysetup has only one record? If you use DLookup without passing it a criteria it simply returns A record. I'd also check if its returning NULL.

Consider me a bit picky but I'd also have Option Explicit at the top. This does mean you will have to declare your string variables, but it helps avoid programming issues caused by misspelled variables.