Using a Check Box in a Subform to select records

[Guest]

I have a Form with a subform and two combo field are used to filter for
records in the subform. The records in the subform have a bound check box.
When I check one record on the subform and then move my forcus off the
subform, every record becomes checked.

Any suggestions?

Thanks

 

[Guest]

Are you sure the check box is a bound control? The behaviour you are getting
suggests that its more likely to be unbound. What does it say in the
control's ControlSource property in its properties sheet?

If it is bound to a column in the underlying recordset then the behaviour
could be explained by a number of things:

1. If the checkbox is bound to a column which is just one row in a table
joined one-to-many to another table in a query.

2. If there is some code in the checkbox's AfterUpdate event procedure
which is updating the values in the column in all the rows in the recordset.
This is theoretically possible but unlikely, as its not the sort of thing
you'd be unaware of.

Ken Sheridan
Stafford, England

 

[Guest]

I figured it out. I some how unbound the check box. OOPS. How I have
another issue. I would like to add code to uncheck the records in the
subform if the user refilters the subform using the combo box I have on the
main form. I am trying to uncheck on the AfterUpdate on the combo box, but I
just can not seem to get is right.

Any hints???

 

[Guest]

Provided you can establish a criterion to identify the rows in the subform's
underlying recordset you can execute an SQL statement in code to update the
rows, then requery the subform.

Let's assume the subform is based on a table called MyTable and its linked
to the parent form on columns called MyID and the column to which the
checkbox is bound is called MyBool, then the code for the AfterUpdate event
of the combo box in the parent form would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE MyTable " & _
"SET MyBool = FALSE " & _
"WHERE MyID = " & Me.MyID

cmd.CommandText = strSQL
cmd.Execute

Me.YourSubFormControl.Requery

This assumes the MyID columns are a number data type. If they were text
you'd need to wrap the value in quotes:

"WHERE MyID = """ & Me.MyID & """"

YourSubFormControl would be the name of the subform control in the parent
form which houses the subform.

Ken Sheridan
Stafford, England

 

[Guest]

It works, thanks.

Provided you can establish a criterion to identify the rows in the subform's
underlying recordset you can execute an SQL statement in code to update the
rows, then requery the subform.

Let's assume the subform is based on a table called MyTable and its linked
to the parent form on columns called MyID and the column to which the
checkbox is bound is called MyBool, then the code for the AfterUpdate event
of the combo box in the parent form would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE MyTable " & _
"SET MyBool = FALSE " & _
"WHERE MyID = " & Me.MyID

cmd.CommandText = strSQL
cmd.Execute

Me.YourSubFormControl.Requery

This assumes the MyID columns are a number data type. If they were text
you'd need to wrap the value in quotes:

"WHERE MyID = """ & Me.MyID & """"

YourSubFormControl would be the name of the subform control in the parent
form which houses the subform.

Ken Sheridan
Stafford, England