Invalid procedure call or argument brows and attach file

[Gismo]

Hi all,

I need to brows for a file on the network then attach the PDF into a Table

All works well, the correct folder is opened, I can select the PDF file, until it needs to attached die file name into my Table, then I get the error

Not sure If I am missing something here, please could you advise

Public Function BrowseFile() As String

Dim strFile As String
With Application.FileDialog(1)
.Title = "Select File"
'.InitialFileName = "\\Sjo2054\shared\CAMO\DAW Sheet\Data Files\Attachments\"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No file selected", vbInformation
Exit Function
End If
End With

BrowseFile = strFile

End Function

Private Sub cmdBrowse_Click()

On Error GoTo Err_Handler

Dim Attachment As String
Dim strFolder As String
Dim strFile As String
Dim intPos As Integer

Attachment = BrowseFile

If Attachment <> "" Then
' get folder and file names
'intPos = InStrRev(Attachment, "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments Temp File\")
strFolder = left(Attachment, intPos - 1)
strFile = Mid(Attachment, intPos + 1)

' populate text boxes on form
Me.Attachment = Attachment

End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub

 

[Minty]

I'd add a debug.print Attachment somewhere, as I have doubt that the file name is being passed around correctly.
However, where is the error occurring - what line of the code, and where is your function stored, what's the name of the module?

 

[Gismo]

I'd add a debug.print Attachment somewhere, as I have doubt that the file name is being passed around correctly.
However, where is the error occurring - what line of the code, and where is your function stored, what's the name of the module?

Hi,

Debug Print strFile has the below result

I managed to get the error resolved but now the field in my Attachment Temp File is not updating the Attachment field which is the current Table the form is based on

Print Debug Attachment results in Null

 

[Minty]

Okay - you can't just add the file name as the attachment you have to add it as a loadfromfile object. See here for two examples

Add attachment via VBA in MS Access

One of my form is as follows It is a form made in MS Access which allows my users to add observations. An observation may include attachments as shown. I am able to click on attachment control and...

stackoverflow.com

Why not just save the path? Your database will grow substantially(bloat) with actual files stored in it?

 

[Gismo]

Okay - you can't just add the file name as the attachment you have to add it as a loadfromfile object. See here for two examples

Add attachment via VBA in MS Access

One of my form is as follows It is a form made in MS Access which allows my users to add observations. An observation may include attachments as shown. I am able to click on attachment control and...

stackoverflow.com

Why not just save the path? Your database will grow substantially(bloat) with actual files stored in it?

I use the same 2 code on another DB and seems to work well

I adopted it for this DB but now seem to be running into some issued

In my other DB, below is a few samples of the attachment field

In another form, i then display the PDF file

 

[Minty]

Displaying it and saving it in the DB are two different things.
That isn't an attachment field - It looks like a simple file path, which makes much more sense.

Edit - Not sure you can use a comma in a file name. I suspect that's might be your problem

 

[Gismo]

Displaying it and saving it in the DB are two different things.
That isn't an attachment field - It looks like a simple file path, which makes much more sense.

Edit - Not sure you can use a comma in a file name. I suspect that's might be your problem

Sorry, yes, I am saving the file path

Even the below with no comma does not update my attachment field in the current form and current table

 

[Minty]

Rename the control on the form to txtAttachment.
Then amend your code to Me.txtAttachment

I'm sure Attachment is a reserved word and is possibly causing you an issue.

 

[Gismo]

Rename the control on the form to txtAttachment.
Then amend your code to Me.txtAttachment

I'm sure Attachment is a reserved word and is possibly causing you an issue.

I made the change, the path still seems to be a null value in debug print txtAttachment

As motioned before, I use this code for another DB and works well, not sure where I could have gone wrong in adapting to this DB

Public Function BrowseFile() As String

Dim strFile As String
With Application.FileDialog(1)
.Title = "Select File"
'.InitialFileName = "\\Sjo2054\shared\CAMO\DAW Sheet\Data Files\Attachments\"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No file selected", vbInformation
Exit Function
End If
End With

Debug.Print txtAttachment
'Debug.Print strFile

End Function

Private Sub cmdBrowse_Click()

On Error GoTo Err_Handler

Dim txtAttachment As String
Dim strFolder As String
Dim strFile As String
Dim intPos As Integer

txtAttachment = BrowseFile

If txtAttachment <> "" Then
' get folder and file names
intPos = InStrRev(txtAttachment, "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\")
strFolder = left(txtAttachment, intPos - 1)
strFile = Mid(txtAttachment, intPos + 1)

' populate text boxes on form
Me.txtAttachment = txtAttachment

Debug.Print txtAttachment
Debug.Print strFile
Debug.Print intPos
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub

Debug.Print txtAttachment
Debug.Print strFile

Below Pic

strFile is correct which must me populated to the attachment filed

 

[Minty]

I wouldn't have called both the variable and the control the same thing, but that should avoid any issues.

Try this please

Private Sub cmdBrowse_Click()


   ' On Error GoTo Err_Handler

    Dim strAttachment As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer

    strAttachment = BrowseFile ' = "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\abc123\MyTestFIle.pdf"
    Debug.Print strAttachment

    If strAttachment <> "" Then
        ' get folder and file names
        intPos = InStrRev(strAttachment, "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\")
        strFolder = Left(strAttachment, intPos - 1)
        strFile = Mid(strAttachment, intPos + 1)
        
        Debug.Print strFile
        Debug.Print intPos
        ' populate text boxes on form
        Me.txtTest = strAttachment

    End If

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub

 

[Gismo]

I wouldn't have called both the variable and the control the same thing, but that should avoid any issues.

Try this please

Private Sub cmdBrowse_Click()


   ' On Error GoTo Err_Handler

    Dim strAttachment As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer

    strAttachment = BrowseFile ' = "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\abc123\MyTestFIle.pdf"
    Debug.Print strAttachment

    If strAttachment <> "" Then
        ' get folder and file names
        intPos = InStrRev(strAttachment, "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\")
        strFolder = Left(strAttachment, intPos - 1)
        strFile = Mid(strAttachment, intPos + 1)
      
        Debug.Print strFile
        Debug.Print intPos
        ' populate text boxes on form
        Me.txtTest = strAttachment

    End If

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub

Hi

I modified my code to the one you supplied
No errors but still my txtAttahment wont populate

Debug.Print strAttachment does show the correct value though

I did keep me.txtAttachment = strAttachment the same as txtAttachment is the control name on my form and the table

Private Sub cmdBrowse_Click()

On Error GoTo Err_Handler

Dim strAttachment As String
Dim strFolder As String
Dim strFile As String
Dim intPos As Integer

strAttachment = BrowseFile ' = "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\abc123\MyTestFIle.pdf"

Debug.Print strAttachment

If strAttachment <> "" Then
' get folder and file names
intPos = InStrRev(strAttachment, "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\")
strFolder = left(strAttachment, intPos - 1)
strFile = Mid(strAttachment, intPos + 1)

'Debug.Print strFile
'Debug.Print intPos
' populate text boxes on form
Me.txtAttachment = strAttachment


End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub

 

[Minty]

Well, something else is going on here then.

Change your code to simply be this

Dim strAttachment As String
Dim strFolder As String
Dim strFile As String
Dim intPos As Integer

strAttachment = BrowseFile ' = "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\abc123\MyTestFIle.pdf"

Debug.Print strAttachment

Me.txtAttachment = Nz(strAttachment,"It was blank")

End Sub

 

[Gismo]

Well, something else is going on here then.

Change your code to simply be this

Dim strAttachment As String
Dim strFolder As String
Dim strFile As String
Dim intPos As Integer

strAttachment = BrowseFile ' = "\\Sjo2054\shared\DAW Sheet\Data Files\Attachments\abc123\MyTestFIle.pdf"

Debug.Print strAttachment

Me.txtAttachment = Nz(strAttachment,"It was blank")

End Sub

Yes, not sure why it is not populating, the code seems just fine

Then change does not make a difference either

The record to update is available
The form take the input when done manually

 

[Minty]

Delete the control.
Save the form, then compact and repair your database.

Now reopen the form, add a new control and call it something completely different from all the other iterations you have tried.
Apply the simple code.
See if that works.

 

[Gismo]

Delete the control.
Save the form, then compact and repair your database.

Now reopen the form, add a new control and call it something completely different from all the other iterations you have tried.
Apply the simple code.
See if that works.

Still no Luck

 

[Minty]

What happens if you simply hard code a value?
Remove all the other code

Me.txtAttachment = "My_Really_difficult _to_insert _filename.pdf"

You haven't got any other code associated with that control?

 

[Gismo]

What happens if you simply hard code a value?
Remove all the other code

Me.txtAttachment = "My_Really_difficult _to_insert _filename.pdf"

You haven't got any other code associated with that control?

I do not have another associated code as I have deleted the previous code and gave it a new name of strBrowseBtn

I created a new button for hard coding, works perfect

When I create this code into the original code, it does not work

 

[Minty]

Okay. Lets do baby steps.
Using your new button
Set a variable to the dummy fixed string.
Assign the variable to the control.

If that works then next stage

Assign that variable to the Browse file result.
Include a debug to make sure the value is there.

 

[Gismo]

Okay. Lets do baby steps.
Using your new button
Set a variable to the dummy fixed string.
Assign the variable to the control.

If that works then next stage

Assign that variable to the Browse file result.
Include a debug to make sure the value is there.

I Do not understand why this is happening, even with a simple code

Hard coding works perfect

Browsing for the file works
Selecting the file works

Debug.Print strAttachment works and displays correct info in immediate window

It just does not update the txtAttachment control

Private Sub Command10_Click()

Dim strAttachment As String

strAttachment = BrowseFile

Me.txtAttachment = strAttachment

End Sub

 

[Minty]

I'm at a loss - Can you put together a demo with enough of your code to make it all work.
e.g. a simple form, an empty table and the browse code, all exactly as you have it now.

In fact, do that and test it, then post it up here. It might be something weird on the form.