How to attach a record set to a subform recordsource?

[Jawad77]

I have a subform control in my main form. The source object is assigned to this subform control on the fly. The source object is a separate form in my database. I like to assign a record set to the record source property of my separate form.

I used this command"

Forms![ISubForm].RecordSource = "Objrst"

Where ISubForm is the separate form mentioned above and Objrst is a recordset object that I create on the fly using SubProcedure.

The above syntax doesn't seem to work. Could you please tell me how to fix this. I am pretty new at this whole VBA/Access thing. Thanks

Jay

 

[Zion7]

Is it a recordset, or the recordsource, that you want to manipulate?

The recordsourece, is either the name of a table, or a SQL statement...

Me.Recordsource = "tblCountry"
Forms!frmCountry.Recordsource = "SELECT * FROM tblCountry WHERE txtCountry LIKE 'A*'"

If it's a recordset, you must create the recordset...

Dim rec as New ADODB.Recordset
rec.open....

Then

Set Forms!frmCountry.Recordset = rec

Hope this helps, good luck!

 

[faeryfyrre]

In the line of code you used, because you encased Objrst in quotes, Access thinks you are providing either:
1. The name of a table or saved query
2. An SQL Statement.

To achieve what you want to do, remove the quotes so that Access knows you are using an object variable, plus you need to assign to a different form property

IE

Forms![ISubForm].Form.RecordSet = Objrst

however, you may run into another problem here as Access 97 does not allow you to assign a recordset as the recordsource of a form.

Also, the recordset you wish to use needs to be an ADODB recordset

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[faeryfyrre]

Oops,

As it is an object you are assigning you should use the SET keyword

SET Forms![ISubForm].Form.RecordSet = Objrst

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Jawad77]

It's still not working for me.

Here is the problem I am running into. I have a main form
called

"MainForm" ... From the main form I can navigate to another form called "IOS Form". The IOS Form has a tab control in the form header and a subform called "SubForm" in the detail section. The tab control page creates a graph for me. The subform is supposed to display the data from the table that's attached to the graph's record source.

When I click on the tab control page, the following code should run:

Forms![IOSForm]![SubForm].RecordSource = "InboundGraphTable"

so that I could see the data values in the subform that are plotting my graph in the tab control page.

For some reason, it's not attaching the "InboundGraphTable" to the recordsource of the SubForm.

When I navigate back to the MainForm through the command button on IOS Form, i like to change the record source of my SubForm to Null or some dummy table. When I click on the command button, I get this erorr:

"MS Access can't find the form "IOSForm" referenced in the VBA code.

I would really appreciate if you could tell me how to fix this thing.

Thanks

Jay

 

[Jawad77]

Opps I am using

Forms![IOSForm]![SubForm].Form.RecordSource = "InboundGraphTable

but still doesn't work

 

[Zion7]

Jawad, it appears you don't want to bind your form to a recordset, but bind its recordsource.
It appears you have the syntax correct but, according to your last post, you are missing final quotes, at the end of the table name. I think this is just a typo.

When you navigate back to Main Form, do you close IOSForm, BEFORE you change the recordsource to a default value?
Forms![IOSForm]![SubForm].Form.RecordSource = ""

As far as the recordsource not adjusting accordingly, are your controls bound also?
The controls on your subform, must have their control source bound to the fields of the recordsource also.

Maybe your doing this already? If so, i'm not sure what else it could be offhand?

Good luck either way.

 

[Jawad77]

Thanks a lot Zion7. It works now.

Do you know how to check in VBA if my table has null records in it.

I tried to use this expression ,,, Didn't work

If IsNull("InboundGraphTable") Then

MsgBox " Message here"

Exit Sub

End If

Thanks a bunch once again!

 

[Zion7]

you can only check, specific fields.

If IsNull(txtCountry)....

or open the recordset, and loop thru the fields collection

rec.open "InboundGraphTable", currentproject.conn.....

Do Until rec.Eof
For x = 0 To rec.Fields.count
If isNull(rec(x)) Then...
Next x
rec.MoveNext
Loop