Please help on this problem (1 Viewer)

christ2000 · Jul 18, 2018

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 · Jul 18, 2018

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 · Jul 18, 2018

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 · Jul 18, 2018

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 · Jul 18, 2018

plog said:
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 · Jul 18, 2018

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 · Jul 18, 2018

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 · Jul 18, 2018

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 · Jul 18, 2018

hello, can I send you through email?

Cronk · Jul 19, 2018

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

Code:

=FileExists([txtFileName], [txtFilePath])

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

The function code would be

Code:

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

Code:

 Forms![ContractorsDBForm]![Requirements]!DocumentsName

christ2000 · Jul 19, 2018

Cronk said:
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

Code:

=FileExists([txtFileName], [txtFilePath])

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

The function code would be

Code:

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

Code:

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 · Jul 19, 2018

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

Mark_ · Jul 19, 2018

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.

Please help on this problem (1 Viewer)

christ2000

New member

plog

Banishment Pending

christ2000

New member

plog

Banishment Pending

christ2000

New member

plog

Banishment Pending

christ2000

New member

plog

Banishment Pending

christ2000

New member

Cronk

Registered User.

christ2000

New member

christ2000

New member

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread