CODEWITHSUNDEEP
ms-accessvba
TimboMan
TimboMan

Reputation: 3

Run-time error '13' Type Mismatch MS Access VBA array

Have been trying to insert a Street, Suburb and a postcode into a table but keep getting

Run-time error '13' Type mismatch

Here is the code:

Sub arrayData()

Dim CustomerNames() As Variant
Dim num As Long, dbs As Database, InsertReocrd As String
Dim CustomerID As Long, num1 As Long
Dim CustomerName As String
Dim Street As String, Suburb As String, Postcode As Integer

Set dbs = CurrentDb()
CustomerID = 0
For num1 = 0 To 50000
CustomerID = CustomerID + 1
CustomerNames = Array(...)

Street = Array("Short", "Lygon", "Flinders", "Elizabeth", "King") //ERROR OCURRS HERE

Suburb = Array("Sandringham", "Brighton", "St Kilda", "Melbourne", "Carlton") //ERROR OCURRS HERE

Postcode = Array("3165", "3298", "3145", "3144", "3000") //ERROR OCURRS HERE

num = Int((250 - 0 + 1) * Rnd + 0)
CustomerName = CustomerNames(num)
InsertRecord = "INSERT INTO CUSTOMER (CustomerID , CustomerName, StreetName, Suburb) VALUES (" & "'" & CustomerID & "'" & "," _
                                                                   & "'" & CustomerName & "'" & "'" & StreetName & "'" & ")"

dbs.Execute InsertRecord
Debug.Print CustomerID, CustomerName, Street, Suburb
Next

End Sub

Upvotes: 0

Views: 2246

Answers (3)

GoneFishin'
GoneFishin'

Reputation: 1

Sorry if this doesn't exactly fit the topic. Just in case any other poor fool spends hours trying to figure out why they're getting a 'Type Mismatch' on a VBA recordset.addnew, check the [Default Values] of the fields/columns in the table. I changed a TEXT field to a DATE field, but neglected to remove the "" in [Default Value]. (trying to set any text value to a date field is a no-no).

Upvotes: 0

Gustav
Gustav

Reputation: 55831

As shown yesterday, simplify your concatenation, and you also need and array for the CustomerID and a loop as well:

For n = 1 To 5     
    InsertRecord = "INSERT INTO CUSTOMER (CustomerID , CustomerName, StreetName, Suburb) VALUES (" & CustomerID(n) & ",'" & CustomerName(n) & "','" & StreetName(n) & "','" & Suburb(n) & "')"

If you really wish to insert 50000 customers this way(?) (you wouldn't), follow the advice from André and use DAO.

Upvotes: 0

Andre
Andre

Reputation: 27634

Variables that should hold arrays need to be declared as Variant, not as String.

So:

Dim Street As Variant, Suburb As Variant, Postcode As Variant

Note that your INSERT statement is missing the value for Suburb.

Using DAO.Recordset.AddNew etc. would probably be faster and better readable.

Upvotes: 1

Related Questions