Subform - Assign Recordset to (DAO)

[Sorrells]

Hello,

This is a fairly lengthy post. My apologies.

The context for my questions is a form that opens with a listbox full of some 60 room/household area names and a subform where a user can place names selected from the listbox in descending order of selection. Names are added to the subform by double-clicking the listbox name or selecting it and then clicking on a "Add Room" command button. The subform's display capacity is 20 selected rooms.
Rooms are added from the parent form. Each time, the underlying table of the subform must have a record appended (this works) and then the subform requeried.
The subform cannot be based directly on a table as the table must filtered. A query currently does this. All of this would be fine except:

When more than 20 records have been added, each additional record causes the subform display to 'jump' slightly and the focus constantly stays on the 1st room entered, thus the user cannot see what they just added without having to scroll down. I cannot figure a way from the parent form to code such that for the additional record over 20 the following occurs:
Records 10-20 are displayed with record 21 at the bottom and current record
And for records 22-30:
The above display is updated by addition of the single new record until the subform again fills
Then finally:
This cycle repeats if needed for each additional set of 10 records.


I've been rooting around forums and Access books looking for information about the above subject but remain unsure of the possibilities. Answers to the following questions might provide significant enlightenment for me, so I ask that knowledgeable folks please respond.

Given an unbound form and subform

1) Can a recordset be assigned to a subform?
2) Can it be assigned in the subform? If so, in what event?
3) More importantly, can it be assigned in the parent form? If so, in what event?
4) What is the syntax for the above?

Given that a recordset CAN be assigned to a subform
1) If the number of records returned is greater than the subform control, how can the display be set to show only the lowest records? For example 50 records are returned for a subform that can display only 30 records. How would you code so that records 20 thru 30 are displayed with empty space at the bottom of the subform?
2) How would you code so that if the number of records returned is greater than the subform control's capacity AND the display is in descending order AND when adding new records you always want to display at least the last 10 records before additional ones fill up the subform control again
3) If a recordset can be assigned (I certainly have not been successful!), would the RecordsetClone and Bookmarks be of use?
4) Adding records 1 through 20 may work very nicely, but the display starts 'jumping' once the subform displayed is filled. Can this be stabilized in VBA code?

 

[Sorrells]

Pat,

Thank you for your response. I am working in Access XP. I've read your response several times and looked at my form/subform. I've tried a variety of approaches but none very successful. At this point I am attempting to assign a recordset to the subform in the OnOpen event. The main form is bound to a lookup table but the subform is unbound. In the OnOpen event of the subform I have attempted the following:

1) Create a recordset from the table [Room_Ordered] for the current client
2) Assign recordset to form RecordSource
3) Assign field to the form controls ControlSource
4) Requery the subform

===================================
Private Sub Form_Open(Cancel As Integer)
Debug.Print
Set dbs = CurrentDb
Set rstSubform = dbs.OpenRecordset("SELECT Room_Ordered.House_Room_ID," & _
"Room_Ordered.Client_ID, Room_Ordered.Room_ID, " & _
"Room_Ordered.orderfield, Room_Ordered.Room_Custom, " & _
"Room_Ordered.Room_Custom_SP, Room_Ordered.Idiosyncrasy " & _
"FROM Room_Ordered " & _
"WHERE Room_Ordered.Client_ID = " & gblClient_ID & _
" ORDER BY Room_Ordered.orderfield")

rstSubform.MoveFirst
rstSubform.MoveLast
Me.RecordSource = "rstsubform" '<- ERROR HERE

txtRoom_Custom] = [Room_Ordered].Room_Custom
txtHouseRoomID] = [Room_Ordered].House_Room_ID
[txtRoomOrder] = [Room_Ordered].orderfield
[txtClient_ID] = [Room_Ordered].Client_ID
[txtClient_ID] = [Room_Ordered].Client_ID
[txtRoom_Custom_SP] = [Room_Ordered].Room_Custom_SP
[Idiosyncrasy] = [Room_Ordered].Idiosyncrasy
Me.Requery
====================================

I am getting an error on assigning the recordset:
Me.RecordSource = "rstsubform"
as follows: 2580 RecordSource 'rstSubform' specified on this form or report does not exist."

The recordset works, the MoveFirst - MoveLast then recordcount in the debugger returned a value of 5 which agrees with the table.

It is this RecordSource assignment that has given me such a hard time.

Aside from this error, does the logic look reasonable?

Again, thanks for your help!

 

[Sorrells]

I should add this point of context, as I could be way out in left field as to what I need.

My only reason for wanting to create a recordset in the subform is a belief that I could then control the display of records. Before I began fiddling with the form, it worked fine except that the current record was constantly the first one. Then my client wanted it to be the last one. Then came the concern when records in the subform were larger than what the control could display in the parent form.

So the ultimate objective of all this work is to obtain a way to determine what record is current, change it if necessary, and if possible, determine what records would be displayed given a specific number of records.

I would hate to divide up the subform's display in 3 sections as the user is adding rooms to a house and might want to scroll backwards at any time. But with each new room added, I need to show the 10 previous rooms selected to provide context for the user.

Are there other approaches to determining the subform display outside of the RecordSet Method & RecordSetClone property?

 

[Lynn_AccessUser]

If you are connecting to a SQL backend then you need

Me.RecordSource = "dbo.rstsubform"

 

[Sorrells]

Hi! The SQL is to a linked table. I do not understand your syntax but XP is not recognizing it.

I would have put this in a list box except for the fact that I must modify and delete these records.

Most of this problem would go away if I could just manipulate records in the subform as it is currently configured. It is:

1) bound to a query
2) contains all fields of the query referenced table

I need in both the parent and sub forms to:
- determine the number of records returned
- identify the current record
- be able to set the current record anywhere I choose
- at times force the focus onto the Record Selector
- at times force the focus into the text of a record
- set the display to a specific records after a requery
I have tried the /decompile option but am not sure it works, there was no reduction in filesize.

I copied objects into a new database. This when compiled changed the db size from 53MB to 14MB. However the Access 2580 error noted earlier with the code remains. If this error makes sense to anyone, please let me know. I do not see how I can move from the first record to the last, get a record count of the recordset then be told that it does not exist! [The recordsource 'rstSubforms' specified on this form or report does not exist.]. Again the code line getting the error in the subform's OnOpen event is:
Me.RecordSource = "dbs.rstSubform"
The error is the same, with or without the 'dbs'.

 

[Lynn_AccessUser]

not sure if this was a typo in your post

Me.RecordSource = "dbs.rstSubform"

should be Me.RecordSource = "dbo.rstSubform"

I am not sure if this will solve your problem. I suggested it because I sometimes have issues with certain users being able to access the sql server and the dbo qualifier seems to resolve it.

The error msg you get is the same msg I get if I forget to use the qualifier.

 

[Sorrells]

Lynn,

I do not know what "dbo" is but it also returns an error 2467 The expression you entered refers to an object that is closed or doesn't exist.

Does this "dbo" need to be declared and set? What is it for? XP VB Help is telling me nothing.

I appreciate your help!

 

[Lynn_AccessUser]

dbo is the database owner.

Are you still creating the select statement in code or did you create a view or stored procedure for the record source.

 

[Sorrells]

Lynn,

I go back and forth, right now the subform is tied to a query. The program is for a single user. There is a 'Backend' with the tables in the same folder as the program dB.

 

[Lynn_AccessUser]

Sorry I am at a lost at this point . . .

 

[Mile-O]

You are attempting to assign an open recordset object to the form's recordsource. I don't know if you can do this.

Just clarifying that you can't.

 

[Mile-O]

Ah! Being fresh to ADO I would never have guessed. Still a DAO guy at heart. Still wish I had A97, too.

 

[Sorrells]

Pat,

I don't want to use a recordset, I just don't see how I can manipulate the subform records without one. I want capability from the Parent and sub form that comes with a recordset (e.g. MoveFirst, Move -2, FindFirst).

If there is a way to do this without a recordset, I am delighted! But I cannot figure out how.

At this time (attempt #nnn to the nth) I found that I cannot link the parent and child forms but I do have them both bound. However, I do not see the leverage obtained thereof.

I am not ADO proficient.

 

[Sorrells]

Pat, I certainly appreciate your questions and interest. I have read over your comments several times and performed the necessary amount of head scratching. The result has been most positive!

First, I succeeded in binding both the parent and sub forms to queries. Secondly, I was successful in using the RecordsetClone to move among the subform records using the Move, No Match and Bookmark properties. The forms perform well at this point.

In fact the only work remaining is adjusting the display in the subform, should the number of records exceed the subform control in the parent form. This is where I hoped the Move properties of the RecordSetClone would be of use but I see that they all affect the current record.

Your comment " If you want to limit the number of records shown in the subform to groups of 10, you can add buttons to the main form to "get the next group" or "get the previous group". These buttons would change the recordsource of the subform and then requery it to get the appropriate set of records.", indicates that I can constrain the display to subset of records. How would I do this in code?

The form enables the user to add rooms at will. Once the display limit of 22 records is reached, new records added are hidden from view. My client insists that the records be entered in ascending order. I need a method to display the bottom records. Your subgroups may be the ticket.

I am including the code for the subform's OnOpen and OnCurrent event for review. Perhaps it can be useful for others. I have also attached a JPG of the form for context.

========== OnOpen Event of Subform =========

On Error GoTo Err_Form_Open

'  OPENING THE ENTER ROOMS SUBFORM
'  1) Check for existing rooms, exit sub if not found
'  2) If global Selected Room then look for match via
'        RecordsetClone
'  3) If there is no match, then select 1st record
'  4) Close the recordset
'

Answer = DLookup("[House_Room_ID]", "[Room_Ordered]", "[Client_ID] = " & gblClient_ID)
If IsNull(Answer) Then
    Exit Sub
End If
   
Set rstSubform_Clone = Me.RecordsetClone
If gblSelected_Room <> "" Then
   rstSubform_Clone.FindFirst "[Room_Custom] = """ & gblSelected_Room & """"
   If rstSubform_Clone.NoMatch Then
      MsgBox ("Subform OnOpen no room found")
      Resume Exit_Form_Open
   End If
   Me.Bookmark = rstSubform_Clone.Bookmark
Else
   rstSubform_Clone.MoveFirst
   Me.Bookmark = rstSubform_Clone.Bookmark
End If

Exit_Form_Open:
    Set rstSubform_Clone = Me.RecordsetClone
    rstSubform_Clone.Close
    Set rstSubform_Clone = Nothing
    Exit Sub

Err_Form_Open:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Form_Open

Debug.Print
End Sub

========= OnCurrent Event of Subform ==========
Private Sub Form_Current()
On Error GoTo Err_Form_Current
' UPDATE DEBUGGER CONTROLS ON MAIN FORM
'  Interestingly, due to the Bookmark command below, the OnCurrent
'    is executed immediately a second time.
'
Set rstSubform_Clone = Me.RecordsetClone
If Not IsNull([txtHouseRoomID]) Then
   gblHouse_Room_ID = txtHouseRoomID
   gblSelected_Room = txtRoom_Custom
Else          'reached bottom of list
     Answer = DLookup("[House_Room_ID]", "[Room_Ordered]", "[Client_ID] = " & gblClient_ID)
     If Not IsNull(Answer) Then
        rstSubform_Clone.MoveFirst
        Me.Bookmark = rstSubform_Clone.Bookmark
     Else  'no more rooms left
        GoTo Exit_Form_Current
     End If
End If

Exit_Form_Current:
    rstSubform_Clone.Close
    Set rstSubform_Clone = Nothing
    Exit Sub

Err_Form_Current:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Form_Current

End Sub

 

[afromanam]

1) Can a recordset be assigned to a subform? 2) Can it be assigned in the subform? If so, in what event? 3) More importantly, can it be assigned in the parent form? If so, in what event? 4) What is the syntax for the above? YES YES YES YES http://www.ozgrid.com/forum/showthread.php?t=48566