If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2007 Concatenation problems
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] & "'") -- Thank you to all who have helped me! |
#2
|
|||
|
|||
2007 Concatenation problems
AllenK wrote:
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 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) -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
2007 Concatenation problems
It worked! Thank you sooooo much!!!!
-- Thank you to all who have helped me! "Bob Barrows" wrote: AllenK wrote: 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 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) -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" . |
Thread Tools | |
Display Modes | |
|
|