Auto Tick on other records having the same ID when one record is ticked

[kevin19]

Can anyone kindly show me how to auto tick or set checkboxes of other records having the same TransID when one has been ticked? Please find attached a screenshot.
Thanks

 

[arnelgp]

on click event of your "Select" control:

private sub select_click()
dim varTransID as variant
dim bolSelect as boolean
dim rs as dao.recordset

varTransID =me.TransID
bolSelect = me.select
set rs=me.recordsetclone

with rs
if not (.bof and .eof) then .movefirst
while not .eof
if (!TransID & "" = varTransID & "") then
.edit
!Select = bolSelect
.update
end if
.movenext
wend
.close
end with
set rs=nothing
end sub

 

[kevin19]

Thanks Arnel!

Works a treat.....
Only thing is that I keep having the Write Conflict Dialog box appearing each time I go to the next record???
Any idea??
Thanks once again.

 

[arnelgp]

sorry about that when you tick the select button, the form is in update mode already. you must first save the record.

DoCmd.RunCommand acCmdSaveRecord

private sub select_click()
dim varTransID as variant
dim bolSelect as boolean
dim rs as dao.recordset

varTransID =me.TransID
bolSelect = me.select
DoCmd.RunCommand acCmdSaveRecord
'or if the above code does not work, try uncomenting this code,
'and comment out the above code
'Me.Dirty = False
set rs=me.recordsetclone

with rs
if not (.bof and .eof) then .movefirst
while not .eof
if (!TransID & "" = varTransID & "") then
.edit
!Select = bolSelect
.update
end if
.movenext
wend
.close
end with
set rs=nothing
end sub

 

[kevin19]

Thanks Arnel once again! What more can I say! Works Great!!

 

[kevin19]

Hi Arnel

Thank you very much for your great help previously on the auto selection. Now I need to display the total amount of the selected records in a text box in the form i.e.
Checkbox ID Qty UnitPrice
True 1 2 £5
True 3 1 £10
Display the total value which is £20 in a textbox in the form header or footer after ticking the checkboxes in the form.
I tried different ways without success. Any chance to help out as you are the expert?
Thanks
Regards
kevin

 

[arnelgp]

you can use the Sum() function in addition with IIF() as controlsource.

=Sum(IIF([CheckedControl], [Qty] * [UnitPrice], Null))

 

[kevin19]

Thanks Arnel! My saviour again. What more can one say!!!

I have created a button to unselect all the records. It works but I am unsure whether I am doing it properly. I am applying an update query to set the checkbox to False but the query obviously runs through all the whole table.
This may not be as effective as just clearing the specific selected records rather then going through the whole table.
Any expertise advice or guidance??

 

[arnelgp]

unfortunately, it will update all records when you use update query.
do the update in the recordset level:

private sub yourButton_Click()
Dim rs As Dao.Recordset
'make sure all records are saved before updating
'so that we don't have write conflict.
If Me.Dirty then Me.Dirty = False

Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
![Selected] = False
.MoveNext
Wend
.Close
End With
Set rs = Nothing
End Sub

 

[Galaxiom]

I have not reviewed the database but ticking one record with a requirement that all corresponding records be automatically ticked suggests the data model is incorrect. There aught to be a place where the tick can be applied to the entire group.

 

[gemma-the-husky]

I agree with galaxiom.
My initial reaction was that there might be a data analysis issue.
If your table has multiple records with the same key then there ought to be another table, and these two table should be split to give a single header record, and multiple detail records. Then you only need to select the record header.

Note that you could just run an update query

currentdb.execute "update table set tickfield = true where idfield = whatever"

you may find you need a me.requery after running this.

 

[kevin19]

Thanks gemma and galaxion!
will look at it.

 

[kevin19]

Hi Arnel

I tried the code but there seems to have a bug. I just use the update all query for now which is working fine.

However, I am trying to run a make table query where I can prompt the user to input the table name to be saved. Getting lost. Trying to find a way to create the parameter to input. I google for an answer but didn't get anywhere....
Any idea?
Thanks