Help needed trouble shooting my code.

[GODZILLA]

Hello,

Im having a problem with this.

It keeps failing when the varible looks at an empty cell in the record source.

What im trying to achieve is i have a method of attaching a file to a data item then i want that path to rename ready for sending.

But i cant seem to make it work.

Can anyone help?

Thanks

The paths are wrong on purpose. I have removed the extentions

Option Compare Database
Option Explicit
Sub Export_attachments()
Dim loDb As Database
Dim loRst As Recordset
Dim fso As Object
Dim FSOobj As Object
Set FSOobj = CreateObject("Scripting.FilesystemObject")
Set loDb = CurrentDb
Set loRst = loDb.OpenRecordset("REF_attachments")
 
With loRst
Dim FromPath1 As String
Dim FromPath2 As String
Dim FromPath3 As String
Dim ToPath1 As String
Dim ToPath2 As String
Dim ToPath3 As String
Dim att1 As String
Dim att2 As String
Dim att3 As String
Dim FBR As String
Dim NN1 As String
Dim NN2 As String
Dim NN3 As String
Do Until .EOF
 
FBR = .Fields("Account Number").value
 
If FSOobj.FolderExists("\" & FBR) = False Then
FSOobj.CreateFolder "\" & FBR
Else
GoTo Finished
End If
Finished:
If .Fields("Attachment_path1").value = Null Then
GoTo two
Else
    att1 = .Fields("Attachment_path1").value
    NN1 = .Fields("NN1").value
    FromPath1 = att1
    ToPath1 = "\" & FBR & "\" & NN1 & Right(att1, 4)
    FileCopy FromPath1, ToPath1
End If
two:
If .Fields("Attachment_path2").value = Null Then
GoTo Three
Else
    att2 = .Fields("Attachment_path2").value
    NN2 = .Fields("NN2").value
    FromPath2 = att2
    ToPath2 = "\" & FBR & "\" & NN2 & Right(att2, 4)
    If FromPath2 = "" Then GoTo Three
    FileCopy FromPath2, ToPath2
End If
Three:
If .Fields("Attachment_path2").value = Null Then
GoTo Final
Else
    att3 = .Fields("Attachment_path3").value
    NN3 = .Fields("NN3").value
    FromPath3 = att3
    ToPath3 = "\" & FBR & "\" & NN3 & Right(att3, 4)
    FileCopy FromPath3, ToPath3
End If
Final:
 
.MoveNext
Loop
End With
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
 
End Sub

 

[vbaInet]

It keeps failing when the varible looks at an empty cell in the record source.

You should be using IsNull() or Nz(), not = Null

 

[GODZILLA]

You should be using IsNull() or Nz(), not = Null

Argument not valid apparently

 

[vbaInet]

Argument not valid where? And what have you changed?

 

[GODZILLA]

Argument not valid where? And what have you changed?

You were right i was wrong.

Thanks for the help!

Ill use isnull() from now on.

 

[vbaInet]

And if you want to check for null AND "" (i.e. zero length string) then you can choose one of these instead:

If Nz(Me.txtbox.value, "")="" then

If Len(Me.txtbox.value & "")=0 then

Glad it worked for you