Mixing
What causes the -2147352571 type mismatch error in VBA userform?
0
Currently I'm working on a Access-Excel connection with an userform. Within this userform data needs to be exported from Excel to Access. The following error occurs (vba error --2147352571 type mismatch) and i can't find where the problem is in my code assigned to the export button. This is my code:
Private Sub cmdAdd_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
'Error handler
On Error GoTo errHandler:
dbPath = ActiveSheet.Range("I9").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="TAGInformation", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'send the data
rst.AddNew
For i = 1 To 213
rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
Next i
rst.Update
'update for the next ID
Sheet1.Range("K9").Value = Arec1.Value + 1
'clear the userform values
For x = 1 To 213
Me.Controls("Arec" & x).Value = ""
Next
'add the next user ID
Me.Arec1 = Sheet1.Range("K9").Value
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'commuinicate with the user
MsgBox " The data has been successfully sent to the access database"
On Error GoTo 0
Exit Sub
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdAdd"
End Sub
excel vba ms-access access-vba
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
|
show 10 more comments
0
Currently I'm working on a Access-Excel connection with an userform. Within this userform data needs to be exported from Excel to Access. The following error occurs (vba error --2147352571 type mismatch) and i can't find where the problem is in my code assigned to the export button. This is my code:
Private Sub cmdAdd_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
'Error handler
On Error GoTo errHandler:
dbPath = ActiveSheet.Range("I9").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="TAGInformation", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'send the data
rst.AddNew
For i = 1 To 213
rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
Next i
rst.Update
'update for the next ID
Sheet1.Range("K9").Value = Arec1.Value + 1
'clear the userform values
For x = 1 To 213
Me.Controls("Arec" & x).Value = ""
Next
'add the next user ID
Me.Arec1 = Sheet1.Range("K9").Value
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'commuinicate with the user
MsgBox " The data has been successfully sent to the access database"
On Error GoTo 0
Exit Sub
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdAdd"
End Sub
excel vba ms-access access-vba
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
4
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
1
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
1
Turn off your error handler temporarily to see where the error pops up. E.g. comment this outOn Error GoTo errHandler:
– Ryan Wildry
Jan 2 at 17:25
1
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
2
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36
|
show 10 more comments
0
0
0
Currently I'm working on a Access-Excel connection with an userform. Within this userform data needs to be exported from Excel to Access. The following error occurs (vba error --2147352571 type mismatch) and i can't find where the problem is in my code assigned to the export button. This is my code:
Private Sub cmdAdd_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
'Error handler
On Error GoTo errHandler:
dbPath = ActiveSheet.Range("I9").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="TAGInformation", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'send the data
rst.AddNew
For i = 1 To 213
rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
Next i
rst.Update
'update for the next ID
Sheet1.Range("K9").Value = Arec1.Value + 1
'clear the userform values
For x = 1 To 213
Me.Controls("Arec" & x).Value = ""
Next
'add the next user ID
Me.Arec1 = Sheet1.Range("K9").Value
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'commuinicate with the user
MsgBox " The data has been successfully sent to the access database"
On Error GoTo 0
Exit Sub
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdAdd"
End Sub
excel vba ms-access access-vba
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
Currently I'm working on a Access-Excel connection with an userform. Within this userform data needs to be exported from Excel to Access. The following error occurs (vba error --2147352571 type mismatch) and i can't find where the problem is in my code assigned to the export button. This is my code:
Private Sub cmdAdd_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
'Error handler
On Error GoTo errHandler:
dbPath = ActiveSheet.Range("I9").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="TAGInformation", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'send the data
rst.AddNew
For i = 1 To 213
rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
Next i
rst.Update
'update for the next ID
Sheet1.Range("K9").Value = Arec1.Value + 1
'clear the userform values
For x = 1 To 213
Me.Controls("Arec" & x).Value = ""
Next
'add the next user ID
Me.Arec1 = Sheet1.Range("K9").Value
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'commuinicate with the user
MsgBox " The data has been successfully sent to the access database"
On Error GoTo 0
Exit Sub
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdAdd"
End Sub
excel vba ms-access access-vba
excel vba ms-access access-vba
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
asked Jan 2 at 17:14
Bram AgterbergBram Agterberg
33
33
4
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
1
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
1
Turn off your error handler temporarily to see where the error pops up. E.g. comment this outOn Error GoTo errHandler:
– Ryan Wildry
Jan 2 at 17:25
1
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
2
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36
|
show 10 more comments
4
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
1
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
1
Turn off your error handler temporarily to see where the error pops up. E.g. comment this outOn Error GoTo errHandler:
– Ryan Wildry
Jan 2 at 17:25
1
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
2
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36
4
4
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
1
1
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
1
1
Turn off your error handler temporarily to see where the error pops up. E.g. comment this out
On Error GoTo errHandler:– Ryan Wildry
Jan 2 at 17:25
Turn off your error handler temporarily to see where the error pops up. E.g. comment this out
On Error GoTo errHandler:– Ryan Wildry
Jan 2 at 17:25
1
1
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
2
2
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36
|
show 10 more comments
1 Answer
1
active
oldest
votes
0
As stated in the comments here is an approach that I hope at least gets you closer to your end goal.
A bit of background on my setup, as this code won't necessarily be a drop in and it works.
I have a table in an Access file, with the following schema.
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+I have a row to insert on Sheet1 of Excel. My headers are on Row 1, with the values on Row 2. I use the FieldName to locate the Field in the Fields Collection to be able to determine the type and get the appropriate value needed to add to the database.
Here is the code:
Private ErrorCollection As Collection
'Probably overkill, but I found added all the ADO Field Types
Public Enum ADOFieldTypes
adArray = 8192
adBigInt = 20
adBinary = 128
adBoolean = 11
adBSTR = 8
adChapter = 136
adChar = 129
adCurrency = 6
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adDecimal = 14
adDouble = 5
adEmpty = 0
adError = 10
adFileTime = 64
adGUID = 72
adIDispatch = 9
adInteger = 3
adIUnknown = 13
adLongVarBinary = 205
adLongVarChar = 201
adLongVarWChar = 203
adNumeric = 131
adPropVariant = 138
adSingle = 4
adSmallInt = 2
adTinyInt = 16
adUnsignedBigInt = 21
adUnsignedInt = 19
adUnsignedSmallInt = 18
adUnsignedTinyInt = 17
adUserDefined = 132
adVarBinary = 204
adVarChar = 200
adVariant = 12
adVarNumeric = 139
adVarWChar = 202
adWChar = 130
End Enum
Public Sub DBExample()
Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOURPATHGOESHERE"
Const SQL As String = "Select * from [Example] where id = 1" ' Simple query that returns all fields for the insert
Set ErrorCollection = New Collection
Dim conn As ADODB.connection: Set conn = New ADODB.connection
Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
Dim ws As Excel.Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long
Dim Fields As ADODB.Fields
Dim FieldName As String
Dim FieldValue As Variant
Dim FieldType As Long
Dim ErrorString As String
Dim ErrorItem As Variant
conn.Open ConnectionString
rst.Open SQL, conn, adOpenForwardOnly, adLockOptimistic
Set Fields = rst.Fields 'Get all the fields for the Database Table
rst.AddNew
For i = 1 To 10
FieldName = ws.Cells(1, i).Value2 'Get the FieldName
FieldType = rst.Fields(ws.Cells(1, i).Value2).Type 'Get the Field's Type from ADODB
FieldValue = ws.Cells(2, i).Value2 'Get the value to update
rst.Fields(FieldName).Value = getFieldValue(FieldType, FieldValue, FieldName) 'Assign the value
Next
'If, and only if there are no errors, update the DB
If ErrorCollection.Count = 0 Then
rst.Update
Else
'Print out error descriptions, which fields are still having issues?
For Each ErrorItem In ErrorCollection
ErrorString = ErrorItem & vbNewLine & ErrorString
Next
Debug.Print ErrorString
End If
conn.close
End Sub
'This maps the field type, and coverts the Excel value to that type
'I've only included the types I thought were most relevant for MS Access
'My Database has the following Type in a table: Date, ShortText, Long, Double, Integer, Decimal, Byte, Boolean (Yes/No), Currency, LongText
Private Function getFieldValue(FieldType As ADOFieldTypes, FieldValue As Variant, FieldName As String) As Variant
On Error GoTo errorHandler:
Select Case FieldType
Case adDate
getFieldValue = CDate(FieldValue)
Case adVarWChar
getFieldValue = FieldValue
Case adInteger
getFieldValue = CLng(FieldValue)
Case adDouble
getFieldValue = CDbl(FieldValue)
Case adSmallInt
getFieldValue = CInt(FieldValue)
Case adCurrency
getFieldValue = CCur(FieldValue)
Case adLongVarWChar
getFieldValue = FieldValue
Case adUnsignedTinyInt
getFieldValue = CByte(FieldValue)
Case adNumeric
getFieldValue = CDec(FieldValue)
Case adBoolean
getFieldValue = CBool(FieldValue)
End Select
Exit Function
errorHandler:
'This will return the FieldType Enum value, you can reference the number returned to ADOFieldTypes
ErrorCollection.Add "Could not add " & FieldName & " with value: " & FieldValue & " it has a type of " & FieldType
End Function
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in theFieldsto see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.
– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010492%2fwhat-causes-the-2147352571-type-mismatch-error-in-vba-userform%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
As stated in the comments here is an approach that I hope at least gets you closer to your end goal.
A bit of background on my setup, as this code won't necessarily be a drop in and it works.
I have a table in an Access file, with the following schema.
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+I have a row to insert on Sheet1 of Excel. My headers are on Row 1, with the values on Row 2. I use the FieldName to locate the Field in the Fields Collection to be able to determine the type and get the appropriate value needed to add to the database.
Here is the code:
Private ErrorCollection As Collection
'Probably overkill, but I found added all the ADO Field Types
Public Enum ADOFieldTypes
adArray = 8192
adBigInt = 20
adBinary = 128
adBoolean = 11
adBSTR = 8
adChapter = 136
adChar = 129
adCurrency = 6
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adDecimal = 14
adDouble = 5
adEmpty = 0
adError = 10
adFileTime = 64
adGUID = 72
adIDispatch = 9
adInteger = 3
adIUnknown = 13
adLongVarBinary = 205
adLongVarChar = 201
adLongVarWChar = 203
adNumeric = 131
adPropVariant = 138
adSingle = 4
adSmallInt = 2
adTinyInt = 16
adUnsignedBigInt = 21
adUnsignedInt = 19
adUnsignedSmallInt = 18
adUnsignedTinyInt = 17
adUserDefined = 132
adVarBinary = 204
adVarChar = 200
adVariant = 12
adVarNumeric = 139
adVarWChar = 202
adWChar = 130
End Enum
Public Sub DBExample()
Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOURPATHGOESHERE"
Const SQL As String = "Select * from [Example] where id = 1" ' Simple query that returns all fields for the insert
Set ErrorCollection = New Collection
Dim conn As ADODB.connection: Set conn = New ADODB.connection
Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
Dim ws As Excel.Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long
Dim Fields As ADODB.Fields
Dim FieldName As String
Dim FieldValue As Variant
Dim FieldType As Long
Dim ErrorString As String
Dim ErrorItem As Variant
conn.Open ConnectionString
rst.Open SQL, conn, adOpenForwardOnly, adLockOptimistic
Set Fields = rst.Fields 'Get all the fields for the Database Table
rst.AddNew
For i = 1 To 10
FieldName = ws.Cells(1, i).Value2 'Get the FieldName
FieldType = rst.Fields(ws.Cells(1, i).Value2).Type 'Get the Field's Type from ADODB
FieldValue = ws.Cells(2, i).Value2 'Get the value to update
rst.Fields(FieldName).Value = getFieldValue(FieldType, FieldValue, FieldName) 'Assign the value
Next
'If, and only if there are no errors, update the DB
If ErrorCollection.Count = 0 Then
rst.Update
Else
'Print out error descriptions, which fields are still having issues?
For Each ErrorItem In ErrorCollection
ErrorString = ErrorItem & vbNewLine & ErrorString
Next
Debug.Print ErrorString
End If
conn.close
End Sub
'This maps the field type, and coverts the Excel value to that type
'I've only included the types I thought were most relevant for MS Access
'My Database has the following Type in a table: Date, ShortText, Long, Double, Integer, Decimal, Byte, Boolean (Yes/No), Currency, LongText
Private Function getFieldValue(FieldType As ADOFieldTypes, FieldValue As Variant, FieldName As String) As Variant
On Error GoTo errorHandler:
Select Case FieldType
Case adDate
getFieldValue = CDate(FieldValue)
Case adVarWChar
getFieldValue = FieldValue
Case adInteger
getFieldValue = CLng(FieldValue)
Case adDouble
getFieldValue = CDbl(FieldValue)
Case adSmallInt
getFieldValue = CInt(FieldValue)
Case adCurrency
getFieldValue = CCur(FieldValue)
Case adLongVarWChar
getFieldValue = FieldValue
Case adUnsignedTinyInt
getFieldValue = CByte(FieldValue)
Case adNumeric
getFieldValue = CDec(FieldValue)
Case adBoolean
getFieldValue = CBool(FieldValue)
End Select
Exit Function
errorHandler:
'This will return the FieldType Enum value, you can reference the number returned to ADOFieldTypes
ErrorCollection.Add "Could not add " & FieldName & " with value: " & FieldValue & " it has a type of " & FieldType
End Function
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in theFieldsto see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.
– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
add a comment |
0
As stated in the comments here is an approach that I hope at least gets you closer to your end goal.
A bit of background on my setup, as this code won't necessarily be a drop in and it works.
I have a table in an Access file, with the following schema.
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+I have a row to insert on Sheet1 of Excel. My headers are on Row 1, with the values on Row 2. I use the FieldName to locate the Field in the Fields Collection to be able to determine the type and get the appropriate value needed to add to the database.
Here is the code:
Private ErrorCollection As Collection
'Probably overkill, but I found added all the ADO Field Types
Public Enum ADOFieldTypes
adArray = 8192
adBigInt = 20
adBinary = 128
adBoolean = 11
adBSTR = 8
adChapter = 136
adChar = 129
adCurrency = 6
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adDecimal = 14
adDouble = 5
adEmpty = 0
adError = 10
adFileTime = 64
adGUID = 72
adIDispatch = 9
adInteger = 3
adIUnknown = 13
adLongVarBinary = 205
adLongVarChar = 201
adLongVarWChar = 203
adNumeric = 131
adPropVariant = 138
adSingle = 4
adSmallInt = 2
adTinyInt = 16
adUnsignedBigInt = 21
adUnsignedInt = 19
adUnsignedSmallInt = 18
adUnsignedTinyInt = 17
adUserDefined = 132
adVarBinary = 204
adVarChar = 200
adVariant = 12
adVarNumeric = 139
adVarWChar = 202
adWChar = 130
End Enum
Public Sub DBExample()
Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOURPATHGOESHERE"
Const SQL As String = "Select * from [Example] where id = 1" ' Simple query that returns all fields for the insert
Set ErrorCollection = New Collection
Dim conn As ADODB.connection: Set conn = New ADODB.connection
Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
Dim ws As Excel.Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long
Dim Fields As ADODB.Fields
Dim FieldName As String
Dim FieldValue As Variant
Dim FieldType As Long
Dim ErrorString As String
Dim ErrorItem As Variant
conn.Open ConnectionString
rst.Open SQL, conn, adOpenForwardOnly, adLockOptimistic
Set Fields = rst.Fields 'Get all the fields for the Database Table
rst.AddNew
For i = 1 To 10
FieldName = ws.Cells(1, i).Value2 'Get the FieldName
FieldType = rst.Fields(ws.Cells(1, i).Value2).Type 'Get the Field's Type from ADODB
FieldValue = ws.Cells(2, i).Value2 'Get the value to update
rst.Fields(FieldName).Value = getFieldValue(FieldType, FieldValue, FieldName) 'Assign the value
Next
'If, and only if there are no errors, update the DB
If ErrorCollection.Count = 0 Then
rst.Update
Else
'Print out error descriptions, which fields are still having issues?
For Each ErrorItem In ErrorCollection
ErrorString = ErrorItem & vbNewLine & ErrorString
Next
Debug.Print ErrorString
End If
conn.close
End Sub
'This maps the field type, and coverts the Excel value to that type
'I've only included the types I thought were most relevant for MS Access
'My Database has the following Type in a table: Date, ShortText, Long, Double, Integer, Decimal, Byte, Boolean (Yes/No), Currency, LongText
Private Function getFieldValue(FieldType As ADOFieldTypes, FieldValue As Variant, FieldName As String) As Variant
On Error GoTo errorHandler:
Select Case FieldType
Case adDate
getFieldValue = CDate(FieldValue)
Case adVarWChar
getFieldValue = FieldValue
Case adInteger
getFieldValue = CLng(FieldValue)
Case adDouble
getFieldValue = CDbl(FieldValue)
Case adSmallInt
getFieldValue = CInt(FieldValue)
Case adCurrency
getFieldValue = CCur(FieldValue)
Case adLongVarWChar
getFieldValue = FieldValue
Case adUnsignedTinyInt
getFieldValue = CByte(FieldValue)
Case adNumeric
getFieldValue = CDec(FieldValue)
Case adBoolean
getFieldValue = CBool(FieldValue)
End Select
Exit Function
errorHandler:
'This will return the FieldType Enum value, you can reference the number returned to ADOFieldTypes
ErrorCollection.Add "Could not add " & FieldName & " with value: " & FieldValue & " it has a type of " & FieldType
End Function
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in theFieldsto see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.
– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
add a comment |
0
0
0
As stated in the comments here is an approach that I hope at least gets you closer to your end goal.
A bit of background on my setup, as this code won't necessarily be a drop in and it works.
I have a table in an Access file, with the following schema.
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+I have a row to insert on Sheet1 of Excel. My headers are on Row 1, with the values on Row 2. I use the FieldName to locate the Field in the Fields Collection to be able to determine the type and get the appropriate value needed to add to the database.
Here is the code:
Private ErrorCollection As Collection
'Probably overkill, but I found added all the ADO Field Types
Public Enum ADOFieldTypes
adArray = 8192
adBigInt = 20
adBinary = 128
adBoolean = 11
adBSTR = 8
adChapter = 136
adChar = 129
adCurrency = 6
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adDecimal = 14
adDouble = 5
adEmpty = 0
adError = 10
adFileTime = 64
adGUID = 72
adIDispatch = 9
adInteger = 3
adIUnknown = 13
adLongVarBinary = 205
adLongVarChar = 201
adLongVarWChar = 203
adNumeric = 131
adPropVariant = 138
adSingle = 4
adSmallInt = 2
adTinyInt = 16
adUnsignedBigInt = 21
adUnsignedInt = 19
adUnsignedSmallInt = 18
adUnsignedTinyInt = 17
adUserDefined = 132
adVarBinary = 204
adVarChar = 200
adVariant = 12
adVarNumeric = 139
adVarWChar = 202
adWChar = 130
End Enum
Public Sub DBExample()
Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOURPATHGOESHERE"
Const SQL As String = "Select * from [Example] where id = 1" ' Simple query that returns all fields for the insert
Set ErrorCollection = New Collection
Dim conn As ADODB.connection: Set conn = New ADODB.connection
Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
Dim ws As Excel.Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long
Dim Fields As ADODB.Fields
Dim FieldName As String
Dim FieldValue As Variant
Dim FieldType As Long
Dim ErrorString As String
Dim ErrorItem As Variant
conn.Open ConnectionString
rst.Open SQL, conn, adOpenForwardOnly, adLockOptimistic
Set Fields = rst.Fields 'Get all the fields for the Database Table
rst.AddNew
For i = 1 To 10
FieldName = ws.Cells(1, i).Value2 'Get the FieldName
FieldType = rst.Fields(ws.Cells(1, i).Value2).Type 'Get the Field's Type from ADODB
FieldValue = ws.Cells(2, i).Value2 'Get the value to update
rst.Fields(FieldName).Value = getFieldValue(FieldType, FieldValue, FieldName) 'Assign the value
Next
'If, and only if there are no errors, update the DB
If ErrorCollection.Count = 0 Then
rst.Update
Else
'Print out error descriptions, which fields are still having issues?
For Each ErrorItem In ErrorCollection
ErrorString = ErrorItem & vbNewLine & ErrorString
Next
Debug.Print ErrorString
End If
conn.close
End Sub
'This maps the field type, and coverts the Excel value to that type
'I've only included the types I thought were most relevant for MS Access
'My Database has the following Type in a table: Date, ShortText, Long, Double, Integer, Decimal, Byte, Boolean (Yes/No), Currency, LongText
Private Function getFieldValue(FieldType As ADOFieldTypes, FieldValue As Variant, FieldName As String) As Variant
On Error GoTo errorHandler:
Select Case FieldType
Case adDate
getFieldValue = CDate(FieldValue)
Case adVarWChar
getFieldValue = FieldValue
Case adInteger
getFieldValue = CLng(FieldValue)
Case adDouble
getFieldValue = CDbl(FieldValue)
Case adSmallInt
getFieldValue = CInt(FieldValue)
Case adCurrency
getFieldValue = CCur(FieldValue)
Case adLongVarWChar
getFieldValue = FieldValue
Case adUnsignedTinyInt
getFieldValue = CByte(FieldValue)
Case adNumeric
getFieldValue = CDec(FieldValue)
Case adBoolean
getFieldValue = CBool(FieldValue)
End Select
Exit Function
errorHandler:
'This will return the FieldType Enum value, you can reference the number returned to ADOFieldTypes
ErrorCollection.Add "Could not add " & FieldName & " with value: " & FieldValue & " it has a type of " & FieldType
End Function
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
As stated in the comments here is an approach that I hope at least gets you closer to your end goal.
A bit of background on my setup, as this code won't necessarily be a drop in and it works.
I have a table in an Access file, with the following schema.
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+I have a row to insert on Sheet1 of Excel. My headers are on Row 1, with the values on Row 2. I use the FieldName to locate the Field in the Fields Collection to be able to determine the type and get the appropriate value needed to add to the database.
Here is the code:
Private ErrorCollection As Collection
'Probably overkill, but I found added all the ADO Field Types
Public Enum ADOFieldTypes
adArray = 8192
adBigInt = 20
adBinary = 128
adBoolean = 11
adBSTR = 8
adChapter = 136
adChar = 129
adCurrency = 6
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adDecimal = 14
adDouble = 5
adEmpty = 0
adError = 10
adFileTime = 64
adGUID = 72
adIDispatch = 9
adInteger = 3
adIUnknown = 13
adLongVarBinary = 205
adLongVarChar = 201
adLongVarWChar = 203
adNumeric = 131
adPropVariant = 138
adSingle = 4
adSmallInt = 2
adTinyInt = 16
adUnsignedBigInt = 21
adUnsignedInt = 19
adUnsignedSmallInt = 18
adUnsignedTinyInt = 17
adUserDefined = 132
adVarBinary = 204
adVarChar = 200
adVariant = 12
adVarNumeric = 139
adVarWChar = 202
adWChar = 130
End Enum
Public Sub DBExample()
Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOURPATHGOESHERE"
Const SQL As String = "Select * from [Example] where id = 1" ' Simple query that returns all fields for the insert
Set ErrorCollection = New Collection
Dim conn As ADODB.connection: Set conn = New ADODB.connection
Dim rst As ADODB.Recordset: Set rst = New ADODB.Recordset
Dim ws As Excel.Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long
Dim Fields As ADODB.Fields
Dim FieldName As String
Dim FieldValue As Variant
Dim FieldType As Long
Dim ErrorString As String
Dim ErrorItem As Variant
conn.Open ConnectionString
rst.Open SQL, conn, adOpenForwardOnly, adLockOptimistic
Set Fields = rst.Fields 'Get all the fields for the Database Table
rst.AddNew
For i = 1 To 10
FieldName = ws.Cells(1, i).Value2 'Get the FieldName
FieldType = rst.Fields(ws.Cells(1, i).Value2).Type 'Get the Field's Type from ADODB
FieldValue = ws.Cells(2, i).Value2 'Get the value to update
rst.Fields(FieldName).Value = getFieldValue(FieldType, FieldValue, FieldName) 'Assign the value
Next
'If, and only if there are no errors, update the DB
If ErrorCollection.Count = 0 Then
rst.Update
Else
'Print out error descriptions, which fields are still having issues?
For Each ErrorItem In ErrorCollection
ErrorString = ErrorItem & vbNewLine & ErrorString
Next
Debug.Print ErrorString
End If
conn.close
End Sub
'This maps the field type, and coverts the Excel value to that type
'I've only included the types I thought were most relevant for MS Access
'My Database has the following Type in a table: Date, ShortText, Long, Double, Integer, Decimal, Byte, Boolean (Yes/No), Currency, LongText
Private Function getFieldValue(FieldType As ADOFieldTypes, FieldValue As Variant, FieldName As String) As Variant
On Error GoTo errorHandler:
Select Case FieldType
Case adDate
getFieldValue = CDate(FieldValue)
Case adVarWChar
getFieldValue = FieldValue
Case adInteger
getFieldValue = CLng(FieldValue)
Case adDouble
getFieldValue = CDbl(FieldValue)
Case adSmallInt
getFieldValue = CInt(FieldValue)
Case adCurrency
getFieldValue = CCur(FieldValue)
Case adLongVarWChar
getFieldValue = FieldValue
Case adUnsignedTinyInt
getFieldValue = CByte(FieldValue)
Case adNumeric
getFieldValue = CDec(FieldValue)
Case adBoolean
getFieldValue = CBool(FieldValue)
End Select
Exit Function
errorHandler:
'This will return the FieldType Enum value, you can reference the number returned to ADOFieldTypes
ErrorCollection.Add "Could not add " & FieldName & " with value: " & FieldValue & " it has a type of " & FieldType
End Function
+---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------++---------------+--------------+
| FieldName | FieldType |
+---------------+--------------+
| DateField | Date |
+---------------+--------------+
| TextField | ShortText |
+---------------+--------------+
| LongIntField | Long Integer |
+---------------+--------------+
| DoubleField | Double |
+---------------+--------------+
| IntField | Integer |
+---------------+--------------+
| CurrencyField | Currency |
+---------------+--------------+
| LongTextField | LongText |
+---------------+--------------+
| ByteField | Byte |
+---------------+--------------+
| DecimalField | Decimal |
+---------------+--------------+
| YesNoField | Boolean |
+---------------+--------------+answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
answered Jan 3 at 1:48
Ryan WildryRyan Wildry
3,5611926
3,5611926
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in theFieldsto see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.
– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
add a comment |
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in theFieldsto see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.
– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
I applied your code to my context, it seems to have solved the type error. When i try to run my userform i get an ''error 3265 item not found in this collection''. The error occurs in the following line of code: rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Which is part of the following loop: 'send the data rst.AddNew For L = 1 To 211 rst(Cells(1, L).value) = Me.Controls("Arec" & L).value Next L rst.Update
– Bram Agterberg
Jan 3 at 9:37
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in the
Fields to see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.– Ryan Wildry
Jan 3 at 12:31
Well that's progress. This error is new, it means the field name doesn't match the field name in the database. Check your database to ensure each field name or index exists in the database. You can print out the field names in the
Fields to see what's up. At this point it is starting to sound like a new question, if you are still having trouble I'd recommend creating a new question for this one.– Ryan Wildry
Jan 3 at 12:31
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
thanks for your help! It's starting to come together with declaring the Arec(textboxes) and defining them as ArecInt, ArecText etc. When I needed I'll post a new question.
– Bram Agterberg
Jan 3 at 18:03
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
Glad it helped, if this helped resolve your original question. Please consider marking as the accepted answer.
– Ryan Wildry
Jan 3 at 18:09
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010492%2fwhat-causes-the-2147352571-type-mismatch-error-in-vba-userform%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
Which line of code is causing the error?
– Robert Harvey♦
Jan 2 at 17:15
1
I don't see any validation of the values you're setting fields to at all. It could be any of the 213 fields in your table, so without knowing the schema of the table and the data you're trying to import, this is likely unanswerable.
– Comintern
Jan 2 at 17:20
1
Turn off your error handler temporarily to see where the error pops up. E.g. comment this out
On Error GoTo errHandler:– Ryan Wildry
Jan 2 at 17:25
1
@ryanwildry my error occurs in the following line: rst(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
– Bram Agterberg
Jan 2 at 17:28
2
The method you use to add the fields doesn't matter - what matters is that one of them doesn't match the expected type. The only way you're going to find it is by stepping through it with a debugger, comparing each value to your schema, and catching the one with the wrong data type. Nobody here can do that, because we don't know the schema, and we don't know the data you're trying to put into that schema.
– Comintern
Jan 2 at 17:36