subform with ADO recordsets (or Query)

[Jeff]

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]

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

--
Ken Snell
<MS ACCESS MVP>

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.

 

[Jeff]

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 = qry

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]

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


--
Ken Snell
<MS ACCESS MVP>

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.

 

[Jeff]

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

Set rst3 = New ADODB.Recordse
Set cmd1 = New ADODB.Comman
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT * FROM tblInventoryHist " &
"WHERE (ProductCode = '" & Me!cboProduct & "'" &
" AND Warehouse = '" & Me!cboWarehouse & "')
.CommandType = adCmdTex
End Wit
Set rst3 = cmd1.Execut
Set Me.Form("subInventoryMovementEnquiryDetail").RecordSource = rst

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.

 

[Dirk Goldgar]

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

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 & "')"

 

[Ken Snell]

Sorry - my error. Change your last line of code to this:

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=...do+recordset+form+daigle+snell&ie=UTF-8&hl=en

--
Ken Snell
<MS ACCESS MVP>

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.

 

[Jeff]

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.

 

[Jeff]

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.

 

[Jeff]

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

 

[Ken Snell]

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

--
Ken Snell
<MS ACCESS MVP>

Um, Ken, I think you've gotten confused here. You can't assign a
recordset to the string propertt RecordSource.

< snip >