Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Using a CheckBox list to create the appropriate Chil recordsHelpful Member! 

bartus991 (Instructor) (OP)
27 Feb 10 16:10
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.

 
Helpful Member!  MajP (TechnicalUser)
27 Feb 10 18:43

Quote:


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 (TechnicalUser)
27 Feb 10 19:13
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 (Instructor) (OP)
3 Mar 10 14:11
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 (TechnicalUser)
3 Mar 10 14:22
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?
bartus991 (Instructor) (OP)
4 Mar 10 14:04
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 (TechnicalUser)
4 Mar 10 16:46
Do you have a question?  Is there something specifically that you having trouble with?


Quote:


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 (Instructor) (OP)
5 Mar 10 15:08
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 (TechnicalUser)
8 Mar 10 8:47
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 (Instructor) (OP)
9 Mar 10 12:04
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 (Instructor) (OP)
9 Mar 10 13:06
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:

CODE

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 (TechnicalUser)
9 Mar 10 14:11
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 (Instructor) (OP)
12 Mar 10 14:16
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:

CODE

 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 (TechnicalUser)
12 Mar 10 16:50
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 (Instructor) (OP)
13 Mar 10 16:07
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!


 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close