Using Checkbox control to create new record or delete record

[gemadan96]

I have a table for Registrations and a form associated with it. I would like to have a table for Registration Details. On the registrations form my thought is to have a subform for the Registrations Details. I would like to have the subform have checkbox controls where each control represents an event that can be registered for. When a checkbox is selected it would add a record to the Registration Details table related to that registration, however if the checkbox is deselected I want the record to be deleted.

Basically this is similar to a table of Orders and a table of Order Details. At the moment there will be about 16 checkboxes that can be selected. Afterward the two tables will be used to group the registrants into predetermined groups based on the events selected.

My thought is that the Registration Details table will have three fields as follows: DetailID (Primarky key), RegID (relating to the Registration table), and Event.



Is this possible, and if so how do I go about doing it?

[June7]

Why a checkbox to trigger record creation? Why not just enter data in subform New record row?

Why delete record? Why not just have a field that changes status of the record?

Adding and deleting with code would require running APPEND and DELETE sql actions or for delete DoCmd.RunCommand acCmdDeleteRecord

[gemadan96]

The idea of the check box was to make the data entry for the user simple and quick. I did think about having a field for the status of the record and that is simple enough. Is there a way when a new registration is added that it can prepopulate the registration detail table with an entry for each event for the registrant? Then the subform could display a datasheet listing the events and showing the status which can be changed.

[June7]

Yes. I don't know your data structure well enought to be specific, but something like:

If you just want a single event added per the specific button clicked:
CurrentDb.Execute "INSERT INTO RegDetails(RegID, RegistrantID, EventID) VALUES(" & Me.RegID & ", " & Me.RegistrantID & ", Event1"

Options to add all events en masse:

1. INSERT SELECT sql action (APPEND query) then refresh or open form to this set of records, something like:
CurrentDb.Execute "INSERT INTO RegDetails(RegID, RegistrantID, EventID) SELECT " & Me.RegID & " As RID, " & Me.RegistrantID & " AS RtID, EventID FROM EventsTable;"

2. loop code that creates new record in the subform with DoCmd.GoToRecord , , acNewRec and populates fields - probably need to open a recordset of the Events table for this approach

Then
Me.Refresh
or
Me.Requery

Whatever the method, can get complicated. The more 'user friendly' the more code.

[gemadan96]

I have the following:
Tables - Registration, Registration Details, Events
Queries - Add Events (append query to add events to Registration Details Table)
Forms - Registration (to view, modify, add registrations and details)

I have a button control on the Registration Form to add the events by running the append query. I also need that button to set the value of the RegID in the Registration Details table equal to the RegID of the form.

I have attached a copy of the database.

[June7]

You have Yes/No fields in Registrations table to select events then you also want the selected events saved as individual records in the Registration Details table.

I have no idea if this can be done with macros. I would use VBA with looping code to look at value of each checkbox and save record as appropriate. Method I will describe requires checkboxes to have similar names, like Event1, Event2, Event3, etc. The sequence number in the name should agree with the event's EID in Events table. You would have to change the Event Class field in Registration Details to number type so the EID value could be saved. If you really want to save the Event Class description then code will be a little more complicated.

Dim i As Integer
For i = 1 to 18
If Me.Controls("Event" & i) = True Then
CurrentDb.Execute "INSERT INTO [Registration Details](RegID, Event) VALUES(" & Me.RegID & ", " & i & ")"
End If
Next

Nothing in that code will prevent duplicate records by repeatedly clicking the button. Also, does not work for adding and removing events if they are checked/unchecked later. If you want finer control then instead of the loop code behind button, put code in AfterUpdate event of each checkbox to perform appropriate action to save/delete record for just that event.

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. Better would be RegistrationDetails or Reg_Details, EventClass or Event_Class.

[gemadan96]

The Yes/No fields are from the clients data. I will be eliminating those fields once the data has been duplicated in the RegistrationDetails table.

At this time the goal would be that when a new registration is created then all the available events in the Events table would be appended to the Registration Details table and associated to the RegID. The current Event subform will be replaced with the Registration Details subform showing the Events and Status columns. Then the data entry person can check of those events that participant is registering for. There won't be a need to delete in active Event Records until after the registration is closed. That I can do with a delete query.

If I need to use a button then it would be great if that only appeared on the form when it is a new registration.

[June7]

Okay, to add a complete set of events to new registration:

CurrentDb.Execute "INSERT INTO [Registration Details](RegID, [Event Class]) SELECT " & Me.RegID & " AS ID, [Event Class] FROM Events"

Have code in the Open or Current event of Registration Details to make Add Events button available only when new registration. Maybe:

Me.AddEvents.Visible = IsNull(Me.CompID)

Again, spaces not good in names, note that I removed it from AddEvents button name.

[gemadan96]

The code to make the button visible only for new registrations works. The code to insert the events into the RegistrationDetails tables does nothing. It doesn't even give me an error. I did create and AddEvents append query. If there is a way to run the query and set the value for RegID in the RegistrationDetails table for the RegID of the open form that would accomplish what I'm looking for.

[June7]

You tried CurrentDb.Execute code?

I don't use Access query objects for sql actions, I use sql in VBA, as in my example. It works for me. If you tried to create an Access append query, show the sql statement of that query.

[gemadan96]

Tried the CurrentDb.Execute code. Copy and pasted what you provided. Now I receive an error: Run-time error '3601: Too few parameters. Expected 1.

[June7]

I left 's' off Events in my posted code which I have since corrected. If it still won't work, provide your revised db for analysis.

[gemadan96]

Ok I played around with it some more and got it working. I added code to requery the subform so the events show up.

CurrentDb.Execute "INSERT INTO [RegistrationDetails](RegID, [EventClass], [DivisionClass]) SELECT " & Me.RegID & " AS RegID, [EventClass], [DivisionClass] FROM Events"
Me.[Registration Details subform].Form.Requery

Thanks.