mainform "findfirst" causes Subform to jump to top record

[casey]

Hello all,

Is there an easy way to keep a Subform record stationary while using the RecordSet.FindFirst method on the MainForm? Using FindFirst on the MainForm causes my subform to "jump" to the top record. The user selects a SubForm record which Finds that record on the MainForm (both having the same RecordSource). Preferably the SubForm would remain unchanged after locating a MainForm record.

Perhaps I should use a Bookmark or RecordSet.Clone???? Can someone point me in the right direction?

 

[jjturner]

Someone will surely have a better response than me, but since no one seems to be around now, I figure I'd offer my 2 cents:

If your subform is using the same recordset as the main form, couldn't it be a separate synchronized form? I'm trying to think why you need the same recordset for both the main form and the sub form (brain cramp).

John

 

[casey]

Thanks for your reply. Good point.

A parent/child relationship could be set up between the forms. That would make things much easier. For that to take place, I would need to set up the relationship in reverse of what "normally" would be done. (i.e. where the parent would be the Subform and the child would be the MainForm). I couldn't figure out how to do that though.

Also, in such a case, both recordsources would need to have the same unique identifier (Atleast, the only way I know how to set it up). That's what I meant by the same recordsource. Maybe I didn't make myself clear enough or perhaps you know of a better way?

Really, the subform acts like a "glorified" listbox, as I wanted to utilize the Subform's ability to "conditional format" its data and "column size" the columns. Things a listbox can't do. Otherwise, a listbox would have been the way to go.

Here's what happens...
The user selects a record in the Subform which sets the MainForm to that record. Records can't be edited in the subform, but there's an Edit tab on the MainForm meant specifically for that purpose. If the user wants to edit a record, they must go to the MainForm's Edit tab to do so. This is done to prevent records from being changed unintentionally and keeps things tidy.

Does this make any sense? Please let me know if you think something could be done in a more conventional manner. As I tend to do things in an unorthodox way much-of-the-time.

 

[jjturner]

Unbound Main Form?

Not knowing how extensive your control setup is on your main form, you may want to consider leaving all those controls Unbound. Especially since the main form's purpose seems to be for editing.

This will alleviate your problems with the cursor when transitioning between the subform/mainform recordsets.

You're right about unorthodox - normally the unique identifier and perhaps 1 or 2 other columns would be sufficient for 'Lookup' purposes, which would be amenable to a ComboBox control, or simple ListBox.

However, you're obviously intent on providing a very robust Lookup interface for the user, so I certainly won't dissuade you from that.

By contrast, I actually abhor subforms to the extent that I'll simulate them with ListBoxes wherever possible. I've found that short of having 'adjustable' columns and conditionally formatted text, they're extremely adaptable and intuitive for the user.

But enough of my rambling...

HTH,
John

 

[casey]

John,

Correction:The mainform/subform recordsources are not the same, they are similar. Sorry.

Thanks for your advice. You're right. A listbox would be ideal, however, my client requested a display where records w/ a (-) quantity to be red and (+) quantities be shown in blue. I couldn't devise a way to do this in a listbox. Hence, the subform.

Personally, I'm a subform "junkie", "hooked" on filtering, conditional formatting, row/column hiding, and resizing features. My clients have grown to appreciate these features as well.

Thanks for your input.

 

[jjturner]

casey, not sure if you're still pursuing a remedy...

Even though your RecordSources between main and sub vary, from what you've explained, a user choice from the sub can be cross-referenced to a record on the main form, so you essentially have a one-to-one relationship?

Again, I would suggest setting your main form with NO RecordSource, then programmatically populate the Unbound controls based on the appropriate recordset values, cross-referenced from the user's choice on the sub form.

However, this will be an involved solution if you are also providing navigation control on the main form, but I don't really see any other way of escaping the sub form record 'jump' you describe. (I believe this 're-set' behavior is built-in to Access, along with a host of other routines run 'behind-the-scenes' when changing the Current record on the main form.)

This solution will also be problematic if your Main form RecordSource returns more records than your Sub form.

This pretty much exhausts my ideas - someone else may have a much simpler solution that would render my suggestion as a lot of hot air

Regards,
John

 

[casey]

I wrote a longer response last-time, but deleted most of it while I thought I was rambling.

Thanks for your guidance, John. You seem to speak my language.

You're right, in that, the subform's "re-set" is a built-in response to the mainform's recordsource requerying. I've got a solution that involves some DAO.Recordset coding which works so I'm going w/ that until something better comes along. Also, I should try and synthesize the Recordsources for my MainForm and Subform, but no anomalies should occur.

The Subform essentially acts as the navigation control. The MainForm needs to be bound to the table while it handles parent/child links for a few other subforms and edits.
I realize that I'm trying to do too much on one form, but I see any problems arising as "neccesary evils" to uncovering the limits to what I can and cannot do in this DBMS.