subform with ADO recordsets (or Query) (Microsoft Access)

subform with ADO recordsets (or Query)
message from =?Utf-8?B?SmVmZg==?= on 30 May 2004
Can anyone help me with this, please ! I have a subform which is unbound to any query. In my code, I have a query called qry1. I would like to assign this qry1 to the Record Source of the subform. This qry1 is based on other control values on the main form. I have tried this for hours but still do not get it right. Thanks.

Ken Snell replied to =?Utf-8?B?SmVmZg==?= on 30 May 2004

How are creating/obtaining qry1? Are you opening it as a recordset? Is it
stored in the database? More information, please.

=?Utf-8?B?SmVmZg==?= replied to Ken Snell on 30 May 2004

The information (data) is from a table. Here is my code.

qry1 = "SELECT * FROM tblInventoryHist " & _
"WHERE (ProductCode = '" & Me!cboProduct & "'" & _
" AND Warehouse = '" & Me!cboWarehouse & "')"

Set Me.Form("subInventoryMovementEnquiryDetail").RecordSource = qry1

BTW, If I used rst1.Open to open the recordset, can I move the data to the subform controls? But how to reference? It is unlike list box/combo box using Index and Row.

Ken Snell replied to =?Utf-8?B?SmVmZg==?= on 30 May 2004

You must open the query as a recordset and then you can set it to the
RecordSource of the form.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qry1 As String
Set dbs = CurrentDb
qry1 = "SELECT * FROM tblInventoryHist " & _
"WHERE (ProductCode = '" & Me!cboProduct & "'" & _
" AND Warehouse = '" & Me!cboWarehouse & "')"
Set rst = dbs.OpenRecordset(qry1, dbOpenDynaset)
Set Me.Form("subInventoryMovementEnquiryDetail").RecordSource = rst

=?Utf-8?B?SmVmZg==?= replied to Ken Snell on 30 May 2004

Hi Ken, since I am using ADO, I modified and here is my code.

Set rst3 = New ADODB.Recordset
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = "SELECT * FROM tblInventoryHist " & _
"WHERE (ProductCode = '" & Me!cboProduct & "'" & _
" AND Warehouse = '" & Me!cboWarehouse & "')"
.CommandType = adCmdText
End With
Set rst3 = cmd1.Execute
Set Me.Form("subInventoryMovementEnquiryDetail").RecordSource = rst3

I got a run-time error '438' code saying that 'Object doesn't support this property or method'. Actually I have been trying for many methods such as RowSource but still cannot find the right one.

Ken Snell replied to =?Utf-8?B?SmVmZg==?= on 31 May 2004

Set Me.Form("subInventoryMovementEnquiryDetail").RecordSet = rst3

I have a vague recollection in my back memory that you may not be able to
use an ADO recordset as a form's recordset, so if this errors as well, you
may need to use DAO instead..... here is a earlier newsgroup thread re: this
issue:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23ko%234GriDHA.1636%40tk2msftngp13.phx.gbl&rnum=1&prev=/groups%3Fq%3Dmicrosoft%2Baccess%2Bado%2Brecordset%2Bform%2Bdaigle%2Bsnell%26ie%3DUTF-8%26hl%3Den

Dirk Goldgar replied to Ken Snell on 31 May 2004

"Ken Snell" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:eY65EtrREHA.1388@TK2MSFTNGP09.phx.gbl

Um, Ken, I think you've gotten confused here. You can't assign a
recordset to the string propertt RecordSource. If this code is running
on the parent form to the subform named
"subInventoryMovementEnquiryDetail", then I'd guess one could write:

Me!subInventoryMovementEnquiryDetail.Form.RecordSource = _
"SELECT * FROM tblInventoryHist " & _
"WHERE (ProductCode = '" & Me!cboProduct & "'" & _
" AND Warehouse = '" & Me!cboWarehouse & "')"

=?Utf-8?B?SmVmZg==?= replied to Dirk Goldgar on 30 May 2004

Hi Dirk, you are right.
main form = "frmInventoryMovementEnquiry"
subform = "subInventoryMovementEnquiryDetail"
In the main form there are 2 controls cboProduct and cboWarehouse. I ran my original code and used
debug.print rst3.getstring to print the resulted recordsets. The result is expected. That means my SQL is working property.
Now I am confusing after trying your suggestion. I got a compile error "Invalid use of property" and got "Me!cboWarehouse" hightlighted.

=?Utf-8?B?SmVmZg==?= replied to =?Utf-8?B?SmVmZg==?= on 30 May 2004

Hi Dirk, I am sorry for my previous message. I just typed what you had told me. Now I can get the recordset attached to the subform. At the subform Navigation Buttons, I can see 5 which is the correct number of records.

But the subform does not display the records. I have double checked that the subform control name is the same as the tblInventoryHist field names. Appreciate your further advise.

=?Utf-8?B?SmVmZg==?= replied to =?Utf-8?B?SmVmZg==?= on 30 May 2004

Hi Ken and Dirk, thanks for your helpful suggestion. I got the problem fixed. Thanks again.

Ken Snell replied to Dirk Goldgar on 31 May 2004

Yep, saw my error just before you posted the fix! Thanks, Dirk. It was time
to go to sleep....that's my excuse, anyway!

Archived message: subform with ADO recordsets (or Query) (MS Access Database)