Store CheckBox data in searchable manner

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My users prefer to see a list of some 60 [Services] check boxes rather than
use a subform of combo boxes with the same list of [Services] (my
preference). This makes creating search forms very cumbersome and requiring
many queries and isn't very updatable. I wrote some code to add the record Id
and a Service Id (from a table) to a join table, like so:

Select Case chk.Name
Case "chkDD"
idServ = 1
Case "chkEdu"
idServ = 2
Case "chkHousing"
idServ = 3
etc.

If chk = True Then ' Update tbl_ServiceProvided if box checked.
Set rs = tbl.OpenRecordset
With rs
.AddNew
!ResourceId = Forms!RESOURCE!ResourceId
!ServiceId = idServ
.Update
End With
rs.Close
End If

If chk = False Then ' Delete any record created by above If
statement if box unchecked.
Set rs = tbl.OpenRecordset
If rs!ResourceId = Forms!RESOURCE!ResourceId And rs!ServiceId =
idServ Then
rs.Delete
End If
rs.Close
End If

However, this creates substantial complexity in the data entry form, namely,
new records must be saved before the Service checks can be used. So I put
them on a subform and put a Save macro on the subforms OnCurrent, but this
slows down browsing whenever I apply or remove a filter - not to mention I
can't filter the subform checks like I could when they were all on the same
form.

One solution: put code on the Before Update of all the checks that somehow
checks whether the record has been saved, then either saves on its own or
prompts the user to save?
 
Brad,

Let me see if I understand your situation:

tables: Record MapRecordServices ServiceCatalog
RecordID RecordID ServiceID
ServiceID

Then, you want to allow users to add/remove services on a per applicant
basis, by clicking on checkboxes to indicate if a service is required.

The problem you're having is that there is other record data on the form
(like a name field), and if someone uses the form to insert a new
record, the main form data needs to be written to the "Record" table
because RecordID needs to exist in Record before you can insert it in
MapRecordServices.

If this is correct, I guess what I would do is disable automatic
MapRecordServices entry when you're on a new record. Just bracket all of
the "AfterUpdate" events with an

If Not Me.NewRecord Then
End If

So it won't try and add records until the record is no longer a new
record. Then, on the form's "AfterInsert" event, which is called after
someone saves a new record, you can iterate through the check boxes and
add mapping records for each one "true" checkbox.

If I've gotten your situation wrong, let me know.

Good luck,
Luke
 
Create a button labeled "Select Service" and have a macro open a second form.

On the second form (ServicesForm) create button. Have it call a macro
"Service01". Copy and pastse as many times as you have services. Label each
button with it's service. Edit each button macro such as Service02,
Service03, Service04, etc.

Create a macro Service01 to SetValue of the service field of your form to
the ServiceCode. Second line of macro closes the ServicesForm. Copy the
macro as many times as you have services. Edit macro name and set value for
each.
 
My thanks to you quick responders. Part of my problem was allowing _users_ to
enter new records. Using If Not Me.NewRecord, I've been able to save any new
records to the table before the other code runs.

A pop-up form was my other idea, and it's good to know other programmers
think like me.

Thanks again.
 
Back
Top