Solved Type Mismatch when attempting to attach a selected file

[Sam Summers]

Hi,

Now this worked before but for some reason it is throwing the error message - Runtime Error '13' Type mismatch
at this line:

Set fldAttach = rstAttach.Fields("FileData")

I've set breakpoints and broken it down and checked the tables and associated forms but still get the same error?
Here is the code that was working.

Option Compare Database
Option Explicit

Private Sub AddCertBtn_Click()

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

End Sub

Public Function AddAttachment(strTableName, strAttachField, strIDfield As String, i As Long, lngCertID 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 & " AND CertID = " & lngCertID, 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

I did change a forms data from a table to a query using the query builder from the properties window. Could this have done it?

 

[Kayleigh]

I did change a forms data from a table to a query using the query builder from the properties window. Could this have done it?

It should not have affected it but maybe check that all the relevant fields exist in the query.

What is your back end?

 

[MajP]

Looks correct, but is "filedata" still the correct name of the mvf?
The query is a non player. Your code is based on a select query from the table. There is no reference to the form's recordset.

 

[arnelgp]

shouldn't rstMain be declared as DAO.Recordset2 too?
see sample on the web.

 

[theDBguy]

See if this article applies to you.

Access – Bug – .Fields Not Working Anymore | DEVelopers HUT

Another bug seems to have surfaced with Microsoft Access 2206 in which DAO .Fields doesn't work properly anymore and seems to return .Field3 instead of .Field2

www.devhut.net

 

[Sam Summers]

Looks correct, but is "filedata" still the correct name of the mvf?
The query is a non player. Your code is based on a select query from the table. There is no reference to the form's recordset.

The actual name in the 'EmployeeCert" table is CertImage so i changed FileData to that and got the error 'Item not found in this collection'

 

[Sam Summers]

It should not have affected it but maybe check that all the relevant fields exist in the query.

What is your back end?

I haven't split this database

 

[Sam Summers]

shouldn't rstMain be declared as DAO.Recordset2 too?
see sample on the web.

So did that which compiled but when run it fails at the same line and on mouse over it says 'rstAttach.Fields("FileData") = Null'

 

[Gasman]

Did you look at theDBguy's link?
Perhaps try Fields2 and 3?

Are you omitting records with no attachments?, else they would be Null, surely?

 

[arnelgp]

you can directly put the file there, provided strFileName does point to a file.

rstAttach.Fields("FileData").LoadFromFile strFileName

 

[Cotswold]

Why not slip some VarType() checks into your code on some or all of the variables in use?

Results will be :
0 = Empty
1 = Null
2 = Integer
3 = Long
4 = Single
5 = Double
6 = Currency
7 = Date
8 = String
9 = vbObject
10 = VBError

VarType() is a very handy for all sorts of validation

 

[MajP]

IMO what the @dbguy is suggesting seems the most likely based on the error message and location of the error. If that is the case i do not know the workaround except wait for a fix from MS. The workaround in that post is to use a generic Field assignment and not a Field2. However only Field2 supports load from file.

Field2 object (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[MajP]

After a test i think you can use a generic Field. I think it subclasses in a way i do not understand.
If i define a standard Recordset and Field variable on any field and then use the Typename function it shows Recordset2 and Field2 by default although declared as Recordset and Field.

 

[Sam Summers]

Right, i have just come back to this and it literally was that initial link from DBguy!

I simply changed DAO.Field2 to DAO.Field

Amazing.

Once again - so many thanks to all of you!

 

[theDBguy]

Right, i have just come back to this and it literally was that initial link from DBguy!

I simply changed DAO.Field2 to DAO.Field

Amazing.

Once again - so many thanks to all of you!

Hi. Congratulations! Glad to hear you got it sorted out. Cheers!