Main form won't go to "after last" record. 1

[4N6Mstr]

Hello, all.

A long story short, to try to keep it making sense.

I have a situation in a form that I could not find a solution (or actually understand why is not working).

Due to reasons that would take too long to explain, I have a kind of different Main Form – Subforms structure (one of the main reasons actually is that continuous forms can’t have Subforms). I show it “graphically” below, in hope that it will make it easier to visualize:

Main Form
---------------------------------------------------------
| |
| Subform A Subform B |
| --------------------- ---------------------- |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | ---------------------- |
| | | Subform C |
| | | ---------------------- |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| --------------------- ---------------------- |
| |
---------------------------------------------------------

Subform A is not directly linked (parent-child) to the Main Form; however, they share a field (unique Foreign Key in the Main Form dataset) that can be used to link them. Subforms B & C are linked to the Main Form through parent-child links.
Records are shown to users on all three Subforms, but not on the Main Form (which is based on a linking table connecting the other three forms tables)
The actual “driver” of record navigation is Subform A. All existing records are shown (continuous form); a Boolean field indicates Products (records) that are in use for a given project. The desired record navigation is that after the user goes to a record that it is in use, the other two Subforms will show the corresponding records.
Record movement on the Main Form is controlled using the foreign key from Subform A. A recordset.find (Subform A foreign key) is used to locate the corresponding record on the Main Form, Subforms B & C records are repositioned without problem due to the parent-child relationship with the main-form. Gold, all good here.

Now the real problem: records without a corresponding foreign key on the Main Form (items not-in-use on Subform A). when the current record on Subform A is a not-in-use record, the recordset.nomatch for the Main Form is true. I tried to no avail to use that to force the record position on the Main Form to move to a “no record” location (before or after respectively first or last record), which by its turn would force Subforms B & C to show no records.

What actually happens is that the main form still shows the record corresponding to the last in-use product visited in Subform A. Naturally, Subforms B & C also show the records that are linked to the key on the Main Form. Lead, not good at all here.

Sorry for the long story. What am I missing? I know that is probably something easy and stupid, but I can’t see it.

Or the whole navigation strategy is bad and I should rethink it all (which I really hope is not the case)?


Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[lameid]

To work around your problem you could append a new record to the Main form recordset when the no match occurs, requery and then find the record again.

If it was me I would have probably just had the subforms point to the subformA via criteria and then requery them on Subform A's On current event and set the default values appropriately.

A different approach would be to use a Bound main form that has one record (the table would be in the Front End if you are split) and then update the record on subform A's On Current event and after update event of the key field.

 

[TheAceMan1]

How are ya 4N6Mstr . . .

Why not hide subforms B & C when [blue]NoMatch[/blue] occurs!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[4N6Mstr]

Hello Lameid / TheAceMan,

Thank you both for your posts.

Lameid: I'd like to avoid creating records just to destroy them later (or not .update). There is an audit feature that would have to account for all Primary Keys (autonum) not showing on the table.

In regards to your other suggestion, the underlying data on Subform A is not directly connected to Subforms B & C. That connection is established through the underlying data on the Main Form. Naturally, I could change the underlying data on Subform A to a query that would include the data from the Main Form; however, that would create other complications, as the users can change the status of a product from not-in-use to in-use on-the-fly. In addition, navigation control logic would be increased. Besides, I'm not sure if that would solve completely my issue, as I would have to send both Subforms B & C to "no-record-land" when a not-in-use record is selected. That is exactly what I can't do with the Main Form in the first place.

Ace: to hide the forms was my initial solution; however, I did not like the form-blinking result. I went to the present solution, just to find the nagging issue that I’m dealing with now. One thing that I did not try is to change the visibility of just the detail line. That might have something there.

Another idea is to force-create the first record of the Main Form underlying table and use that as the “no-record” position, but I was trying to avoid tricks and really understand what is going on. The real question is why can't I control the record pointer positioning of the Main Form? When .find fails to (oh well) find a match, present record is undefined, shouldn't the Main Form reflect that and got to no-record?


Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[TheAceMan1]

4N6Mstr . . .

You could goto a new record on the mainform which would force subforms B & C to do the same.

A simple message box would also do . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

There is a far easier way to do this. See demo.

http://www.4shared.com/file/176969705/ae98198/SubForms.html

1)Do not bound the main form
2)Not sure what the relation is to subforms B and C,
but on Sub form A's on current event populate hidden textboxes on the main form with the values needed to link subforms B and C. Lets assume it is a primary key from sub A.

Private Sub Form_Current()
Me.Parent.txtbxlink = Me.OrderID
End Sub

Now simply link B and C to txtBxLink. Yes you can link a subform to a control.

You navigate to a record in subform A. This loads a hidden control on the main form with a value. The other subforms move to the value (or new record) in the hidden texbox.

 

[4N6Mstr]

Thx, MajP. Works like a charm. Bound a subform to a txb, what a concept!

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there