subform with ADO recordsets (or Query)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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>

Jeff said:
Can anyone help me with this, please !
I have a subform which is unbound to any query. In my code, I have a
Click to expand...
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.
 
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
 
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>

Jeff said:
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
Click to expand...
subform controls? But how to reference? It is unlike list box/combo box
using Index and Row.
 
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.
 
Ken Snell said:
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
Click to expand...

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

Jeff said:
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
Click to expand...
property or method'. Actually I have been trying for many methods such as
RowSource but still cannot find the right one.
 
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.
 
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.
 
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>

Dirk Goldgar said:
Um, Ken, I think you've gotten confused here. You can't assign a
recordset to the string propertt RecordSource.
Click to expand...
< snip >
 
Back
Top