Solved Compile Error: Expected user-defined type, not project

[Hamdard]

Hi experts,
I want to copy data from one table into another using the following code. Before copy, this codes runs through the objective field and looks for comma (,), when found it splits the word before a comma into a new row in the destination table. However, when I click on the button to run this code, I get this message "Compile Error: Expected user-defined type, not project". I looked around for some help topics but no luck. Could you help me understand what the problem is and the solution?

Private Sub Command0_Click()
DoCmd.SetWarnings Off
On Error GoTo Error_Resume
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal As String
Dim TestArray() As String
Dim fieldMechanism, fieldObjectives, fieldProjects As String
Dim i As Integer

sqlStr = "SELECT [Mechanism], [Objectives], [Projects] FROM Tab_ProjectsByObjectives_ForSplitByObjectives"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fielMechanism = rs.Fields(0)
fieldObjectives = rs.Fields(1)
fieldProjects = rs.Fields(2)
TestArray() = Split(fielObjectives, ", ")

For i = 0 To UBound(TestArray)
If TestArray(i) <> "" Then

arrayVal = TestArray(i)

insertSQL = "INSERT INTO Tab_ProjectsByObjectives_SplittedByObjectives([Mechanism], [Objectives], [Projects]) " _
& "VALUES(""" & fieldMechanism & """, """ & arrayVal & """, """ & fieldProjects & """)"
DoCmd.RunSQL (insertSQL)
Error_Resume:
Resume Next

End If
Next i
rs.MoveNext
Loop
End Sub

 

[theDBguy]

Hi. When you go to Debug mode, which line is highlighted?

 

[The_Doc_Man]

A couple of comments are in order. First, a simple one.

This statement: Dim sqlStr, insertSQL, arrayVal As String doesn't do what you think it does. Only arrayVal is actually a string. The other two elements are variants. Correct syntax to make all three of those strings is Dim sqlStr As String, insertSQL As String, arrayVal As String because the "As String" modifiers do not distribute across all three elements. They ONLY apply to the variable that they immediately follow.

I think you have an error in this: TestArray() = Split(fielObjectives, ", ") in that if that was a cut/paste, you have spelled something wrong. AND you did it when filling variables from rs.Fields().

Now, the next comment: It is hard to be sure, but the only other thing that comes to mind is that you should always qualify anything you know to be a structure, such as recordset objects. They can be DAO.recordset or ADO.recordset or some other type of recordset, but they are external to Access in that they are defined in a library, not in Access itself. For me it is a matter of what I think to be "good practice" to ALWAYS qualify objects with where they originate.

The User-Defined Data Type error PROBABLY comes from the TestArray() = Split(fielObjectives, ", ") line because Split doesn't know what fielObjectives is. You didn't define that. You defined fieldObjectives in your Dim statement.

 

[Hamdard]

Hi. When you go to Debug mode, which line is highlighted?

The following line is highlighted.

Dim db As Database

 

[theDBguy]

The following line is highlighted.

Dim db As Database

Try changing it to

Dim db As DAO.Database

 

[GPGeorge]

The following line is highlighted.

Dim db As Database

In the more recent versions of Access, there has been less and less tolerance of ambiguity (which is a good thing to my way of thinking, btw)

What that means in practice is that we can no longer use a naked "Database" reference.
We need to qualify that as a DAO.Database, or as an ADO.Database as the case may be in the particular procedure at hand.
The same is true of the Recordset Dimmed in the subsequent line....

 

[Hamdard]

A couple of comments are in order. First, a simple one.

This statement: Dim sqlStr, insertSQL, arrayVal As String doesn't do what you think it does. Only arrayVal is actually a string. The other two elements are variants. Correct syntax to make all three of those strings is Dim sqlStr As String, insertSQL As String, arrayVal As String because the "As String" modifiers do not distribute across all three elements. They ONLY apply to the variable that they immediately follow.

I think you have an error in this: TestArray() = Split(fielObjectives, ", ") in that if that was a cut/paste, you have spelled something wrong. AND you did it when filling variables from rs.Fields().

Now, the next comment: It is hard to be sure, but the only other thing that comes to mind is that you should always qualify anything you know to be a structure, such as recordset objects. They can be DAO.recordset or ADO.recordset or some other type of recordset, but they are external to Access in that they are defined in a library, not in Access itself. For me it is a matter of what I think to be "good practice" to ALWAYS qualify objects with where they originate.

The User-Defined Data Type error PROBABLY comes from the TestArray() = Split(fielObjectives, ", ") line because Split doesn't know what fielObjectives is. You didn't define that. You defined fieldObjectives in your Dim statement.

Thank you for the advises, very useful. Now the problem is fixed and it works.

 

[Hamdard]

Try changing it to

Dim db As DAO.Database

Changed it and it worked. I also had spell mistakes and errors in dim statements as The_Doc_Man pointed out. Thank you both for your expert advises.

 

[theDBguy]

Changed it and it worked. I also had spell mistakes and errors in dim statements as The_Doc_Man pointed out. Thank you both for your expert advises.

Glad we could assist. Good luck with your project.

 

[The_Doc_Man]

Great! Good luck on further progress. And just watch for spelling. Access just doesn't like spelling errors.

As a suggestion to help prevent them, always define Option Explicit at the beginning of any code module, whether it is a form's class module or an Access general module. That way, spelling errors get highlighted quickly, allowing you to fix them. In the examples you showed us, for example, that fielObjectives (without the "d") would have been called out. You would have immediately realized that you had a spelling error because that would have counted as an undeclared variable in Option Explicit context. Just a tip on how to prevent that error from cropping up again.