Populate RowSource with ADO RecordSet?

[Tekime]

Greets,

Is it possible for me to assign an ADO recordset to a combobox rowsource? I'm trying to populate a combobox ina subform with transactions based on credit card number input.

Here is the code I'm using in an onEnter event of the search submit form:

Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset

Dim sqlCbTrans As String

sqlCbTrans = "SELECT [tbl_Transactions].[trns_date], [tbl_Transactions].[trns_amount], [tbl_Transactions].[transactionId], [tbl_Orders].[ord_ccNumber] " & _
"FROM [tbl_Orders] INNER JOIN [tbl_Transactions] ON [tbl_Orders].[orderId] = [tbl_Transactions].[trns_orderId] " & _
"WHERE ([tbl_Orders].[ord_ccNumber]) LIKE '" & Forms![frm_Chargebacks_De]![ccNumber] & "*' "

Set rs = db.OpenRecordset(sqlCbTrans)
If (rs.EOF) Then
MsgBox ("No records found&quot
Else
Forms!frm_Chargebacks_De!frm_Chargebacks_Transactions_Sub.Form.cbTrns_transactionId.RowSource = rs
End If

Instead of populating the combobox I'm getting a type mismatch error. Am I doing this incorrectly or is this a problem with something else?

Many thanks!

http://www.fury-tech.com

 

[cmmrfrds]

1. The code you have set up is not ADO.
2. Valid for a combobox are a "value list", table name, or sql statement.
3. the = sign is used to equate variables not objects. The Set statement is used for objects.
i.e. Set Me.Recordset = rs
4. easiest to just make the row source of the combobox an sql statement.

 

[Tekime]

Thanks cmmrfrds. The ADO part was a typo, sorry. I meant DAO.

I know I can set the rowsource as an SQL statement, but I want to test the results of the SQL statement before trying to populate the combobox. I.E. if the statement returns no records then deactivate the combobox and give a message.

I haven't been able to find a way to accomplish this by assigning an SQL statement directly.

I can use the SQL statement I have ("sqlCbTrns&quot to get a recordset using DAO, test that, and if it contains records then assign the SQL statement to the combobox rowsource. But unless I'm mistaken this would result in having two queries performed, and I'm looking at anywhere from 100-500,000 plus records, so every query matters.

Any suggestions on handling this then, if it can't be done with DAO?

Thanks

http://www.fury-tech.com

 

[cmmrfrds]

As long as the criteria fields are indexed, it probably is not very expensive in resources to have the first query check the criteria, provided the criteria can be in the where clause and you can avoid wildcard characters.

something like.
select count(*) as cnt from tab where citeria1 = var1 and criteria2 = var2 etc....

if cnt > 0 then
something
end if

 

[Tekime]

Good thought, retrieving the # of records hadn't occurred to me. I can see this reducing load to a point, but isn't the most 'cumbersome' part of a query the criteria/clause? I do have all of my criteria fields indexed, so this should help though.

Do you know of any good methods/apps for monitoring execution time of queries in Access? I can probably do this in the form module, I'll just have to read up on timing in VBA.

Also, is there any way I could construct an appropriate variable from the DAO recordset which could be assigned to the rowsource? I think I would rather waste a few clock cycles on the client computer than tie up the back-end with queries that are basically identical.

Thanks again for the help, any other suggestions on improving or bettering this process are welcome

http://www.fury-tech.com

 

[cmmrfrds]

One of the row source types is a list. The problem with a list it is limited, I believe, to 2048 bytes. You can use a call back function to load the list from an array. Basically, load you recordset into an array and use the array for the list.

In the Row Source Type put UserGroupList or the name of your choosing which is the name of the Function.

Example of loading login user to a listbox with a callback function. Notice the name of the function equals name I indicated above. The initialize part of the callback function is where you build the array.


Function UserGroupList(fld As Control, ID As Variant, _
rowX As Variant, col As Variant, _
code As Variant) As Variant
Dim ur As User, gp As Group
Static myarray() As Variant
Static row As Integer, rowcount As Integer
Dim cg As New ADOX.Catalog

Dim ReturnVal As Variant
ReturnVal = Null

Select Case code
Case acLBInitialize ' Initialize.
Set cg.ActiveConnection = CurrentProject.Connection
rowcount = cg.Users.Count
row = 0
ReDim Preserve myarray(rowcount, 2)
For Each ur In cg.Users
myarray(row, 0) = ur.name
For Each gp In ur.Groups
myarray(row, 1) = gp.name
Next
row = row + 1
If row = rowcount Then Exit For
Next
ReturnVal = rowcount

Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = rowcount
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 2
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1

Case acLBGetValue ' Get data.
'-- zero based array
'Select Case col
' Case 0
' ReturnVal = myarray(rowX, 0)
' Case 1
' ReturnVal = myarray(rowX, 1)
'End Select
ReturnVal = myarray(rowX, col)
Debug.Print "column = "; col

Case acLBEnd ' End.
Erase myarray
End Select
'''''Debug.Print "return value = "; ReturnVal
UserGroupList = ReturnVal
End Function