Solved Missing something in concatenation

[Sam Summers]

Hi,

Can someone give me a clue here?
Tried multiple things and its not quite there.
I need to attach an image using the EmployeeID and also the CertID.

Call AddAttachment("EmployeeCert", "CertImage", "EmployeeID", Me.EmployeeID " & " "CertID", Me.CertID)

But it is failing at the ampersand point and when i tried this:

Call AddAttachment("EmployeeCert", "CertImage", "EmployeeID", Me.EmployeeID, "CertID", Me.CertID)

It is returning 'Wrong number of arguments or invalid property assignments'

Many thanks in advance

 

[GPGeorge]

Perhaps it would be helpful to provide a fuller context, such as the full text of that procedure being called. Apparently it's a Function or sub named "AddAttachment" and you need to pass those arguments to it.

That said, the quotes between Me.EmployeeID and CertID look hinky to me.

 

[Sam Summers]

Perhaps it would be helpful to provide a fuller context, such as the full text of that procedure being called. Apparently it's a Function or sub named "AddAttachment" and you need to pass those arguments to it.

That said, the quotes between Me.EmployeeID and CertID look hinky to me.

Hi and thank you for replying.

So basically this code works fine and does what it is supposed to:

Private Sub AddCertBtn_Click()

Call AddAttachment("EmployeeCert", "CertImage", "EmployeeID", Me.EmployeeID)

End Sub

Public Function AddAttachment(strTableName, strAttachField, strIDfield As String, i As Long)

    Dim fd As FileDialog
    Dim oFD As Variant
    Dim strFileName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Title = "Choose File"
        .InitialView = msoFileDialogViewDetails
        .Show

        For Each oFD In .SelectedItems
            strFileName = oFD
        Next oFD
        On Error GoTo 0
    End With

    Set fd = Nothing

    Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, _
        fldAttach As DAO.Field2
    Set cdb = CurrentDb
    Set rstMain = cdb.OpenRecordset("SELECT " & strAttachField & " FROM " & strTableName & " where " & strIDfield & "= " & i, dbOpenDynaset)

    rstMain.Edit
   Set rstAttach = rstMain(strAttachField).Value
    rstAttach.AddNew

    Set fldAttach = rstAttach.Fields("FileData")

    fldAttach.LoadFromFile strFileName
    rstAttach.Update
    rstAttach.Close
    Set rstAttach = Nothing
    rstMain.Update
    rstMain.MoveNext
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing
End Function

But the employee usually has more than one Certificate so i need to make it also recognize the specific CertID as well.

Hope that makes sense?

 

[Gasman]

Well your function needs to cater for that?
You quotes are wrong in the call anyway.
How is the idfield and certid meant to be identified?

 

[Sam Summers]

Well your function needs to cater for that?
You quotes are wrong in the call anyway.
How is the idfield and certid meant to be identified?

The call works in the last code i posted.

Other than that I don't know and that's why I am asking on here.

 

[Gasman]

Ok, let us try another way.
For the Employee field, what do you expect to see?
12345601
where 12345 is the employee ID and 01 the cert id?

Look at the Select statement. How are you meant to include the CertID in that statement?
You could have the CertID as an optional 5th parameter and amend the select to suit?

 

[CJ_London]

The error message is quite clear ‘wrong number of arguments’

The function requires 4, you have applied 6

 

[Sam Summers]

Good morning,

Thank you for your assistance.
I have an Employee Table and a Certificate Table joined by an EmployeeCert Table to give me a many to many.
In the employee table i have EmployeeID (PK)
EmployeeCert table i have EmpCertID and CertID as foreign keys and Certificate table i have CertID (PK)

I understand what you mean that i'm not getting the other arguments required to the function. I'm just not sure how to do it but i will go and have another look and try.

Thank you

 

[Gasman]

I believe you will need to adjust the function to take into account the CertID as well.?
Perhaps that always defaults to 1 ?, so you could make it optional and if nothing passed for certID, then set it to 1, 5 then use in the function.
Easier to pass all the time though?, up to you.

Something along the lines of

Public Function AddAttachment(strTableName, strAttachField, strIDfield As String, i As Long, lngCertID As Long)
....
.....
    Set rstMain = cdb.OpenRecordset("SELECT " & strAttachField & " FROM " & strTableName & " where " & strIDfield & "= " & i & " AND CertID = " & lngCertID,  dbOpenDynaset)

You would call it with

Call AddAttachment("EmployeeCert", "CertImage", "EmployeeID", Me.EmployeeID, Me.CertID)

 

[Sam Summers]

I believe you will need to adjust the function to take into account the CertID as well.?
Perhaps that always defaults to 1 ?, so you could make it optional and if nothing passed for certID, then set it to 1, 5 then use in the function.
Easier to pass all the time though?, up to you.

Something along the lines of

Public Function AddAttachment(strTableName, strAttachField, strIDfield As String, i As Long, lngCertID As Long)
....
.....
    Set rstMain = cdb.OpenRecordset("SELECT " & strAttachField & " FROM " & strTableName & " where " & strIDfield & "= " & i & " AND CertID = " & lngCertID,  dbOpenDynaset)

You would call it with

Call AddAttachment("EmployeeCert", "CertImage", "EmployeeID", Me.EmployeeID, Me.CertID)

Thats it!!

I just didnt know how to combine the two but it all works. I had to add a bit into the view Certificate form as well to make only the correct Certificate display but it is working great.

Thank you once again.

And now onto the next problem..............