Using a CheckBox list to create the appropriate Chil records 1

[bartus991]

Access 2007 has a great new feature with the multiple value field, unfortunately you can not access these ‘childrecords’ to add more fields or add another childrecord.

For an already developed theatre reservation system they would like to create pricingsets. Which mean that for each show you select the appropriate pricingset.

Now I have created the following tables:

tblRank
- RankId
- Rank (dress-circle for example)

tblPricingTypes
- PricingTypeId
- PricingTypeName

tblDaySelection
- DaySelectionId
- DayName
- checkbox field for the days (Monday, Tuesday, Wednesday etc.)

tblTimeSelection
- TimeSelectionId
- TimeName
- Start Time
- End Time

Above tables are needed to create the pricing set, which have the following main table:

tblPricingSet
- PricingSetId
- PricingSetName
- Valid From Date
- Valid to Date

The second level of this table will contain the Pricingtypes:
tblPricingSet_PricingTypes
- PricingSet_PricingTypesId
- FKPricingSetId
- FKPricingTypeId
- Details of a group or combo ticket.

After creating the ticketset is created on a form I would like to have a kind of listview, which looks likes the multiple value field in Access 2007 to create the appropriate child records.
This list contains all the rows of the tblPricingTypes and simply to check or uncheck a checkbox the child record will be created or deleted.

After choosing the right Pricing Types you will have the following listviews with checkboxes to choose the:
- RankSelection
- DaySelection
- TimeSelection

For each dayselection you can choose the different timeselections. The Rank will be added to all fields. Which gives you the following hierarchy:
- Pricing Set
- - Pricing Type
- - - Day Selection
- - - - Time Selection
- - - - - Rank

So I was thing to do above in one table as a child record of the tblPricingSet_PricingTypes:
tblPricingSet_PricingTypesSub
- PricingSet_PricingTypesSubId
- FKPricingSet_PricingTypesId
- FKDaySelectionId
- FKTimeSelectionId
- FKRankId
- Price

Hopefully someone can help me to create these kind of listviews with checkboxes. The create the appropriate childrecords. I do not want to create each record with a dropdownbox (already have it) as it take more time to check if all the pricingtypes are added. A listview with a checkbox gives you a much better overview.

I have tried to use an unbound check box on a continues form, but this won’t work.

This method is based on another system also in made in access which have above possibility, but I’ve only seen some screenshots, and the developer won’t tell me how it works.

For each show we run a query to give an overview of the possible prices per rank.

 

[MajP]

Access 2007 has a great new feature with the multiple value field,

No. Access allows you to do really dumb things, and pay for it in the end. These are one of them. Unless I need to be compatible with a Sharepoint list that requires these, I would avoid it.

But making listviews with checkboxes is pretty easy.

http://www.4shared.com/file/154413177/1b92333f/listviewDemo.html

But here is another idea. You could build popup commandbars with checkboxes. I will make another demo for the checks. But this is pretty cool. Just learned how to do this.

http://www.4shared.com/file/217898219/b4aa23fe/ShortCutMenuControl_V2__2003_.html

 

[MajP]

Take a look at this demo

http://www.4shared.com/file/231188165/cb810d74/ShortCutMenuControl_V3_With_Ch.html

Click the demo button, and then select beverages. This will show you pop up menus with checkboxes. The nice thing about these is you can build them once and use them everywhere.

 

[bartus991]

If looked at your demo's and they are looking great! And I will definatly use this in my database.

But for the part I am building now I'm not convinced yet. As we want a simple overview which records have been created and which not. For this we would like to have a listview. If a record has been created the check box is checked if there is no matching record for the value the checkbox is unchecked.

I'll try to explain with a little example, the will have a tabcontrol.

Rank
Here you have a list of all the possible ranks (for example dress-circle). You select (or deselect) all the lines which you need (or not)

Day and Time
In the first list you will select a daycombination, which will update the second list with all the programmed timeperiods for this day. Checking or unchecking the checkbox of the time list will add or delete the timeperiodId including the above Dayselection Id. So the time list will work as a kind of sub of the Daycombination list.

Then when after clicking on save, or when closing the form.
The selected ranks will be added to all the records, or when uncheck will be deleted.

Now where working with different subforms, and as you do not have a simple list overview with checkboxes sometimes the pricingset isn't programmed correctly. It will take a lot of time to check if everything is correct and a listview with checkboxes will avoid this.

So when opening the form the listview have to retrieve which values are already added in the pricing set and the check box has to be checked.

 

[MajP]

I provided a demo of a listview with checkboxes. I am not sure what your questions are. It sounds like a listview will work and you just have to figure out what to load and what actions occur when you check and uncheck an item. Do you want something specifically demoed?

 

[MajP]

http://www.4shared.com/file/233750574/de76ba15/listviewDemo_CheckBox.html

 

[bartus991]

Thank you for your last upload!

We have created a small plan how it would look like. Please see at the following link a small desciption of what I would like to create:

http://www.4shared.com/file/234322431/8dad89aa/Creating_a_ticketset.html

Sorry that the used pictures are in Dutch, but hopefully I can get things clear now.

Thanks to a previous post, I can work with the treeview. But using the listboxes with checkboxes to create the appropriate records ain't so easy.


For each dayselection you can select the applicable timeselection. At the end the selected Ranks will be applicable to all the timeselections.

I was thing to use the following table for this part:
tblPricingSet_PricingTypesSub
- PricingSet_PricingTypesSubId
- FKPricingSet_PricingTypesId
- FKDaySelectionId
- FKTimeSelectionId
- FKRankId
- Price
- Etc...

There will also be an option to copy to entire pricing set, so I thought that it was better to decrease the use of childrecords.

Hope you understand what I mean!

 

[MajP]

Do you have a question? Is there something specifically that you having trouble with?

But using the listboxes with checkboxes to create the appropriate records ain't so easy.

I showed how to do it generically, so you should just modify the insert queries.

 

[bartus991]

I have created yesterday the listbox which creates the subrecords of the Pricingtypes.

But the description the differents list to create the correct records for the Day/Time/Rank selection is difficult. So here are questions:

How do I get the Time selection list box as a sub of the Day selection?

The Day Selection will not create any records, It will the checkbox active will only be shown as if there is a time selection for this date.

The Rank box won't create any records untill the form is closed. Where do I store the selected value's, untill the insert queries will run?

Can I add to the list box a column Active, with the checkboxes in that column?

 

[MajP]

I have not had time to demo this, but if it was me I would do this differently than using a listview. You could do it that way, but it would require lots of code to synchronize the list boxes.

This is how I would do it.
1)In each of your "lookup" tables:"Rank, Day, Time" add a boolean field "SelectedRank, SelectedDay, SelectedTime"
2)Now build a sub form that uses this boolean field as a checkbox (like your demo). Format to look like a listview.
3) Now look at my demo of populating the listview. After I build the listview I
a. Read the data table and determine which values are in the table.
b. For selected values in the data table I check the box
c. When I check a listview item I run an insert query in the data table
d. When I uncheck I run a delete query
4) Now you can use these subforms the same way, but you can use the power of subforms to link together.
5) So you would do something like
a Select a specific pricing set
b run update queries to clear out all "selected" booleans
c read through the pricingSetSub to populate the "lookup" tables. You can do this with update queries. Example: if the fkDaySelectionID is monday then set DaySelected = true for monday
d Make your selections
e. When all done either use recordsets or update queries to update your data table with the boolean selected values.

I have used this trick many times and it works well. This gives you persistent values in your selected fields, but you can update your data table at the end.

 

[bartus991]

Thanks for your reply, it is very usefull for me and I will try to get everything work. I was already experimenting with subforms but was using an unbound checkbox, which didn't work.

 

[bartus991]

Haven't quite figure out how to load the values, but I will keep on trying.

Just another question. Above is based on a single checkbox, but can this also be done, using two checkboxes, for example:

Terminal    Reservate    Book
POS 1          V            
POS 2          V          V
POS 3
etc...

And I guess It will also allow me to add fields that can be filled in. Not applicable for above but for a form that has to be developed.

 

[MajP]

Yes. The boolean field is just a place holder.
1) You select the applicable choices.
a. you can either insert and remove records from your data table as you click or on click
or
b. When all done loop through the recordset and make your updates
something like

dim rsCheckForm as dao.recordset
dim rsData as dao.recordset
set rsCheckForm = forms("frmName").recordset
set rsData = currentdb.openrecordset ("yourdatatable,dbopendynaset)

do while not rsCheckForm.eof
if rsCheckForm!blnReservate then
rsData.addNew
rsData!someField = rsCheckForm!Terminal
rsData.update
end if
if rsCheckForm!blnBook then
rsData.addNew
rsData!someOtherField = rsCheckForm!somefield
rsData.update
rsCheckForm.moveNext
loop

2) Once you figure out how to writ to a data table based on items selected in the subform, you have to figure out the reverse of selecting records based on values in the data table. I demonstrate this in the listview. When you open the listview it checks the items that exist in the data table.

 

[bartus991]

Another question. I have made the continues form, and all the checkboxes will be checked or unchecked correctly, using the code you had for the listview. Works great.

I would like to use a update button that will add or delete the selected or deselected records.

Some how the above code doesn't quite work. As it wil add some records, but not all the selected records that have a checkbox. It look likes this code will not check every record on the form but only some. It has to do something with the focus I guess.

I was thiking to use a code like:

 Dim rsCheckForm As dao.Recordset
 Dim rsData As dao.Recordset
 Set rsCheckForm = CurrentDb.OpenRecordset("tblPrijstype", dbOpenDynaset)
 Set rsData = CurrentDb.OpenRecordset("tblTicketset_Prijstypes", dbOpenDynaset)
 
    Do While Not rsCheckForm.EOF
    If rsCheckForm!SelectedPrijstype = True Then
        If IsSelected(rsCheckForm!PrijstypeId) Then
        
        Else
            rsData.AddNew
                rsData!Prijstypes = rsCheckForm!PrijstypeId
            rsData.Update
        End If
    Else
    
    End If
    rsCheckForm.MoveNext
    Loop

But this didn't worked either. The code isn't quite finished, the delete part had to be added. But It wont add all the records als selected on my form.

 

[MajP]

A couple of observations:

I would think the recordset you want for rsCheckForm is the form's recordset. Something like

set rsCheckForm = forms("frmMain").subFrmProjectType.form.recordset

It looks like you are using the table that the form is based on.

Put some debug.prints

Do While Not rsCheckForm.EOF
debug.print rsCheckForm!prijTypeID & " " & rsCheckForm!SelectedPrijstype
If rsCheckForm!SelectedPrijstype = True Then
If IsSelected(rsCheckForm!PrijstypeId) Then
debug.print rsCheckForm!PrijstypeId & " is Selected"
Else
rsData.AddNew
debug.print "Add new data: " & rsCheckForm!PrijstypeId
rsData!Prijstypes = rsCheckForm!PrijstypeId
rsData.Update
End If
Else

End If
rsCheckForm.MoveNext
Loop

Review the results and see what is happening. Focus is not an issue when dealing with recordsets only controls. If you post a demo it will be easier for me to verify what the code is doing.

 

[bartus991]

Hereby the Demo database in which I am testing the forms code to create a ticketset.

http://www.4shared.com/file/240551727/f7d51e6e/Test_Ticketsets.html

The form TicketsetFrame is the main form. Via the treeview (code not finished, but It will open load the correct subform (not the correct record) the user is able to create or update a ticketset.

The first form after creating the ticketsset is the form TicketsetModify. On the second tabcontrol the first 'listview' is created. The code will check the appropriate buttons. But haven't get the code right to add or delete the records in the table: Ticketset_Prijstypes.
It must be something like: if checked and record has not been created yet then add, if unchecked and record has been created then delete. The code will be running after clicking the update button.
In this demo version, the code is deleted as it wasn't working anymore.

When clicking on the 3rd level in the treeview the subform TicketsetModifySub is loaded (their is rs.findfirst mistake in the code. Haven't fixed it yet).
On this form the follwoing will be selected with a sort of listview.

Rangen
Profitcenter
Dag/tijd
Terminaltype/Betalingswijze (has to be created)

On the bottom of this form the Save button is located.
The follwoing code has to be programmed.

The checked items of Profitcenter will be added or deleted if unchecked in the table Ticketset_Prijstypes_ProfitCenter.

Then, the difficult part:
On the tab Dag/Tijd
The first list is the Day list on which the Time list has to be depending. For each day different times can be created.

Then the Rank (can be multiple) have to be added for each record and eventually added (or deleted) to the table: Ticketset_PrijstypesSub on which the entire ticketset is running.

This last part hasn't been coded yet, as i was trying to get the code on the TicketsetModify form correct.

Thanks for your help!