Invalid procedure call or argument brows and attach file (1 Viewer)

Gismo

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

1635334851017.png


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
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

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

Gismo

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

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

1635336921935.png


In another form, i then display the PDF file
 

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

1635339095137.png
 

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

1635341123784.png
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
I wouldn't have called both the variable and the control the same thing, but that should avoid any issues.

Try this please
Code:
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
I wouldn't have called both the variable and the control the same thing, but that should avoid any issues.

Try this please
Code:
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

1635398716360.png


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
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
Well, something else is going on here then.

Change your code to simply be this
Code:
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
Well, something else is going on here then.

Change your code to simply be this
Code:
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

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 12:46
Joined
Jun 12, 2017
Messages
1,298
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
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,355
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.
 

Users who are viewing this thread

Top Bottom