List records sorted into category groups with subheadings

[KiwiGenie]

I have a form which lists all records for the user to choose which ones to print. Its a continuous form with a checkbox for each record, bound to a "selected" yes/no field in the table.This works well for what it is, however I really want to split my list into categories. I can do this in reports but don't know how in forms and searching has been unsuccessful.
In my table I have fields for FoodCategory, RecipeName, Selected, and some others. I want to make a list like:

FoodCategory1
--RecipeName1
--RecipeName2
FoodCategory2
--RecipeName3
etc...

I want to be able to select multiple RecipeNames and even if possible to be able to select a whole FoodCategory at once.
Please can someone help with this?
Jenny

[FishVal]

I have a form which lists all records for the user to choose which ones to print. Its a continuous form with a checkbox for each record, bound to a "selected" yes/no field in the table.This works well for what it is, however I really want to split my list into categories. I can do this in reports but don't know how in forms and searching has been unsuccessful.
In my table I have fields for FoodCategory, RecipeName, Selected, and some others. I want to make a list like:

FoodCategory1
--RecipeName1
--RecipeName2
FoodCategory2
--RecipeName3
etc...

I want to be able to select multiple RecipeNames and even if possible to be able to select a whole FoodCategory at once.
Please can someone help with this?
Jenny

Hi!

I would like to suggest you the following interface:

MainForm (Food Categories)
--Subform (Receipe Names)

Subform contains checkbox you've mentioned.
MainForm contains unbound checkbox which when updated updates checkboxes in underlaying subform.

If this is what you want I will help you with design and coding.

[KiwiGenie]

Hi..thanks for the reply. I am still very much an access newbie and I'm not sure I understand how the checkboxes would work, but it really got me thinking and I wondered would something like this work:
Unbound main form
--Combo box - FoodCategory
--Listbox - Recipe names matching chosen category, hopefully would be able to include an "all" option to select a whole category.

Then when a recipe name is clicked on in the listbox, a subform datasheet shows up listing selected recipe names. Then I can see which ones will be printed, maybe if possible choose to unselect some, then preview or print.
Hope I'm making some kind of sense ;) I haven't had time to try out much of this yet, so far I have the combo box working to fill the listbox. Any more ideas or guidance very much appreciated :)

[KiwiGenie]

Just to update I have added the subform now and added the following code to the listbox:

Private Sub RecipeName_Click()
 
Dim strSelected
 
strSelected = strSelected & "SELECT * FROM tblRecipes WHERE tblRecipes.RecipeName ='" & Me!RecipeName & "'"
Me.SubfrmSelected.Form.RecordSource = strSelected
 
End Sub

This works if I click on one recipe name from the listbox, but if I click on another the first name is replaced by the second. How can I get it to remember all the recipe names that I've clicked on. (I will have a "clear" button somewhere to clear the subform). I made the subform an unbound form, is this the problem?

[FishVal]

Just to update I have added the subform now and added the following code to the listbox:

Private Sub RecipeName_Click()
 
Dim strSelected
 
strSelected = strSelected & "SELECT * FROM tblRecipes WHERE tblRecipes.RecipeName ='" & Me!RecipeName & "'"
Me.SubfrmSelected.Form.RecordSource = strSelected
 
End Sub

This works if I click on one recipe name from the listbox, but if I click on another the first name is replaced by the second. How can I get it to remember all the recipe names that I've clicked on. (I will have a "clear" button somewhere to clear the subform). I made the subform an unbound form, is this the problem?

Hi!

I was somewhat busy. Sorry.
I've read your posts. It is not very difficult to implement an interface you like. But first of all to avoid misunderstanding plz provide the tables structure including field types and PK / FK relationships (I mean particulary table of RecipeNames and table of FoodCategories).

P.S. The code you've posted is supposed to do exactly what you are getting (each time executed it replaces subform RowSource with a new query returning one recipe name).
The easiest way to do multiple selection is to update Y/N field in tblRecipes you've mentioned in msg#1.
SQL:
UPDATE tblRecipes SET tblRecipe.IsSelected = Yes
WHERE tblRecipes.RecipeName="'" & Me!RecipeName & "'";
Now you can easily require to the subform an report records where "tblRecipe.IsSelected = Yes" and require records where "tblRecipe.IsSelected = No AND tblRecipe.FoodCategory="&Me!FoodCategory to the Listbox.
A simple update query
UPDATE tblRecipes SET tblRecipe.IsSelected = No;
will clear selection on user request.

P.P.S. Anyway to make my help more certain plz provide tables structure. To provide current form design and code would be nice as well. ;)

[KiwiGenie]

Hi..First off, no worries about any delays, I get busy too! Besides I've had time to play round and learn a bit more about what I'm trying to do.

P.S. The code you've posted is supposed to do exactly what you are getting (each time executed it replaces subform RowSource with a new query returning one recipe name).
The easiest way to do multiple selection is to update Y/N field in tblRecipes you've mentioned in msg#1.

I came to that conclusion last night after trying it with the listbox on multi-select, and using the ItemsSelected property. I got this to work, but when I changed categories in the combobox then clicked on a recipename the previous selections would disappear from the subform. What I have now appears to work (so far)...
Here are my relevant table structures:
tblRecipes:
-RecipeID - AutoNumber (PK)
-RecipeName - Text
-FoodCategory - Text (FK to tblFoodCategories.FoodCategory)
-Source - Text
-Instructions - Memo
-Selected - Y/N
tblFoodCategories:
-FoodCategory - Text (PK)

And my current form design etc:
Main form
-FoodCategory (ComboBox with RowSource = SELECT tblFoodCategories.FoodCategory FROM tblFoodCategories;)

Private Sub FoodCategory_AfterUpdate()
 
    If IsNull(Me!FoodCategory) Then
      Me!Subfrm1.Form.RecordSource = ""
    Else
      Me!Subfrm1.Form.RecordSource = "SELECT * FROM tblRecipes WHERE tblRecipes.FoodCategory ='" & Me!FoodCategory & "'"
    End If
   Me.Requery
End Sub
 

-Subfrm1 (Continuous form)
--RecipeName - TextBox - ControlSource = RecipeName
--CheckSelected - Checkbox - ControlSource = Selected

Private Sub CheckSelected_Click()
  Me.Parent.SubfrmSelected.Form.RecordSource = "SELECT * FROM tblRecipes WHERE tblRecipes.Selected = -1;"
  Me.Parent.SubfrmSelected.Requery
End Sub

-SubfrmSelected (Datasheet Form)
--RecipeName - TextBox - ControlSource = RecipeName
--FoodCategory - TextBox - ControlSource = FoodCategory

Thank you for your help in getting this far, hope its clear what I have so far, and sorry for the long post!
Jenny

[KiwiGenie]

Ok, something is wrong already, when I select the first recipe, nothing happens in the subform, then when I select another recipe, the first one shows up, select a third and the second shows up...
Any ideas?

[FishVal]

Ok, something is wrong already, when I select the first recipe, nothing happens in the subform, then when I select another recipe, the first one shows up, select a third and the second shows up...
Any ideas?

Ok. Its completely normal behaviour as you try to requery subform with selected recipes while the record you've changed is not yet saved in the table (pencil sign on a record selector).

I propose the following.

• you should handle changing of the CheckBox on AfterUpdate event
• you should first of all force the current record to be saved in db (e.g. "Referesh" method)
• the row in CheckSelected_Click sub there you change subform recordsource is not actually needed as you don't change anything :)

Below is an example of code

Private Sub CheckSelected_AfterUpdate()
Me.Refresh
Me.Parent.SubfrmSelected.Requery
End Sub
 

Good luck.