Solved Check box on continuous form - affects all rows

[andymartin3186]

Hi,
I have a form where I can choose a customer, and then a subform displays all jobs currently set to "For Delivery"
I have added a control on the subform which is an unbound yes/no check box.
A query then looks at this check box status to decide whether to include this item on the delivery note.
The check box works to a point, when unticked it doesn't show on the report, and when ticked it does.
The issue im having is when I tick one row, it ticks all rows. It seems a simple mistake somewhere but can't figure out why its doing this.

Could anyone help?

 

[andymartin3186]

I believe the issue is because its unbound. Do I need to add a field to the job table and bind the control to this?
I didn't really want to store this in a table as its just temporary one off when running the report

 

[Gasman]

Have you tried a query and add the field to that?

You would be correct I believe?

Referencing an unbound form control in a continuous form using recordsetclone

I've got a continuous form that has three unbound textboxes, named "linked_swimmer1", "linked_swimmer2" and "linked_swimmer3" on. The form is a list of contact names and I am looking to display the people these contacts are linked to and but only show the unbound textboxes where there are...

www.access-programmers.co.uk

 

[CJ_London]

Perhaps this is what you are looking for?

A multi select form using unbound check boxes

Thought I would put this up since it is a fairly common topic and I have put it up elsewhere - selecting records from a list for onward processing without the need for a table based tickbox. Similar in concept to the conditional formatting version I provided here...

www.access-programmers.co.uk

 

[DHookom]

Have you considered a multi-select list box rather than a form?

 

[Ken Sheridan]

Like Duane, I'd suggest that you open a dialogue form containing a multi select list box, whose RowSource restricts the rows returned to those which reference the parent form's key used as the subforms LinkMasterFields property. The following is an example of the sort of code in the dialogue form's module to open a report:

Private Sub cmdOpenReport_Click()

    Dim varItem As Variant
    Dim strWhereClause As String
    Dim ctrl As Control
   
    Set ctrl = Me.lstContacts
   
    If ctrl.ItemsSelected.Count > 0 Then
        ' if at least one item has been selected in the list box
        ' loop through the control's ItemsSelected collection
        ' and build a value list of ContactID values
        For Each varItem In ctrl.ItemsSelected
            strWhereClause = strWhereClause & " OR Contacts.ContactID = " & ctrl.ItemData(varItem)
        Next varItem
       
        ' build WHERE clause for report's query
        strWhereClause = " WHERE " & Mid(strWhereClause, 5)
        ' open report in print preview, passing WHERE clause into report
        DoCmd.OpenReport "rptContacts", View:=acViewPreview, OpenArgs:=strWhereClause
    Else
        MsgBox "No contacts selected", vbInformation, "Warning"
    End If

End Sub

The code builds a value list of the selected values from the list box's BoundColumn, in this case the hidden ContactID column, and passes the list to to the report as the OpenArgs argument of the OpenReport method. A WHERE clause is then added to the report's RecordSource query in the report's Open event procedure:

Private Sub Report_Open(Cancel As Integer)

    ' add WHERE clause to report's query
    Me.RecordSource = Replace(Me.RecordSource, ";", "") & Me.OpenArgs & ";"
   
End Sub

This assumes that the report's RecordSource query is not already restricted by a WHERE or HAVING clause. If the query is already restricted by a WHERE clause then it would be a simple matter to amend the code which opens the report so that the OpenArgs argument begins with AND rather than WHERE. If the query aggregates data on the other hand and is restricted by a HAVING clause, then it would be trickier, as the WHERE clause would have to be inserted into the query before the HAVING clause. In that case there are other methods which could be used, referencing a hidden control in the form as a parameter.

If you want to stick with a check box, however, then the simple solution is to add a Boolean column to the relevant table, and restrict the report on that column. In the parent form's Current event procedure execute an UPDATE statement to set the Boolean column to FALSE in every row.

 

[MajP]

Manipulating an independent check box field in a form

Hello! I have a form that its details is fill with fields from a query. I would like to have an independent check box field for each detail that I would like to change. I create the field in the form but when I change it on one record it chages all the records. I am thinkig to create a temporal...

www.access-programmers.co.uk

There are several different approaches here.

 

[andymartin3186]

true"]https:/ww.access-programmers.co.uk/forums/threads/a-multi-select-form-using-unbound-check-boxes.323395/[/URL]

Thanks, this worked great.

The only bit that got me was the setfocus to "GF" line of code. Turns out "GF" was a text box but was 0cm x 0cm so couldn't see it anywhere but it wasn't hidden.
I added one and it worked great.

 

[Gasman]

Look in the control list for the form?

 

[CJ_London]

yes it's 0 x 0cm so cannot be seen - but needs the visible property as true to be able to receive the focus. It's a method I use when I need to do something to a control that has the focus - simply move the focus elsewhere. GF is just a name which means 'Got Focus' or 'Gets Focus'.