Run arbitrary INSERT INTO SQL Query using parameters
This code can't handle fields with spaces in them unless you specifically pass them pre-wrapped in square brackets. Things such as My Field would need to go into your parameter as "field1, [My odd field], field3". So, a definite area for improvement would be to handle that corner case.

I also don't really care for the comma delimited string parameter. I feel that it would be much cleaner and nicer to pass in a string array representing the field names. So, something along the lines of this psuedo code might be a bit cleaner.

Dim fields As Variant
fields = Array("field1","My odd field","field3")

InsertInto "Table1", fields, values)

' ...

Public Sub InsertInto(...)
    AddBracketsToStrings fields

    '...

End Sub




The next thing I notice is these comments:


'Assigns strParams value in format "[p1],[p2],[p3],...,[pN]"
...
'Construct SQL
...
'Give values to parameters
...



The comments are nice, and add a bit of clarity to the code, but these kinds of comments indicate an opportunity to extract subroutines/functions. Each of these comments could be replaced with a well named function.

ConvertValuesToParameters values
Set qdf = CurrentDb.CreateQueryDef("", ConstructSQL(TableName, Fields, strParams))
SetParameterValues qdf, values


Which brings me to the SQL Injection issue. It's never a good idea to concatenate SQL strings together. It's much better to use parameterized queries. This may require that you switch to using ADODB instead of DAO. I'm not sure. I don't know if DAO supports parameterized queries.




Case 13   'Type mismatch i.e. Values is not an array



That's a good comment, but a constant value would be better.

Const TypeMismatchError As Long = 13

Select Case Err.Number
    Case TypeMisMatchError 
        ' ...