On Run Check One Cell in All Records and Change Another Cells Data

[ScriptKiddy]

I hope this makes sense.

I am writing come code in a DB to check a link in a DataSheet to verify the file is present. Then I want to have the code change another cell to say whether it is True or False. From there I have the Conditional Formatting set to change the color of a different cell.

I know lots of things happening. I am able to get it to work if I set it to OnCurrent but, when I set it to OnOpen nothing happens.

Here is what I have so far.

Option Compare Database
Private Sub ScannedFileName_DblClick(Cancel As Integer)
''Hyperlink Open
On Error GoTo ScannedFileName_DblClick_Err
    Dim ctl As Object
  Set ctl = Me!OpenScannedPDF
  With ctl
    .Visible = False
    .HyperlinkAddress = "\\NetworkDrive\Digital Sender\" + Me.ScannedFileName + ".pdf"
    .Hyperlink.Follow
  End With
ScannedFileName_DblClick_Exit:
    Exit Sub
ScannedFileName_DblClick_Err:
    Resume ScannedFileName_DblClick_Exit
End Sub

Private Sub Form_AfterInsert()
Me.Refresh
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Refresh
End Sub

Private Sub Form_Open(Cancel As Integer)
'Verifies that a Scanned File is Present in the Digital Sender Directory
With Application.FileSearch
    If Me.ScannedFileName = True Then
        'Conditional Formating Makes the ScannedFileName Cell Turn Green
        Me!colorTF = True
    Else
        'Conditional Formating Makes the ScannedFileName Cell Turn Red
        Me!colorTF = False
        'MsgBox for Test Purposes Only
        MsgBox "Nothing has been scanned for this record."
    End If
End With
End Sub

Thanks in advance for your time and consideration in this matter.

 

[PHV]

Use the Load event instead of the Open.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ScriptKiddy]

Ok so after some work I have found that what I had submitted just won't work. I did some more research and found System.IO.file.Exists() placed it in the Load event and still wont go past the first record.

Here is what I have come up with...Any help is very much appreciated.

Private Sub Form_Load()

Dim f1ledd As String

filedd = "\\Nework Location\" + Me.FileNumber + ".pdf"

If System.IO.file.Exists(filedd) = True Then
'the file does exist
Me!LOOKYLOOKY = False
Else
'the file does not exist
Me!LOOKYLOOKY = True

End If

End Sub

 

[HarleyQuinn]

System.IO.file.Exists is .Net syntax, you could use the Dir() command instead.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PHV]

wont go past the first record
You may loop the form's Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886