Get split form behaviours without using a split form

[SyntaxSocialist]

Hey all! Since I last posted I've picked up some basic SQL and VBA and feel pretty confident with it on the level of an intermediate beginner, at least when it comes to VBA for Access. The issue I've run into now, though, is that I want to create a form (frmEdit) with several elements. The primary purpose of this form will be to display existing records from tblMain (the only table in the database) in order that they can be edited. Here's how I want it to work.

1. The form will have three "sections": a search area, a subform (subMain) displaying those search results, and a number of controls bound to tblMain that display the contents (ProjectID, FirstName, LastName, StartDate, EndDate, etc.) of the active record. The user can then edit either through subMain or through the bound controls.

2. The user "filters" records through a series of controls (combo boxes, text boxes, check boxes, and option groups) in the search section. All the records that meet the user-provided criteria then appear in subMain (this has already been accomplished). I've set up 4 buttons (First, Previous, Next, Last) that the user can use to cycle through the records that are left after filtering.



3. Now for the part where I'm having difficulties: When the user clicks a cell on subMain (say, the "FirstName" cell for John McFie's record), I want the record that that field is a part of (John McFie's record) to become the record that is showing in the bound controls. Similarly, as the user cycles through the records in the bound controls using the 4 buttons described above, I want the subform to highlight the record that is showing in the bound controls, or at least the focused field in those bound controls (i.e.: when ProjectID has the focus, I would like the corresponding ProjectID to be highlighted on subMain).

So basically, I'm asking how to make my form and subform behave like a split form--to fully sunchronize the two of them. The reason I don't want to use a split form is because I don't freaking understand them =P That said, if someone can provide me with an assurance that a split form would provide the functionality I need, then I would consider using one. But they're not as customizable and I've had a lot of trouble convincing them to do what I want them to.

[June7]

I also found the split form lacking when I first encountered it. I have yet to implement it in any db. However, I have seen other db's that use it successfully.

I explored the split form and also something like what you want and rejected both. What I have now is a form/subform arrangement where no data is accomplished through either. The main form has unbound controls for entering search criteria then I use VBA code to either filter the subform or to go to specific record of subform, depending on which controls user enters criteria into and which buttons are clicked. The subform is in Datasheet View. Also, buttons on the main form will initiate tasks (edit, print, view) for selected subform record or the filtered recordset. Clicking on specific record of subform will open another form for viewing (not editing) details of record.

What you want will require form and subform to not be linked. Code will either set Filter and FilterOn properties or go to specific record.

Controls used to enter filter criteria cannot be bound to fields.

[SyntaxSocialist]

June,

That actually sounds a fair bit like what I've got set up right now. I don't care for it. I think I'm going to have to bite the bullet and deal with the limitations of splitforms in exchange for their capacity to accomplish what I'm trying to. If it were just for me, this wouldn't be an issue, but I'm designing frmEdit for a group that doesn't know much about the ins and outs of access. An interface with little to no guesswork is paramount here.

Thanks for your help!