Assign a recordset to a continuous form.

[Meltdown]

Hi all,

Can anyone tell me how to assign a recordset as a data source for a continuous form?

I'm using Set Forms("frm_OrdersSummary").Recordset = rstRecords, the screen flashes but then the data disappears from the screen for some reason.

Regards
Melt

Dim dbs As DAO.Database
Dim rstRecords As DAO.Recordset

   Set dbs = CurrentDb

   Set rstRecords = dbs.OpenRecordset("qrySummaryOrders")


   If rstRecords.EOF Then
      'no records so set the recordsource to return a no results row, fixes a nasty access bug that blanks unbound combo   boxes on a form, when there is no data returned
      
Me.RecordSource = "qryNoResults"
      
      'show the No Results textbox
      Me!txtNoResults.Visible = True

        Me.Refresh

   Else

                Set Forms("frm_OrdersSummary").Recordset = rstRecords
                 
                 'turn off No Results textbox
                 Me!txtNoResults.Visible = False
                
                 Me.Refresh
                          

   End If

   rstRecords.Close
   dbs.Close

   Set rstRecords = Nothing
   Set dbs = Nothing

 

[Pat Hartman]

Do you really want to work with an unbound form or are you just trying to change the form's RecordSource on the fly? To change a recordsource, use:

Me.RecordSource = "qrySummaryOrders"

You can do this in the click event of a button. When I have search forms, I create them as bound forms and once the form is working, I delete the RecordSource. Then when the form opens up, it is unbound. The user enters his selection criteria and then, I either choose which saved query to make the RecordSource or I use an SQL string that I built based on their selections.

 

[Meltdown]

Hi Pat,

I do want to change the forms recordsource, but I need to check if records are being returned. If there are none I run a qryNoResults query instead.

I thought that since I've opened a recordset to check for records, I should then be able to apply it to the form and save the overhead of setting the recordsource and re-running the query to populate the form.

Thanks for the reply.
Melt

 

[jal]

Since you are binding the recordset to the form, it's probably not a good idea to close the recordset. (I could be wrong, as I have never tried it).

 

[Meltdown]

Hi jal,

Thanks for the reply, made some progress with your reply.

If I comment out:
'rstRecords.Close
'dbs.Close

'Set rstRecords = Nothing
' Set dbs = Nothing

...then my recordset is applied to the form and the data displays, however is this not going to cause memory leak problems if I never close the recordset and database objects?

Regards
Melt

 

[boblarson]

When you close the form it will destroy the recordset.

 

[Meltdown]

Hi Bob, thanks for the reply.

The problem with that is, this form stays open all day and user are constantly filtering for data using a number of unbound combo boxes at the top of the form.

Regards
Melt

 

[Banana]

I don't know if this will fix the flashing problem, as I've not seen that one before, but should point out that this line:

Set rstRecords = dbs.OpenRecordset("qrySummaryOrders")

Can be made to be a bit more explicit:

Set rstRecords = dbs.OpenRecordset("qrySummaryOrders", dbOpenDynaSet)

As we only want to deal with Dynaset recordsets for binding with form whenever we need the ability to edit/add new records. The default is actually dbOpenTable (well, it's not always that straightforward as dynaset may be defaulted, but to me that's precisely the problem-we can't be too sure if it's the type we want unless we're explicit) which is not always the appropriate type.

HTH.

EDIT: Forgot to mention that if your qtrNoResults is basically a placeholder for indicating there's no records and isn't supposed to be edited, you can enforce read-only by opening it as snapshot-type recordset.