using function to get multi values in query

[dominic]

I had write a function in module to get multi value and used it in Query
unsuccessful. How am I modify to get "Fax" value using function in SQL. I
prefer to use such function to get multi values in stead of modified it to
return only "ONE" value.

*** SQL statement

SELECT tblClientRecord.RecordID, GetClientInfo([RecordID]) AS Fax
FROM tblClientRecord;


***** function
Public getClientInfo(ClientID) As Variant
Dim tmp(1 To 3) As Variant
strSQL = "SELECT UserName, Phone, Fax FROM tblClientRecord WHERE RecordID =
" & ClientID

CurrentDBConnect
OpenRst (strSQL)

With rstObject
tmp(1) = ![ClientName]
tmp(2) = ![Phone]
tmp(3) = ![Fax]

GetClientInfo = tmp

..Close
End With

Call CloseConnection

End Function

****

 

[Brendan Reynolds]

A query can't do anything with an array, but you could write a wrapper
function to transform the result of the first function into something that
the query can handle, and call the wrapper function from the query. For
example, you could not call the first function below directly from a query,
but you could call the second one ...

Public Function ReturnArray() As Variant

Dim varResult(2) As Variant

varResult(0) = "zero"
varResult(1) = "one"
varResult(2) = "two"

ReturnArray = varResult

End Function

Public Function ReturnString() As String

ReturnString = Join(ReturnArray(), ", ")

End Function

 

[peregenem]

I had write a function in module to get multi value and used it in Query
unsuccessful. How am I modify to get "Fax" value using function in SQL. I
prefer to use such function to get multi values in stead of modified it to
return only "ONE" value.

*** SQL statement

SELECT tblClientRecord.RecordID, GetClientInfo([RecordID]) AS Fax
FROM tblClientRecord;


***** function
Public getClientInfo(ClientID) As Variant
Dim tmp(1 To 3) As Variant
strSQL = "SELECT UserName, Phone, Fax FROM tblClientRecord WHERE RecordID =
" & ClientID

CurrentDBConnect
OpenRst (strSQL)

With rstObject
tmp(1) = ![ClientName]
tmp(2) = ![Phone]
tmp(3) = ![Fax]

GetClientInfo = tmp

.Close
End With

Call CloseConnection

End Function

****

Here's an alternative approach:

Sub testdominic()

Const CONN_STRING_SHAPE As String = _
"Provider=MSDataShape;Data "

Const CONN_STRING_MDB As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TestShape.mdb;"

' Delete file (if exists)
On Error Resume Next
Kill "C:\TestShape.mdb"
On Error GoTo 0

' Create database file
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
cat.Create CONN_STRING_MDB

' Open SHAPE connection
Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
.CursorLocation = 3
.ConnectionString = _
CONN_STRING_SHAPE & CONN_STRING_MDB
.Open

' Create table
.Execute _
"CREATE TABLE tblClientRecord (" & _
"RecordID INTEGER NOT NULL PRIMARY KEY," & _
"ClientName VARCHAR(20) NOT NULL," & _
"Phone CHAR(15)," & _
"Fax CHAR(15));"

' Create test data
.Execute _
"INSERT INTO tblClientRecord VALUES (" & _
" 1, 'dominic', '0034948785221', '0034948785222');"
.Execute _
"INSERT INTO tblClientRecord VALUES (" & _
" 2, 'BrendanR', '0034955885491', '0034955885492');"
End With

Const SQL As String = _
"SHAPE {" & _
"SELECT RecordID FROM tblClientRecord" & _
"} APPEND ({" & _
"SELECT RecordID, ClientName, Phone, Fax" & _
" FROM tblClientRecord" & _
"} AS chapClientRecords" & _
" RELATE RecordID TO RecordID)"

' Create hierarchical recordset
Dim rs As Object
Set rs = con.Execute(SQL)

' Test: print data
With rs
Dim lRows As Long
For lRows = 0 To rs.RecordCount - 1
Debug.Print rs.Fields(0).Value
Debug.Print rs.Fields(1).Value.GetString
.MoveNext
Next
End With
End Sub