Linda Nichols
asked on
How to keep multiple selections from a combo box together on an Access report
I am creating a report, based on a query that has many tables. Everything is working great, except that the information from multi-select combo boxes are carrying over to a separate page, instead of keeping in line with the other responses. I've managed to stop every field from duplicating just because there are multiple responses to this control, but what I want is for the report to look like this (what I am having trouble with is the Sections):
Township/Range 17N 3W Sections 1,2,3,8,9
18N, 3W 31,32
My main issue is that I don't know what I am asking for. Sorry!!!
Township/Range 17N 3W Sections 1,2,3,8,9
18N, 3W 31,32
My main issue is that I don't know what I am asking for. Sorry!!!
also set the Keep Together property of the section to True (Yes)
ASKER
I set the Hide Duplicates to Yes on the property sheet of the report.
thanks
is Sections a multi-value field?
btw, 'Sections' is a reserved word and should not be used as a name:
http://allenbrowne.com/AppIssueBadWord.html#S
also, best to create a table for multiple values rather than use a MV field, which is very hard to control
if you are worried because you already have a lot of data, I have code that will take a day or 2 to post that will do the separation for you. You will have to tell me a bit about your structure.
Also have code to loop and combine values so you can still get, ie, "1,2,3,8,9"
is Sections a multi-value field?
btw, 'Sections' is a reserved word and should not be used as a name:
http://allenbrowne.com/AppIssueBadWord.html#S
also, best to create a table for multiple values rather than use a MV field, which is very hard to control
if you are worried because you already have a lot of data, I have code that will take a day or 2 to post that will do the separation for you. You will have to tell me a bit about your structure.
Also have code to loop and combine values so you can still get, ie, "1,2,3,8,9"
ASKER
Just to clarify, I set the hide duplicates for each control that it pertains to. I tried setting the Keep together on the Township/Range Sections controls, but then the whole sheet duplicates for just a change in section. I couldn't find where it says "true." I have Access 2016 and Keep whole group together on one page is a selection
Keep Together would be set for the section that the controls are in -- not for each control. True and Yes are the same thing. In code, Yes would be True or -1.
thanks, Linda -- also please read my last comment if you still have issues
thanks, Linda -- also please read my last comment if you still have issues
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! I will try this out. I need to get my report done for tomorrow, sigh.
you're welcome
> "I need to get my report done for tomorrow, sigh."
no problem, Linda ... and now I realize my "run" procedure doesn't exactly have good names ... copied what I used for something else -- just put the code into a new module, Debug, Compile, and Save. Then modify values in the "run" procedure -- Debug, compile, and Save again. then click in the "run" procedure and press F5 to test. Once you are comfortable with the parameters, you can make the substitution in the SQL (for query or record source).
Made a couple substitutions in the "run" procedure to help: ********** ********** ********** **
*** How to Create a Standard (General) Module ***
Press Alt-F11 to go to the VBE (Visual Basic Editor)
From the menu in a the Microsoft Visual Basic window:
Insert --> Module
once the code is in the module sheet, from the menu, do -->
Debug,Compile
if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good ;)
Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc
~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
> "I need to get my report done for tomorrow, sigh."
no problem, Linda ... and now I realize my "run" procedure doesn't exactly have good names ... copied what I used for something else -- just put the code into a new module, Debug, Compile, and Save. Then modify values in the "run" procedure -- Debug, compile, and Save again. then click in the "run" procedure and press F5 to test. Once you are comfortable with the parameters, you can make the substitution in the SQL (for query or record source).
Made a couple substitutions in the "run" procedure to help:
Sub RunLoopAndCombine()
'test LoopAndCombine
Dim sTablename As String _
, sIDFieldname As String _
, sTextFieldname As String _
, nValueID As Long _
, sFieldSortBy As String
sTablename = "MyTablename"
sIDFieldname = "MyNumericForeignKeyFieldname" 'if FK is not a n umber, you will need to add delimiters to LoopAndCombine where it is referenced
sTextFieldname = "Description of Fieldname"
nValueID = 138 'some number you know is in the table
sFieldSortBy = "FieldnameToSortBy"
MsgBox LoopAndCombine(sTablename, sIDFieldname, sTextFieldname, nValueID, , , , sFieldSortBy)
End Sub
***************************** How to Create a Standard (General) Module ***
Press Alt-F11 to go to the VBE (Visual Basic Editor)
From the menu in a the Microsoft Visual Basic window:
Insert --> Module
once the code is in the module sheet, from the menu, do -->
Debug,Compile
if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good ;)
Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc
~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
Option Explicit ' require variable declaration
If you have more questions, we can help ... you will get there :)
> "I've managed to stop every field from duplicating"
how was that done?