Navigation Buttons

[sara82]

I searched the forum on custom navigation buttons and found great posts about it. I downloaded 3 samples and the one that fit what I needed was SJ McAbney's dbNavSubform.zip. sfrmNavigation I imported sfrmNavigation per SJ McAbney's on the post into my database.

I didn't want to start a new thread so I posted on the thread: http://www.access-programmers.co.uk/forums/showthread.php?t=88880
But I didn't get any responses.. I don't know if it's been overlooked.

I am encountering problems when I use dbNavSubForm in my database.

1. I made 2 copies of sfrmNavigation and I am using it on 2 of my forms.
On the main form that I am using it when I first open the database it shows record 1 of 1 when really there is more than 1 record. When I click on the next button it then goes to say 2 of 15.
How can I fix this so that when I open the database it will say 1 of 15 and not 1 of 1?

2. I have a Main form ex: frmMain and a subform on frmMain. I added the the subform (dbNavSubForm) onto my subform. Make sense? Here the navigation buttons do not work. When I try to add a new record it tells me "The object frmTOItems isn't open" When I try to click on the next or previous it tells me "Object variable or With block variable not set. How can I fix this?

I have captured a picture of my form so that you can see what I mean.

Thanks in advance

 

[llkhoutx]

Navigation buttons take more than a little work.

The O'Reilly (Publisher) Access Cookbook, 2nd Ed. has very good examples of same, with code.

 

[RuralGuy]

I have not used SJ McAbney's dbNavSubform.zip but the answer to your 1st question is probably that the RecordSet.RecordCount is not accurate until you have done a MoveLast.

 

[sara82]

Thanks llkhoutx about the info on the book. I just ordered it from amazon.com.

But in the meantime there isn't a quick fix to this.. something that I would have to alter the VB code to? The reason is because my manager needs this to be deployed by the end of the week and I won't have the book that soon.

Thanks RuralGuy for the post. How will I need to modify the code so that it will be accurate?



Thanks.

 

[RuralGuy]

Hi Sara,
I'll must say that your form is one of the best looking forms I've seen in a while. Good Job!
I've had a look at SJ McAbney's dbNavSubform.zip and frankly it is some pretty tricky code. It can function on either the main form or sub form as the *main* form navigation buttons. I don't think it will like having one on both the main form and the sub form. My guess is the Main form will win since it loads last. That is probably why you are getting the errors on the SubForm navigation. You are probably better off doing your own nav buttons on the SubForm.
My guess is you used this navigation system because you had some control over the look and feel as opposed to the ones that come with Access. I'll admit it looks very nice the way you have it.
At this point I can't even get the demo running properly so I can't help yet on the RecordCount problem.
Disable the nav buttons on the subform and see if the main form starts behaving properly.

 

[ghudson]

Sara82,

In response to your two questions.

1) Check this out for my X of Y method is accurate... Record X of Y

2) Create your own navigation buttons within the subform. Use the subforms "footer" area for you subform navigation buttons.

Below is the basic code I use for my custom navigation buttons. To save space I have removed the error handlers but this is so simple yet it always works without failure. The user will never be able to advance beyond the first or last record in the record set. Try these in your main form and your subforms.

Private Sub bAddNewRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    Else
        Beep
        DoCmd.GoToRecord , , acNewRec
    End If

End Sub

Private Sub bUndo_Click()
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdUndo
    Else
        Beep
        MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"
    End If
    
End Sub

Private Sub bFirstRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    Else
        Beep
        DoCmd.GoToRecord , , acFirst
    End If

End Sub

Private Sub bLastRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    Else
        Beep
        DoCmd.GoToRecord , , acLast
    End If

End Sub

Private Sub bNextRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
        Beep
        DoCmd.GoToRecord , , acNext
    End If

End Sub

Private Sub bPreviousRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord > 1 Then
        Beep
        DoCmd.GoToRecord , , acPrevious
    End If

End Sub

'For this one I left the error handler since it is involved.
'It is used in my [URL=http://www.access-programmers.co.uk/forums/showthread.php?t=38364]A better mouse trap?[/URL] sample
Private Sub bSave_Click()
On Error GoTo Err_bSave_Click

    Me.tbHidden.SetFocus

    If IsNull(tbFirstName) Or IsNull(tbLastName) Then
        Beep
        MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
        Exit Sub
    End If

    Beep
    Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & "  Yes:         Saves Changes" & vbCrLf & "  No:          Does NOT Save Changes" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")
        Case vbYes: 'Save the changes
            Me.tbProperSave.Value = "Yes"
            DoCmd.RunCommand acCmdSaveRecord
            Me.tbProperSave.Value = "No" 'added 07/29/2003

        Case vbNo: 'Do not save or undo
            'Do nothing

        Case vbCancel: 'Undo the changes
            DoCmd.RunCommand acCmdUndo
            Me.tbProperSave.Value = "No"

        Case Else: 'Default case to trap any errors
            'Do nothing
    End Select

Exit_bSave_Click:
    Exit Sub

Err_bSave_Click:
    If Err = 2046 Then 'The command or action Undo is not available now
        Exit Sub
    Else
        MsgBox Err.Number, Err.Description
        Resume Exit_bSave_Click
    End If
    
End Sub

 

[sara82]

Thank you RuralGuy I've been working on database for a while now. I really appreciate your input. Yes I noticed the same thing that you did. That it will work fine when it's on a main form.

Yes, I used this navigation system because I had more control of the look of it and could place them where I needed to. I also liked the fact that you can enter a number in the textbox and it take you to that record just like the access nav buttons.

I'll go with your advice and add my own buttons on the subform. I like how this current one is though if there isn't a previous or next record then those buttons are disabled. If it's on the first record, previous is disabled and if it's on the last record then next is disabled.

I'll probably need help though with coding that. Then I can use ghudson's record x of y sample to capture that part of it.

Thanks ghudson for sharing that with me. I tried this out and I got an error on the Save button. In debug mode it highlighted MessagePrompt under bSave_Click()

Also just for my info and to better my understanding, is it necessary to save each time? Doesn't Access do that automatically for you?

 

[sara82]

Also I wanted to mention a separate problem that I am having. If you take a look at my picture, you will see on my subform I have the combo box "Product" Based on what is selected from the combo box a text box will become visible. Right now there are 5 different types of txt boxes this is the code that I have:

Private Sub toggleprop()

 'Sub procedure that toggles the visible properties of the text fields
 'depending on what value of the combobox, Product, is selected

BasicThrough.Visible = Product.Value = "BASIC"
ChgNo.Visible = Product.Value = "CHG"
RevNo.Visible = Product.Value = "REV"
SuppLetter.Visible = (Product.Value = "OS" Or Product.Value = "SS" Or Product.Value = "SUP")
TPNo.Visible = Product.Value = "TP"

End Sub


Private Sub Form_Current()
     
'Sets the visible property of the text fields to false
BasicThrough.Visible = False
ChgNo.Visible = False
RevNo.Visible = False
SuppLetter.Visible = False
TPNo.Visible = False

'Calls the subprocedure and retains the value of property if it is not a new record
If Not Me.NewRecord Then
  Call toggleprop
End If

End Sub

Private Sub Product_AfterUpdate()


'Calls the subprocedure in the afterupdate event of the Product combo box

Call toggleprop
     
End Sub

The problem I am encoutering is also with the navigation buttons. But here I even tested the Nav buttons from Access and I go the problem.

When I try to add a record or go to a record I get the error:

Run-time error '94'"
Invalid use of Null

When I click on Debug it highlights line by line all of my code in toggleprop() and Private Sub Form_Current(). So is it not liking how I am making those txt fields visible?

How can I overcome this problem?

Thanks.

 

[RuralGuy]

I'm not sure what version of Access you are using but the accepted reference for controls now is to use the Me. qualifier, so I would try:

Me.BasicThrough.Visible = (Me.Product = "BASIC")
Me.ChgNo.Visible = (Me.Product = "CHG")
Me.RevNo.Visible = (Me.Product = "REV")
Me.SuppLetter.Visible = ((Me.Product = "OS") Or (Me.Product = "SS") Or (Me.Product = "SUP"))
Me.TPNo.Visible = (Me.Product = "TP")

The same thing in the Current event. The Value property is the default property so it is not necessary to call it out if you do not want to.

P.S. Getting your Nav buttons to enable/disable depending on First/Last Record is a snap!

 

[sara82]

RuralGuy I am using MS Access 2003.

I did as you sugessted and added Me. I am still encountering the same error:
Run-time error '94'"
Invalid use of Null

And as you may have noticed I'm not all that great with VB I added the 3 buttons Add new item, Go to previous item, and Go to next item using the wizard:

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click


    DoCmd.GoToRecord , , acNext

Exit_cmdNext_Click:
    Exit Sub

Err_cmdNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdNext_Click
    
End Sub
Private Sub cmdPrevious_Click()
On Error GoTo Err_cmdPrevious_Click


    DoCmd.GoToRecord , , acPrevious

Exit_cmdPrevious_Click:
    Exit Sub

Err_cmdPrevious_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevious_Click
    
End Sub
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


    DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
    
End Sub

Can you help me for the code for:
When there is no previous record or next record disable those buttons and keep cmdAdd enabled.
When you are at the beginning disable cmdprevious
When you are at the end disable cmdnext

 

[ghudson]

Why did you not try the code I listed above? The code I listed above will prevent the problem you have with your Next and Previous buttons because I am testing if the user is at the first or last record in the record set.

Private Sub bNextRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
        Beep
        DoCmd.GoToRecord , , acNext
    End If

End Sub

Private Sub bPreviousRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord > 1 Then
        Beep
        DoCmd.GoToRecord , , acPrevious
    End If

End Sub

 

[RuralGuy]

Hi Sara,
For the Nav buttons put the following in the Current event of the SubForm:

If Me.CurrentRecord = 1 Then
   Me.cmdPrevious.Enabled = False
Else
   Me.cmdPrevious.Enabled = True
End If

Me.RecordsetClone.MoveLast
If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
   Me.cmdNext.Enabled = False
Else
   Me.cmdNext.Enabled = True
End If

If Me.NewRecord Then
   Me.cmdAdd.Enabled = False
Else
   Me.cmdAdd.Enabled = True
End If

This is <<air code>> it has not been tested and there may be some typo's but I think I've covered all of the bases.

You may want to incorporate gh's Dirty test in your code.

If Me.Dirty Then
   Me.Dirty = False
End If

...will save the record without a message box.

gh,
I think it is a matter of style with Sara. Rather than tell the user they pushed the wrong button she wanted to give them the visual que that it won't work if they try it.

 

[RuralGuy]

The Run-time error '94' is a different problem and unlikely caused by changing the visible property on controls. Moving to a different record will try and save the current record if it is Dirty. I suspect one of your controls is trying to put a Null where is does not belong.
We may also want to leave the cmdAdd button enabled and handle it in the click event.

 

[sara82]

Thank you GH and RG.

GH I'm appreciate you're help. Yes, it is just a matter of style. Instead of having to save the record and having messages, I would just like to enable/disable the buttons.

RG I added your code and it all worked fine.

I needed to add the dirty at the end of the code like so?

Private Sub Form_Current()
     
 If Me.CurrentRecord = 1 Then
   Me.cmdPrevious.Enabled = False
Else
   Me.cmdPrevious.Enabled = True
End If

Me.RecordsetClone.MoveLast
If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
   Me.cmdNext.Enabled = False
Else
   Me.cmdNext.Enabled = True
End If

If Me.NewRecord Then
   Me.cmdAdd.Enabled = False
   Me.cmdNext.Enabled = False
   Me.cmdPrevious.Enabled = True
Else
   Me.cmdAdd.Enabled = True
End If

If Me.Dirty Then
   Me.Dirty = False
End If

Thanks again for all of your help

 

[sara82]

RG as for the Runtime Error, I just noticed that I will get that error when moving to a different record and when that combo box Product does not have anything selected I get that error. So I get that error everytime I add a new record because the combo box has no selection that is made to it.

 

[RuralGuy]

Sara, something else is going on. You should *only* get the error when something is changed which wll set the Dirty flag. Just moving through the subform recordset with the Next or Previous buttons should *not* change anything unless you have some code that it doing that. Is the ComboBox bound to a field in the underlying query/table?

Do *not* put that Dirty code in the Current event. You will need to deal with it in the Click event of the Nav buttons.

 

[sara82]

RG:

Yes the combobox is bound to a field in a table. What I posted is all the code that I have for that. The control source of the combo box is the "Product" field in a table.

As for the Dirty Code is the proper syntax?

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

[COLOR=Red][B]If Me.Dirty Then
   Me.Dirty = False
Else
DoCmd.GoToRecord , , acNext
End If[/B][/COLOR]

Exit_cmdNext_Click:
    Exit Sub

Err_cmdNext_Click:
    MsgBox Err.Description

    Resume Exit_cmdNext_Click
    
End Sub

 

[ghudson]

I have this code posted two times above. Why are you not using it as-is for it does work?!? It will not allow the user to move to next record if they are already at the end of the record source. It does not pop up a message [or error message] or disable anything, it just quietly keeps the user on the current record if they at the end of the record source. Too easy?!?

Private Sub bNextRecord_Click()
    
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
        Beep
        DoCmd.GoToRecord , , acNext
    End If

End Sub

 

[RuralGuy]

gh,
I think Sara is put off by the Beeping and MsgBox messages.

Sara,
If we were going to use the Dirty flag it would be something like:

If Me.Dirty Then
   '-- Save the record before moving
   Me.Dirty = False
End If
DoCmd.GoToRecord , , acNext

BUT let's *not* use that code for now and see what happens. Access will save a record without our help or permission. You need to pin down who is trying to put a Null where it does not belong!

 

[sara82]

Sorry ghudson. I didn't realize that it quietly keeps the user on the current record.
I thought that it you go to the last record that a msgbox pops up notifying you that you need to save or undo.