2007 Concatenation problems

  • Thread starter Thread starter AllenK
  • Start date Start date
A

AllenK

I am using Mr. Hookums formula he gave me for concatenation in Access 2003
and it works fine. As soon as I copy it into 2007 I get error messages. I
have tried converting the database to 2007, but get errors. I created 2007
database and imported all files from 2003. Switchboard and concatenation no
longer work.
Here is what I am getting for messages.

Microsoft Visual Basic "Compile error: User-defined type not defined" and
here is the code, first three lines below are highlighted yellow;
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function


Here is the query I am trying to run;
OE Number: Concatenate("SELECT comp_no FROM OE_Interchanges_tbl WHERE
part_no ='" & [part_no] & "'")
 
AllenK said:
I am using Mr. Hookums formula he gave me for concatenation in Access
2003 and it works fine. As soon as I copy it into 2007 I get error
messages. I have tried converting the database to 2007, but get
errors. I created 2007 database and imported all files from 2003.
Switchboard and concatenation no longer work.
Here is what I am getting for messages.

Microsoft Visual Basic "Compile error: User-defined type not defined"
and here is the code, first three lines below are highlighted yellow;
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset
Click to expand...

You need to set a reference to the ADO library ... open the module
containing the code, go to Tools|References, and scroll down and select the
latest version of the Microsoft ActiveX Objects installed on your machine
(probably 2.8)
 
Back
Top