Custom Navigation for Subform 1

[CantDo]

Hi. New to this forum, so here goes.

First, let me say that TheAceMan1's advice on thread thread702-1064799 -- "Custom Navigation Buttons like - Next, Prev, First, New & so", was great! It got me to third base, but I need a bit more advice, if possible.

I've created custom navigation buttons on a populated form that controls the (filtered) records on a linked subform. It works great, except that the text indicator ("Record x of y") only works after a navigation button has been pushed (since it runs on the subform's Current event, as advised in the previous post mentioned above).

Can anyone help so that the text indicator works on opening the main form? Any help is most appreciated! Thanks.

 

[TheAceMan1]

How are ya CantDo . . . . .

How are you controlling the filtering of the subform?

See Ya! . . . . . .

 

[CantDo]

Thanks for responding. I'm controlling it the "normal" way, through the child link and master link fields on the subform control (so the subform only shows the record match on the main form).

In other words, the master form holds family level data and the subform, through a child link field, displays items belonging to that family.

Does that help to clarify?

Thanks again.

 

[TheAceMan1]

OK rmtiptoes . . . .

For future reference, if Form & subform are linked [blue]say linked[/blue] . . . . not filtered . . . its misleading.

To pin this down:
[ol][li]Which form has the custom Nav Buttons?[/li]
[li]Which form are the Nav Buttons controlling?[/li]
[li]Which form has the [blue]x of x[/blue] textbox?[/li][/ol]
If you placed code in the [blue]OnCurrent[/blue] event, post it.

See Ya! . . . . . .

 

[CantDo]

Thanks again Aceman, sorry for the confusion.

The custom nav buttons, and the textbox, are on the main (or master) form. The nav buttons are controlling the records on the subform.

The code for OnCurrent is on the subform and says:

Private Sub Form_Current()

Dim frm As Form, LastRec As Long

Set frm = Forms!FamilyMembersForm 'MainForm Name

If Trim(frm!txtRecordNo & "") = "" Then
Me.RecordsetClone.MoveLast
DoEvents
End If

LastRec = Me.RecordsetClone.RecordCount
If Me.NewRecord Then LastRec = LastRec + 1

frm!txtRecordNo = "Family Member " & CStr(Me.CurrentRecord) & " of " & CStr(LastRec)

Set frm = Nothing

End Sub

Most of the code should look familiar as it was given by you in a previous post. And it works fine, once the user hits a nav button. What I'm having trouble with is how to make the "x of x" show "1 of [total]" when the master form opens. It may be as simple as putting it in the right event, but I've tried OnOpen (subform) OnActivate (Subform), etc with no luck. Somehow, I have to find the recordset number for the linked subform's records and pass it to the master form. I'm just stuck after trying a bunch of things like recordsetclone, etc.

As you can probably tell, I'm not fluent yet with Access VB, but I'd sure like to be! Thanks again!

 

[CantDo]

Aceman:

OK, it may not be elegant, but here is the only way I can get the darn thing to work right on opening the main form!

create an invisible text box (call it, say, "TotalNum") on the main form and set its control source to (all items in red to be substituted with relevant names):

=DCount("*","[table source]","[table source].[link child field] = Forms![parent form]![link master field]")

Next, add code to the Main Form's Open Event as follows:

Me!txtRecordNo = "1 of " & Me!TotalNum

It works, but it would be nice to make this more elegant. Anyway, your assistance was invaluable! If you have anything more to add, please do so; I'm always looking to learn. Thanks!

 

[TheAceMan1]

CantDo . . . . .

Sorry to get back so late (Mom not well).

Try the following. Note:
The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[blue]Private Sub Form_Current()

  Dim frm As Form, LastRec As Long, [purple][b]rst[/b][/purple] As DAO.Recordset
   
   Set frm = Forms!FamilyMembersForm 'MainForm Name
   Set [purple][b]rst[/b][/purple] = Me.RecordsetClone
   
   If Trim(frm!txtRecordNo & "") = "" Then
      [purple][b]rst[/b][/purple].MoveLast
      DoEvents
   End If
   
   LastRec = [purple][b]rst[/b][/purple].RecordCount
   If Me.NewRecord Then LastRec = LastRec + 1
   
   frm!txtRecordNo = "Family Member " & CStr(Me.CurrentRecord) & " of " & CStr(LastRec)
   
   Set rst = Nothing
   Set frm = Nothing
          
End Sub[/blue]

Don't forget to disable your other code.

[purple]Interested in how this works out! . . . .[/purple]

See Ya! . . . . . .

 

[CantDo]

It still doesn't work. I assume you wanted this put into the subform's current event. but when I did, I still get "1 of 1" when the main form opens. I think that's because, until I enter or change something in the subform, its not "On Current" -- does that sound right? The only thing that's worked so far is my inelegant solution posted above.

Sorry to hear about your mom. Hope she feels better.

Thanks again.

 

[TheAceMan1]

[blue]until I enter or change something in the subform, [purple]its not "On Current"[/purple] -- does that sound right?[/blue]

No! . . . the [blue]Current Event[/blue] is triggered [blue]whenever you open a form . . . that includes subforms[/blue], and be aware: [purple]subforms open first![/purple]

I really need to know what this is, so if you dont mind, do the following:
[ol][li]In the Current event of the subForm set a breakpoint on the following line:

[blue]   DoEvents[/blue]

[/li]
[li]Close out and open the form.[/li]
[li]If the debugger stops at the brake point, [blue]thats good[/blue]. Go on to the next step.
If the debugger doesn't stop at the brake point,[purple]thats bad[/purple]. Stop here and let me know.[/li]
[li]Set a break point on the following line:

[blue]   If Me.NewRecord Then LastRec = LastRec + 1[/blue]

[/li]
[li]Close out and open the form.[/li]
[li]When the debugger stops the code, hold the mouse over [blue]LastRec[/blue]. Its value will popup.
Post that value.[/li][/ol]

See Ya! . . . . . .

 

[CantDo]

OK. All the break points worked fine. The result is LastRec = 5, which is correct for this particular Family.

However, when the form opens, the text box says "1 of 1", despite your code returning the correct value for LastRec.

For clarification, in case this makes a difference, the main form and subform are called from a double-click event on a list box and the main form is filtered (yes, I do mean it this time) to show the record from the line double clicked. The subform is linked to that main form by the same field that called it, i.e., the key field of the Family Name. What I am trying to achieve here is a listbox showing basic family info that, when double clicked, allows edit and browse functions for detailed info on that femily and its members. The subform holds the members' info. The whole thing works, except for this one feature I'm asking about now (if I don't use the method I described above)

You're spending a lot of time on this to help me and I really appreciate it. Thanks.

 

[CantDo]

Aceman, I found the culprit!

I had the following code in my main form's OnOpen event:

DoCmd.ApplyFilter , "FamilyIDNumber = Val(Forms!MainMenu!LinkID)"

When I removed this code, everything you gave me worked perfectly! The code was redundant since I am filtering through the query datasource for the main form. It was there because I originally couldn't get the filter to work and this was something I had read about, so I included it and forgot to get rid of it when I filtered in the query.

So that solves everything. Thanks again!

 

[TheAceMan1]

CantDo . . . . .

[blue]Excellent! . . .[/blue]

When you have a chance [blue]learn to use the Watch Window[/blue] in VBE ([blue]you won't have to hover the cursor to see the values[/blue]), it'll answer alot of questions in troubleshooting, and you can monitor as many items as you like. For instance you could have monitored:
[ol][li]Me.CurrentRecord[/li]
[li]LastRec[/li]
[li]frm!txtRecordNo[/li]
[li]Trim(frm!txtRecordNo & "")[/li][/ol]
[blue]All at the same time! . . . even watch them change as you single step!.[/blue]

[purple]Cheers![/purple]

See Ya! . . . . . .

 

[CantDo]

Aceman:

Your code works great, but its now causing a different problem. I'm going to try to explain, buts its complex so bear with me.

I'm opening a filtered form (with subform) from a list box double-click event. The form is based on a saved query that links two tables by equating a common field (e.g., Code_ID). The form is filtered by equating the list box entry ID to one of the queries table's Key ID field (e.g., WHERE table.ID = forms!form!list_ID). The subform is populated from a 3rd table and is linked to the main form by a commmon field ID key, which happens to be the same as the previous filter ID.

If I try opening the form when either there is no query linking field populated (i.e., table 1 has not its query linking field blank) or there are no records in table 3 tied to table one (i.e., no records have been entered yet for table 3 belonging to the record chosen from the list box), then I get an error "Run Time Error '3021': No Current Record". This is generated from the code you provided me with; if I comment all of that code, I get the form to work as I need it to.

I'm sure this is probably something basic that I'm missing, but do you know how to modify the code you gave me to avoid the form error? I'm trying to get the form to open and allow for updating so these fields can be entered if the user wants, but need not appear if the information is unknown.

Any help is most appreciated -- again. Thanks.

 

[TheAceMan1]

CantDo . . . . .

Try This:

[blue]  Dim frm As Form, LastRec As Long, rst As DAO.Recordset
   
   Set frm = Forms!FamilyMembersForm 'MainForm Name
   Set rst = Me.RecordsetClone
   
   [purple][b]If rst.BOF Then
      frm!txtRecordNo = "No Records!"
   Else[/b][/purple]	
      If Trim(frm!txtRecordNo & "") = "" Then
         rst.MoveLast
         DoEvents
      End If
      
      LastRec = rst.RecordCount
      If Me.NewRecord Then LastRec = LastRec + 1
      
      frm!txtRecordNo = "Family Member " & CStr(Me.CurrentRecord) & _
                        " of " & CStr(LastRec)
   [purple][b]End If[/b][/purple]
      
   Set rst = Nothing
   Set frm = Nothing[/blue]

See Ya! . . . . . .

 

[CantDo]

Once again, you prove why YOU ARE THE aceMAN!

Thanks!

 

[TheAceMan1]

BTW . . . Welcome to Tek-Tips!

See Ya! . . . . . .

 

[CantDo]

AceMan, something you said above -- "be aware: subforms open first!" -- just solved another problem I was having! I gave you another star for another hidden gem in all the help you've given me so far. Thanks again!