Access Query In VB

change the code, from Access VBA to VB 6 like this:

Public Function ConcatenateNotes(sOrder As String) As String

' Reference: Microsoft AciveX Data Objects 2.xx Library
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmd As ADODB.Command
Dim p As Integer
Dim sql As String
Dim sRet As String

' change start HERE ---------------------------------------------------------------------
'   NEW DECLARATION
Dim cnn as ADODB.Connection
' Now create the Connection
Set cnn = New ADODB.Connection
' and assign the Connection String to point to your MDB

  cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
  cnn.Mode = adModeReadWrite
  cnn.Open <path to your MDB>
' that is the end of the changes and enfd HERE --------------------------------------------------------

Set cmd = New ADODB.Command

sql = "SELECT Notes.NOLINE, Notes.THOURREF FROM Notes WHERE (((Notes.THOURREF)='" & sOrder & "'))"

With cmd
 .ActiveConnection = cnn
 .CommandType = adCmdText
 .CommandText = sql
 Set rs = .Execute
End With

With rs
 If Not .BOF And Not .EOF Then
   .MoveFirst
   While Not .EOF
   sRet = sRet & !NOLINE & ", "
   .MoveNext
   Wend
 End If
End With

p = Len(sRet) - 2

If sRet <> "" Then
 sRet = Left(sRet, p)
End If



That should do it.

AW

when i create the query and run it i get undefined function in expression

Dave

just to check i have done this correct i have put the code into a module

Dave

where do you get the error message?  On what line of code?

AW

and how are you calling this code, from a Button of a Form or ....?

AW

i am trying to call the code via a select query

SELECT DISTINCT Notes.THOURREF, ConcatenateNotes([THOURREF]) AS ConcatNotes
FROM Notes;

the error message isnt on a line its a message box

Dave

i also get a compile error, external name not defined on line
cnn.Open ([C:\Data.mdb])
if i try to make an exe

Dave

the sql changes when i add it to th query
SELECT DISTINCT
    Notes.THOURREF, ConcatenateNotes(`THOURREF`)
    AS ConcatNotes
FROM Notes

i think this is what is causing the error

Dave

try this change:

from:

cnn.Open ([C:\Data.mdb])

to:

cnn.Open "C:\Data.mdb"  

assuming that Data.Mdb is in fact locatged in the Root directory of your C: drive (which seems like a strange place to have the file, but if it is there, then that's where it is).

and you are asking for BIG trouble if your CancatenateNotes function, being called from the SQL you show, is in fact trying to open a different recordset (as the code shows).

What EXACTLY (in English, please) are you trying to do with this code?  If you can explain it clearly and succinctly, then perhaps we can assist you in achieving that goal.  But so far, what you have said and shown is VERY confsuing, and I am not at all clear on what your goal is.


AW

Hi Arthur

The function combines lines of text that are in a DBF file exported from a program we use as work

Example

Thourref    Noline

Sor001      Example1
Sor001      Example2
Sor001      Example3

Sor002      Example4
Sor002      Example5

After running the Function and Query I get

Thourref     Noline
Sor001        Example1, Example2, Example3
Sor002        Example4, Example5      

The reason the Data file is located at C:\Data.mdb is because I am working at home and will deploy it to the server next week

Thanks

Dave

well you can't call a function, the itself opens a recordset, from within SQL that will be used to create a recordset.  If you want to build the Noline as you have shown, then genrally that is done with code, much as you have shown in the Function defintion that you gave originally, but that fuction IS NOT called when building ANOTHER recordest (that is, it cannot be used like this:

SELECT DISTINCT
    Notes.THOURREF, ConcatenateNotes(`THOURREF`)
    AS ConcatNotes
FROM Notes


AW

What is the best way to call the function ?

this is the code i am using on my form

Option Explicit
Dim WithEvents adoPrimaryRS As Recordset
Dim mbChangedByCode As Boolean
Dim mvBookMark As Variant
Dim mbEditFlag As Boolean
Dim mbAddNewFlag As Boolean
Dim mbDataChanged As Boolean
Private fmtBooleanData As StdDataFormat
Private rs As ADODB.Recordset


Private Sub Form_Load()

  Dim db As Connection
  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Dave\My Documents\Visual Basic Project\Data.mdb;"
 

  Set adoPrimaryRS = New Recordset
  adoPrimaryRS.Open "SELECT Orders.THACC AS ACCOUNT, Orders.THDATE AS [DATE], Orders.THOURREF AS [SALES ORDER], Orders.THDATEDU AS [DUE DATE], Orders.ACCOMP AS CUSTOMER, Orders.THUSER3 AS DETAILS, Orders.THUSER4 AS DESCRIPTION, Orders.THORDOS AS [VALUE], Orders.THUSER2 AS [EMB/PR], Orders.ACUSER1 AS [TYPE], Complete.Complete AS [COMPLETE], Complete.Completesalesorder FROM Orders LEFT JOIN Complete ON Orders.THOURREF=Complete.CompleteSalesOrder", db, adOpenStatic, adLockOptimistic

  Set grdDataGrid.DataSource = adoPrimaryRS
Set fmtBooleanData = New StdDataFormat
      fmtBooleanData.Type = fmtBoolean
      fmtBooleanData.TrueValue = "Yes"
      fmtBooleanData.FalseValue = "No"
      fmtBooleanData.NullValue = ""

      Set grdDataGrid.Columns(10).DataFormat = fmtBooleanData
      grdDataGrid.Columns("VALUE").NumberFormat = "£0.00"
      grdDataGrid.Columns("complete").Button = True
      grdDataGrid.Columns("completesalesorder").Visible = False

  mbDataChanged = False
 
End Sub

Thanks Again sorry this turned out to be sutch a long Question

Dave

Sorry I forgot to mention the results of the are to be joined to my data on the datagrid thourref is the joined field

Dave

well, the problem is that you are using a Recordset as the DataSource for the Grid, and that makes it essentially IMPOSSIBLE to use the function that you have, to create the field in the manner that you want.  

the closest you could come would be to create a New, disconnected recordsdet, in code, popluate the fields from the Recordset that you are filling from the database, and massage the data for the field that you want to concatenate, as you cycle through the records.  

This will not be easy code to write.

AW

could i create an update query to populate a table im my mdb then get the data from there, if so what code would i use for it

Dave

(the query could be run from a button at any time)

glad to be of assistance

AW