Subform - Assign Recordset to (DAO) (1 Viewer)

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?

I wouldn't use an unbound form. Unbound forms require an extrordinary amount of coding to simply duplicate what Access does for you automatically in a bound form. I would use a bound form and subform. 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.

To answer your questions,
1) Can a recordset be assigned to a subform?
Yes but only if the parent form is bound.
2) Can it be assigned in the subform? If so, in what event?
If you assign it in the subform, the best event to use is the Open event since it runs before Access attempts to open the subform's recordsource.
3) More importantly, can it be assigned in the parent form? If so, in what event?
It can be assigned in the parent form in any event as long as you requery the subform after you assign the recordsource. The reason for this is the subform is opened and loaded PRIOR to the main form. Therefore, you are actually adding the recordsource AFTER the subform has been opened.
4) What is the syntax for the above?
If you're in the main form -
Me.YourSubform.RecordSource = "some SQL string" or the name of a stored querydef
Me.YourSubform.Requery

If you're in the subform -
Me.RecordSource "some SQL string" or the name of a stored querydef
Me.Requery

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!

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?

If you are connecting to a SQL backend then you need

Me.RecordSource = "dbo.rstsubform"

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

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.

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!

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.

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.

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

Why are you opening a recordset first? You are attempting to assign an open recordset object to the form's recordsource. I don't know if you can do this. I do know that you can do what I told you to do which is to assign an SQL string or the name of a stored querydef.

Me.RecordSource = "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"

Pat Hartman said:

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

Click to expand...

Just clarifying that you can't.

Actually you can. I didn't have time earlier to look it up but I just did. You can assign a recordset object to a form/report's recordsource. See kb article:

281998 - How to Bind Microsoft Access Forms to ADO Recordsets

The poster had the syntax wrong and may also be working in a version of Access that doesn't offer the capability.

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

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.

You can only set the master/child links if BOTH forms are bound. Bind the forms to queries. You can replace the recordsources at runtime and as long as you don't change the field list, they will work properly.

I have no idea where you're going with the moveFirst, MoveNext, business. Why would you not use navigation buttons on the forms? If you don't like the built in ones, you can turn them off and add your own custom buttons. In fact the button wizard will build the custom navigation buttons for you. The code behind these buttons moves the recordset forward or backward under the form. Is that what you're trying to figure out how to do?

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

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