Custom Navigation Buttons like - Next, Prev, First, New & so 3

[Kenny2003]

Hi gang,

Just spent most of today and all evening trying to get this to work and access keeps giving me a "Run Time error '2489' so before I tear all my hair out can any one suggest how to do what I need.

I am trying to write the code for Custom Navigation Buttons like - Next, Prev, First, New & so on.

I have a main form [frmMain] that is not bound to anything, it just acts as a holder for the sub-form and contains a couple of labels like the database name and so on.

On the main form is a sub-form [frmSub1] that is bound to the table that contains the actual data.

I want to have a set of buttons on the main unbound form that moves the records back or forth. To keep it simple, if we look at just the "next record" button.

I inserted a command button and on its "on click" property I have tried various code but none of it worked. I have searched all over the net but with no luck. I keep getting the same run-time error '2489' The Object '[Forms]![frmMain].[Form]![frmSub1]' isn't open? Below is a code sample so you know what I have tried to do so far.

Thank you in advance,

Kenny

------------------Code Begins-----------------------
Private Sub cmdGoToNext_Click()
DoCmd.GoToRecord acDataForm, "[Forms]![frmMain].[Form]![frmSub1]", acNext
End Sub
------------------Code Ends-------------------------

 

[TheAceMan1]

Kenny2003 . . . . .
[ol][li]Add an [blue]Unbound Textbox[/blue] to the header or footer section and set the [blue]Name[/blue] property to [purple]CurRec[/purple].[/li]
[li]In the [blue]Current[/blue] event of the form, copy/paste the following:

[blue]   Me!CurRec = CStr(Me.CurrentRecord) & " of " & _
               CStr(Me.RecordsetClone.RecordCount)[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[Kenny2003]

Hey ya TheAceMan1,

Thanks for the suggestion.

I tried out the code as you suggested but Access throws up Run-Time error '2455' You entered an expression that has an invalid reference to the property CurrentRecord.

I have tried referring to CurRec in various different ways but I just cant seem to get it to work.

If I put CurRec on the sub-form, it works fine. Sadly from a GUI point of view my form is not designed to have the record count of the sub-form.

Any further suggestions?

Regards,

Kenny

 

[TheAceMan1]

Roger That Kenny2003 . . . .

[ol][li]Delete the previous code (where ever you put it).[/li]
[li]Put [blue]CurRec[/blue] on your mainform [blue]frmMain[/blue].[/li]
[li]In the [blue]OnCurrent[/blue] event of your subform [blue]frmSub1[/blue], copy/paste the following:

[blue]   Dim frm As Form
   
   Set frm = Forms!frmMain
   
   frm!CurRec = CStr(Me.CurrentRecord) & " of " & _
                CStr(Me.RecordsetClone.RecordCount)
   
   Set frm = Nothing[/blue]

[/li][/ol]
[purple]Thats it! . . . . .[/purple]

See Ya! . . . . . .

 

[Kenny2003]

Hi TheAceMAn1,

Boy oh boy am I making you work tonite - Cant express my gratitude enough.

OK, I did exactly as you said above except I changed the line "Set frm = Forms!frmMain" to Set frm = Forms!frmPlant_Main as frmPlant_Main is the name of my main form which has the text box CurRec - Is this correct?

Access is giving (yet) another error - lol, this time its saying: Run-Time error '2455': You entered an expression that has an invalid reference to the property CurrentRecord.

When I hit Debug, the following lines are highlighted in yellow:
frm!CurRec = CStr(Me.CurrentRecord) & " of " & _
CStr(Me.RecordsetClone.RecordCount)

"When I move the cursor (with my mouse) over the section "frm!CurRec" Access shows the following: frm!CurRec = Null.

Then when I move my cursor over the section "CStr(Me.CurrentRecord)" Access shows the following: Me.CurrentRecord = <you have entered an expression that has an invalid reference...

If I could give you 3 gold stars I would for help above and beyond the call of duty. Any ideas ?

Regards,

Kenny

 

[TheAceMan1]

Kenny2003 . . . . .

The indication is that loading isn't quite finish.

In the [blue]OnLoad[/blue] event of the subform try:

[blue]   DoEvents[/blue]

This is something I'm gonna have to log in my library. What version Access?

See Ya! . . . . . .

 

[Kenny2003]

Hey Ya TheAceMan1

I tried your suggestion but I get the exact same error as before.

I am using Access 2000 (9.0.2720)

Kenny

 

[RoyVidar]

Initially, you say your main form doesn't have any recordsource, then adressing currentrecord, recordseclone on the main form would probably throw such errors.

If you're after the subform count, use a reference to the subform/subform recordset.

[tt]me.controls("CurRec").value = _
me.controls("Garden_Sub").form.currentrecord & " of " & _
me.controls("Garden_Sub").form.recordsetclone.recordcount[/tt]

Roy-Vidar

 

[Kenny2003]

Hi RoyVidar,

Thank you for the reply and code suggestion.

It kind of works, it displays "1 of 1" no mater what record you are looking at and no matter if you click the Next Record, Prev Record and so on.

Any further suggestions please?

Regards,

Kennyh

 

[RoyVidar]

Most of these things are available through a search here - this is probably related to the recordset not being fully populated, try a

[tt]me.controls("Garden_Sub").form.recordsetclone.movelast[/tt]

first, or from the on current of the subform, you should be able to refer to the parent forms control through for instance

[tt]me.recordsetclone.movelast
me.parent.form.controls("CurRec").value = _
me.currentrecord & " of " & _
me.recordsetclone.recordcount[/tt]

Roy-Vidar

 

[TheAceMan1]

OK Kenny2003 . . . . .

After some deep digging in my library, ran across some notes relating to the [blue]OnCurrent Event and loading of the RecordSource when a form is open.[/blue] So without getting into a deep explannation try this ([blue]should work![/blue]):
[ol][li]Delete [blue]DoEvents[/blue] in the OnLoad event of the subform.[/li]
[li]In the [blue]OnCurrent[/blue] event of the subform, replace the code with the following:

[blue]   Dim frm As Form, LastRec As Long
   
   Set frm = [purple][b]frmPlant_Main[/b][/purple] [green]'MainForm Name[/green]
   
   If Trim(frm!CurRec & "") = "" Then
      Me.RecordsetClone.MoveLast
      DoEvents
   End If
   
   LastRec = Me.RecordsetClone.RecordCount
   If Me.NewRecord Then LastRec = LastRec + 1
   
   frm!CurRec = CStr(Me.CurrentRecord) & " of " & CStr(LastRec)
   
   Set frm = Nothing[/blue]

[/li][/ol]
[purple]Thats it . . . give it awhirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[Kenny2003]

Hi RoyVidar

Thanks for the post and the suggestion. You code kinda worked, the text box CurRec simply displayed "1 of 1" regardless of which record I was viewing.

I think we are getting close to cracking this but I am at the mercy of you guys.

Thanks again,

Kenny

 

[Kenny2003]

Hi TheAceMan1

Thank you so so much for taking the time to help me. I especially appreciate the fact that you looked up some stuff in your library, not everyone would do that -thank you.

OK, I did exactly as you suggested and.. yes, yet again rotton old Access threw up an error.

As soon as I click on one of the form navigation buttons, VBA opens and in a pop-up says "Microsoft Visual Basic: Compile error - Varible not defined. It highlites in blue the "frmPlant_Main" section of the line Set frm = frmPlant_Main 'MainForm Name

I never dreamed whan i started this db that such a small db could be so much trouble to design but it's a great learning experience.

Any suggestions?

Thanks again,

Kenny

 

[TheAceMan1]

My fault Kenny . . . .

[blue][b]Change:[/b]
   Set frm = [purple][b]frmPlant_Main[/b][/purple]
[b]To:[/b]
Set frm = [purple][b]Forms!frmPlant_Main[/b][/purple][/blue]

See Ya! . . . . . .

 

[Kenny2003]

Hi TheAceMan1,

What can I say - You really are a complete star!!

It all works great, thank you so so so much, I am really in your debt. I can sincerely say that I am very grateful indeed to you for helping me and staying with it.

Thank you so much again,

Best Wishes

Kenny

 

[Wrathchild]

good stuff, have a twinklah Aceman