Recordset as Form Source 1

[rgbanse]

I have created a multi-record recordset using vba. How can I use it as a source in a 'Continuous Forms' form to show all records.
thx
RGB

 

[RoyVidar]

Hi!

You should be able to do that by assigning the recordset to the forms recordset.

[tt]set me.recordset=rs[/tt]

Note - if you're using ADO, you might like to have a look here How to bind Microsoft Access forms to ADO recordsets

Roy-Vidar

 

[nimarii]

Hello,

I'm trying to populate a subform based on a recordset, and have used the code above, but the subform isn't populating. I've tried bounding the controls on the form, and unbounding them, and when bounded, #Name? is what shows up in the field.

here's the code that I'm using the main form's OnCurrent event:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
If Me.GlobalCircuitID.Value <> Null Then
    strSQL = "SELECT * From BillStreamUpload WHERE GlobalCircuitID = '" & Forms!provision.GlobalCircuitID.Value & "'"
    MsgBox strSQL
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    Forms!provision.BillStreamTab.Form.Recordset = rs
End If

Does anyone know what I'm doing wrong?

 

[RoyVidar]

I'm not very fluent in DAO, but try:

[tt]Set rs = db.OpenRecordset(strSQL,dbOpenDynaset)[/tt]

- that is of course, based on you having the correct subform control name (i e the reference to the subform)

Roy-Vidar

 

[nimarii]

Thanks RoyVidar. It turns out that i had forgotten to use the word "set" when defining the data source!

 

[nimarii]

sorry, but i have one more question. The subform is only being populated by one record within the recordset - how can i get all the records to appear?

 

[RoyVidar]

How many records satisy the criterion GlobalCircuitID = '" & Forms!provision.GlobalCircuitID.Value & "'"?

hmpf - not seeing the missing set statement

Roy-Vidar

 

[PHV]

Replace this:
If Me.GlobalCircuitID.Value <> Null Then
By this:
If Not IsNull(Me.GlobalCircuitID.Value) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nimarii]

RoyVidar,

There's 2 records that match the criteria, and the subform allows for up to 10 records. All ten are being populated by the first record...

Thanks PHV - the form is still populating all available fields with the first record though...

 

[RoyVidar]

Do you populate the controls manually, or are their controlsources set to the fields from the query. This sounds like you're populating them thru code.

Roy-Vidar

 

[nimarii]

ahh, the fields on the subform are just set to the fields from the query...

 

[nimarii]

ok, i figured out, here's the final code i used:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i, rsCount As Integer
Set db = CurrentDb()
'populate the BillStreamTab with a recordset
If Not IsNull(Me.GlobalCircuitID.Value) Then
    strSQL = "SELECT * From BillStreamUpload WHERE GlobalCircuitID = '" & Nz(Forms!provision.GlobalCircuitID.Value, "") & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    rs.MoveLast
    rsCount = rs.RecordCount
    rs.MoveFirst
    Set Forms!provision.BillStreamTab.Form.Recordset = rs
    For i = 1 To rsCount
        Forms!provision.BillStreamTab.Form!("Description" & i) = rs("description")
        Forms!provision.BillStreamTab.Form!("Type" & i) = rs("ROTflag")
        Forms!provision.BillStreamTab.Form!("CDflag" & i) = rs("CDflag")
        Forms!provision.BillStreamTab.Form!("Amount" & i) = rs("amount")
        rs.MoveNext
    Next
End If

thanks for helping me!