Conflict with Record Counter and Print check box selector

[Redbeard]

Conflict with Record Counter and Print check box selector
I have created a new navigation bar for my form, as the one Access provides is very small and hard to read. The problem is with the Record counter code is conflicting with a Print Selector button that already exists on my form. I use the Print Selector button when I have filtered the form down to say 20 records that I want. Then when I click the button, it automatically checks a bound check box for all 20 records, so I don’t have to do it one by one. I use this check box as a record selector to then run reports or do more filtering. Now when I click the button it only checks the first record but not the rest. I did not write the code for the Print Selector button and do not entirely understand how it works. But, I think I know what the conflict is I just don’t know how to fix it. Both codes run through the record set to select records that have been filter, one counts them the other updates. The line that causes the problems is line 3 in the Record Counter, but if I take it out it does not work properly. The codes are below.

Record counter: (in On Current of Form)

Private Sub Form_Current()
With Me
     If RecordsetClone.RecordCount > 0 Then .RecordsetClone.MoveLast
    !txtRecordDisplay = "" & .CurrentRecord & " of " & .RecordsetClone.RecordCount
    !cmdFirst.Enabled = .CurrentRecord <> 1 'And Not .NewRecord
    !cmdPrevious.Enabled = .CurrentRecord <> 1 'And Not .NewRecord
    !cmdNext.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount 'And Not .NewRecord
    !cmdLast.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount 'And Not .NewRecord
End With
End Sub

Print Selector button:

Private Sub filtog_Click()
On Error GoTo Err_filtog_Click
Set DBase = CurrentDb()
Set Fc = Forms![frmDescription]
Set filttogrecset = Fc.RecordsetClone
 
   If filttogrecset.EOF Then
       filttogrecset.MoveFirst
   End If
 
Dim Searcher As Integer
Searcher = 0
 
Do While Not filttogrecset.EOF
Me.Bookmark = filttogrecset.Bookmark
 
filttogrecset.Edit
 
filttogrecset!Search = True
 
filttogrecset.Update
Searcher = Searcher + 1
filttogrecset.MoveNext
 
Loop
 
filttogrecset.Close
 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
txt_ToggleCounter.Value = CStr(Searcher) + " item(s) are toggled"
Exit_filtog_Click:
    Exit Sub
 
Err_filtog_Click:
    MsgBox Err.Description
    Resume Exit_filtog_Click
 
 
End Sub

I have look at changing both codes but have had no luck. Does anyone have any suggestion on how to resolve this with changes to either of the codes?

[zmbd]

You haven't mentioned which version of Access nor if this is a recent move from an older version to a newer version.

Also, if you open the form using the "old" method, does the code work?

How do you call this form vs. how you used to call the form?

Your first code block, "Form_Current," is toggleing the state of the record-navigation/movement buttons. It doesn't appear to be altering the value of the [Search] field which the second block appears to be doing.

The second code block, "filtog_Click" are you getting any error messages from this when you click on the code?

Also try this:
Comment out line 29 by placing a single quote ( ' ) in front of it, the line should turn green if the defaults are used for font colours. I don't want to delete this line of code yet, just idle it for the moment.
Now Right below that line enter:

me.refresh

This should show only the changes to the records made within this code.

Let us know what happens.

[Redbeard]

Ok, I will try an explain better. First I am using Access 2010 and have been for a few months now. Everything was working fine then I created the new record navigator to replace the small one Access provides at the bottom of the screen. It has a forward, back, move last, move first and new record buttons with an unbound text box for the record count. It also worked with no issues on my form. However, when I filtered down to 20 records that I would like to print and click the Print Selector button, it just checked the last records check box and not all of them. When I turn the new navigation bar off, by placing single quotes on every line of the code, the Print Selector button works and puts a check in all 20 records check box. I do not get an error message when this happens, it just does not work.

So since the code on the Print Selector button runs through each record with (.MoveNext) and put a check in the ones that are currently filter, and the record count box uses (.MoveLast) when counting… I am assuming that when I click the Print Selector button it moves to the next record then the record count code counts and moves to the last and only the last one gets checked.

I tried what you suggestion zmbd by taking out line 29 and putting in the requery but nothing changed.

I have been looking at alternative codes for both but nothing seems to work. Any suggestions?

[zmbd]

Why are you using recordsetclone in the first block and not recordset. In this case you are looking at the actual position of the current record as indicated by the cursor. One normally uses the clone when you don't need to have the form synced with the record operations (one can ofcourse sync via the bookmark as needed).
So let's try something very simple within the first code block and simply remove "clone."

If that doesn't fix it then I'll need to really read thru things carefully and maybe build a test database to see if I can replicate what is happening to you.

[Redbeard]

Hi zmbd. I tried removing “clone” as you suggested and it takes me to the last record on load. When I try to move off that record it gives me a message that “I can’t go to that record and puts me back to the last one. After doing a bit more hunting around on the internet I have clean up my code and used what seem to be more stander as I have found multiple post of similar code (see below). I also found a couple of post that say you need to use “RecordsetClone” to get an accurate count… not sure if that’s true but it does seem to work well. However, I still get the conflict when I try and use my Print Selector button. Since I think that I am at a dead end with changing the record count I am now trying to figure out a way to modify the Print Selector button code. Any other suggestion are appreciated.

Dim rst As DAO.Recordset
    Dim lngCount As Long
 
    Set rst = Me.RecordsetClone
 
    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
 
    Me.txtRecordDisplay = Me.CurrentRecord & " of " & lngCount
    Me.cmdFirst.Enabled = Me.CurrentRecord <> 1
    Me.cmdPrevious.Enabled = Me.CurrentRecord <> 1
    Me.cmdNext.Enabled = Me.CurrentRecord <> Me.RecordsetClone.RecordCount
    Me.cmdLast.Enabled = Me.CurrentRecord <> Me.RecordsetClone.RecordCount

[Redbeard]

So I have made some progress… I have added this to the Print Selector button code:
At the start

Me.txtRecordDisplay.Enabled = False

and at the end

Me.txtRecordDisplay.Enabled = True

And I have added this to the “On Current” of my form:

If Me.txtRecordDisplay.Enabled = True Then

with an "EndIf" at the bottom.

The “txtRecordDisplay” is my unbound textbox that displays my record count. The theory is that when the Print Selector button is click it disables the record count box before starting to move through the records and checking the boxes. When the “On Current” runs, it requires that box to be enabled to set the record count. So since the “On Current” is not running there is no conflict and the code should work! When all the boxes are checked it enables the textbox again.

So this is what actually happens… when I click the Print Selector button first time it does the same thing… checks the checkbox in only the last record. But when clear the checkbox and click the Print Selector button again it preforms the function as intended and check the check box in every record! So I am almost there, just need to figure out why it won’t do it the first time through? Any thoughts?

[zmbd]

Ok, this is going to take more time to look at than I have at the moment. It's my day off and I have the four kids :-)
Love'em - but they take a lot of work and chasing after!

[Redbeard]

No worries zmbd... I am just about to go off on a long weekend and will not be back to this till next Thursday. Enjoy the time with the kids.