I want to replicate the Windows behavior where I have a datasheet of records with a checkbox against each one.
As well as the selection checkboxes I have another one which sets or unsets all or no records (working correctly)
I want to do the reverse setting of the summary checkbox.
So let's say all records are selected and the summary checkbox is ticked.
I unset one record, and I want to set the checkbox to null (indicating part selection).
On the click event of the record checkbox I have me.dirty=false to update the record immediately.
But I'm finding I can't get a count of the selected records. It appears to be not updated in time. My query returns the previous sum of checked records, even though it executes after the me.dirty=false
How can I get a count of the selected records to set my checkbox state ?
When I untick one record, I want to assign 'Null' to the Tags checkbox.
I can do that, but what I can't do is get the sum of checked records in time.
Sub CountTransLines runs a SQL query but it returns the 'old' total of checked records.
Code:
Private Sub slSelected_Click()
Dim lngNumRecs As Long
Dim lngNumTagged As Long
' save the record immediately if we click the tag
Me.Dirty = False
CountTransLines Me.tlTransHeaderFK, lngNumRecs, lngNumTagged
MsgBox "lngNumRecs = " & lngNumRecs & " lngNumTagged = " & lngNumTagged
If lngNumTagged = lngNumRecs Then
' all lines are checked, set the checkbox
Forms!frmJournal!chkToggleTags = True ' ticked checkbox
ElseIf lngNumTagged = 0 Then
' no records are checked, unset the checkbox
Forms!frmJournal!chkToggleTags = False ' unticked check box
Else
' some records are checked, set checkbox to greyed out
Forms!frmJournal!chkToggleTags = Null
End If
End Sub
SELECT tblTransLines.tlTransHeaderFK, Count(tblTransLines.TransLineID) AS CountOfTransLineID, Count(tblSelectLines.slSelected) AS CountOfslSelected
FROM tblTransLines INNER JOIN tblSelectLines ON tblTransLines.TransLineID = tblSelectLines.SelectLinesID
GROUP BY tblTransLines.tlTransHeaderFK
HAVING (((tblTransLines.tlTransHeaderFK)=[HeaderID]) AND ((Count(tblSelectLines.slSelected))=True));
hmmm. don't know what's going on here. CountOfSlSelected always returns the same value as CountOfTransLineID, even though slSelected isn't checked for every record.
To be honest getting the total of tagged lines in a query wasn't my preferred method. It seems like such a simple thing to do but I've been on this for hours. I preferred to let Access do the summing in the sub form datasheet footer but it's not working.
In this screen shot, I have two text boxes at the bottom of the form.
One shows the number of records in the recordset, 54.
The other shows the number of tagged lines, 48. You can see that some records are unchecked.
They are totals in the sub form datasheet footer carried over into the parent form, so Access is doing the counting.
The tagged lines total changes when I check or uncheck a record as you would expect. But it seems like it's never updated in time. So I uncheck, and in the click event I do me.dirty=false, then me.refresh followed by an evaluation of the total of checked records. It doesn't update in time so I don't get the right count.
The number of records in the recordset isn't changing, just a field in one of the records. Do I really need to requery for that ?
My query is supposed to be returning only the number of *checked* records in the linked table, tblSelectLines, but it's returning the *total* number of records.
I think I may need to re-visit my original method of using the datasheet footer totals, which are at least correct, and find some way of retrieving the updated totals. It's late I'll have to sleep on it.
My query is supposed to be returning only the number of *checked* records in the linked table, tblSelectLines, but it's returning the *total* number of records.
I think I may need to re-visit my original method of using the datasheet footer totals, which are at least correct, and find some way of retrieving the updated totals. It's late I'll have to sleep on it.
"Never updated in time" requires an explanation of why you choose that particular phrase. Does it eventually catch up to the point of being accurate? If that is true, then the REAL question is why it is so slow. Or what event or action makes it catch up? We are lacking diagnostic data here.
From post #1
When I untick one record, I want to assign 'Null' to the Tags checkbox.
As to assigning "NULL" to anything... a checkbox has a perfectly good non-null value of FALSE and you should use that. Having a null as a value in a checkbox leads you to the problem that if you did comparisons, NULL is never equal to anything including itself. It leads to various difficulties that only complicate your programming efforts later in the project.
You also commented that using NULL was to indicate "part selection." If you are using tri-state logic, you may be doing yourself serious dirt here. Yes, tri-state logic exists, but using NULL to represent ANYTHING other than "no data available" is probably a misuse of the control. If your checking and unchecking are designed to be a three-state toggle then make a true three-state field that doesn't require null as one of the states.
If your checking and unchecking are designed to be a three-state toggle then make a true three-state field that doesn't require null as one of the states.
The check/uncheck checkbox is a three-state indicator, and a two-state click. It serves two functions.
As an indicator:
Assigning false clears the checkbox, a clean empty box
Assigning true shows a tick in the box
Assigning null shows a small black square (indicating partial selection)
As a checkbox, it does one of two things
Checks all records, as long as *none* are already checked
Unchecks all records, if *any* are already checked
The 'Never updated in time' thing. I've sort of got to the bottom of that. In fact I think it's to do with order of events.
In my sub form datasheet, where I can check individual records, I have a conditional IF which shows the count of checked records in the recordset. And for the most part it works as I expect.
In the record selector checkbox event, I had a "me.dirty = false" so that the individual record was immediately saved and the pencil never appeared. I followed that up with an assignment to my 'check/uncheck all' *according to the value of txtTaggedLinesTotal*, which is the Access footer SUM function which, when carried over to the parent form, shows the correct total of tagged records.
But even though the record had clearly been saved (no pencil!) the value of txtTaggedLinesTotal was *not* updated, until Access had exited from the AfterUpdate routine of the line checkbox. So, how do I assign my triple-state checkbox? I can't get the total of summed lines in time before the event quits. So my 'check/uncheck all' indication was always one-step behind the true condition.
So I have learned to be very careful when relying on the sub form datasheet sum functions provided by Access. Other events which will affect the values take precedence.
I *have* got it working by tweaking my SQL statement so that it returns the correct total of tagged records, *without* relying on form events and timing. It's maybe worth mentioning that when I check a record, I'm not updating the 'data' record but I'm inserting and checking records in another table with a 1:1 relationship.
The 'check/uncheck all' has to be unbound, because as well as assigning a value in code, I want to be able to take user input. So, when it is clicked, if runs code to either set or unset all records.
It's taken quite a bit of code, here is some of it:
This is the click event of the individual checkbox in the sub form datasheet:
Code:
Private Sub chkslSelected_Click()
' user clicked a record selector checkbox ('tag')
' save the record immediately if we click the tag
Me.Dirty = False
' set the status of chkToggleTags on the parent form
SetChkToggleTags Me
End Sub
This is the sub that is called from chkslSelected_Click(), I have a sub 'CountTransLines' which runs the SQL to get the correct total of records and tagged records.
Code:
Public Sub SetChkToggleTags(frm As Form)
' sets the status of the checkbox chkToggleTags according
' to how many TransLine records are tagged.
' call this sub from the sub form that displays the lines.
'
Dim lngNumRecs As Long
Dim lngNumTagged As Long
' run sql query to return the totals *after* the record is saved
CountTransLines frm.Parent!TransHeaderID, lngNumRecs, lngNumTagged
If lngNumRecs = 0 Or lngNumTagged = 0 Then
' no lines are checked, unset the checkbox
frm.Parent!chkToggleTags = False ' unticked check box
ElseIf lngNumRecs = lngNumTagged Then
' all lines are checked, set the checkbox
frm.Parent!chkToggleTags = True ' ticked checkbox
Else
' some lines are checked, set checkbox to greyed out
frm.Parent!chkToggleTags = Null
End If
End Sub
And the click event of the 'check/uncheck all' checkbox on the parent form
Code:
Private Sub chkToggleTags_Click()
' this is the checkbox event to toggle *all* tags on or off
If Me.txtRecordCount > 0 Then
If Me.TxtTaggedLineCount = 0 Then
' no lines are checked, set them all
Me.chkToggleTags = True ' ticked checkbox
SetTransactionTags Me.TransHeaderID, True
' requery the datasheet, this is necessary because
' the records may not yet exist in tblSelectLines
' and we won't see the tagged lines if we don't
ctlTransLines.Form.Requery
Else
' some or all records are checked, uncheck first
Me.chkToggleTags = False ' unticked check box
SetTransactionTags Me.TransHeaderID, False
' refresh so we can see the tags checked.
' records unchecked *must* exist so a refresh
' is sufficient
ctlTransLines.Form.Refresh
End If
End If
End Sub ' chkToggleTags_Click