Snowflake68
Registered User.
- Local time
- Today, 07:51
- Joined
- May 28, 2014
- Messages
- 452
I have an Access database that is used for comparing changes to existing sales orders from a new file which I import and then do the comparisons and mark the records accordingly in several columns in the table.
I have a main form with a Datasheet sub form containing a list of the orders that have changed (this is bound to a query)
The datasheet can be filtered by the user to display specific records. e.g 'Qty Changed' (=Yes) and the user decides which of the sales orders to accept by ticking a box (AcceptSalesOrderChanges) against each record. No problem so far.
On the main form I have a check box called chkSelectAll that when ticked I want to update the [AcceptSalesOrderChanges] column for just the filtered records.
I have the code below (which I found on the internet and then modified) which updates the column called [AcceptSalesOrderChanges] and just for the filtered records to save the user having to tick each and every box.
My issue is that sometimes this code works and other times it doesn't and I cant figure out why. I only seems to work when the data is filtered apart from when the form is first loaded.
I have attached a stripped down version of the app in the hope that someone can work out whats going on.
When you open the app you will be presented with a form showing you the data. If you then filter one of the columns say 'Qty Changed' to only show the 'Yes' values and then click on the 'select all' tick box it will update all of the records in the subform datasheet (all good). If you untick the 'Select All' it will untick all of the filtered records (still all good). BUT when you remove the filter/s and then use the 'select all' it no longer works. :banghead:
Ive tried adding code to requery the subform at various different points. Also tried saving the data in the subform as I also keep getting the 'Data has changed' message.
Hope someone can help please as its been driving me nuts for days now
I have a main form with a Datasheet sub form containing a list of the orders that have changed (this is bound to a query)
The datasheet can be filtered by the user to display specific records. e.g 'Qty Changed' (=Yes) and the user decides which of the sales orders to accept by ticking a box (AcceptSalesOrderChanges) against each record. No problem so far.
On the main form I have a check box called chkSelectAll that when ticked I want to update the [AcceptSalesOrderChanges] column for just the filtered records.
I have the code below (which I found on the internet and then modified) which updates the column called [AcceptSalesOrderChanges] and just for the filtered records to save the user having to tick each and every box.
My issue is that sometimes this code works and other times it doesn't and I cant figure out why. I only seems to work when the data is filtered apart from when the form is first loaded.
I have attached a stripped down version of the app in the hope that someone can work out whats going on.
When you open the app you will be presented with a form showing you the data. If you then filter one of the columns say 'Qty Changed' to only show the 'Yes' values and then click on the 'select all' tick box it will update all of the records in the subform datasheet (all good). If you untick the 'Select All' it will untick all of the filtered records (still all good). BUT when you remove the filter/s and then use the 'select all' it no longer works. :banghead:
Ive tried adding code to requery the subform at various different points. Also tried saving the data in the subform as I also keep getting the 'Data has changed' message.
Hope someone can help please as its been driving me nuts for days now
Code:
Private Sub chkSelectAll_AfterUpdate()
DoCmd.SetWarnings False
Me!sfrmRR_Check_SO_Changes.Requery
Dim rs As DAO.Recordset
Set rs = Me!sfrmRR_Check_SO_Changes.Form.RecordsetClone
While Not rs.EOF
rs.Edit
If chkSelectAll = vbTrue Then
rs!AcceptSalesOrderChanges.Value = True
Else
rs!AcceptSalesOrderChanges.Value = False
End If
rs.Update
rs.MoveNext
Wend
Set rs = Nothing
Requery
DoCmd.SetWarnings True
End Sub
