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

Question

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

Andre · Accepted Answer · 2017-04-25 07:38:58Z

1

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.

answered Apr 25, 2017 at 7:38

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TimboMan Over a year ago

Can you show me what you mean, when it comes to writing the INSERT statement as I am struggling with the concatenation.

Gustav · Accepted Answer · 2017-04-25 08:34:33Z

0

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.

answered Apr 25, 2017 at 8:34

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

TimboMan Over a year ago

I keep getting 'Expected array' what is causing it? As I generating random numbers for this.

Gustav Over a year ago

In my code, CustomerID is an array as well. So modify your code to adopt.

GoneFishin' · Accepted Answer · 2018-05-08 16:01:55Z

0

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).

answered May 8, 2018 at 16:01

GoneFishin'

1

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related