Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to keep multiple selections from a combo box together on an Access report

Posted on 2016-08-27
11
Medium Priority
?
38 Views
Last Modified: 2016-10-15
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!!!
0
Comment
Question by:Linda Nichols
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 22
ID: 41773693
it can be done with code but it would be much easier and much better for you to use a related table to store the choices instead of using a multi-select field

> "I've managed to stop every field from duplicating"

how was that done?
0
 
LVL 22
ID: 41773709
also set the Keep Together property of the section to True (Yes)
0
 

Author Comment

by:Linda Nichols
ID: 41773764
I set the Hide Duplicates to Yes on the property sheet of the report.
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 22
ID: 41773766
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"
0
 

Author Comment

by:Linda Nichols
ID: 41773772
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
0
 
LVL 22
ID: 41773773
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
0
 

Author Comment

by:Linda Nichols
ID: 41773782
I have it set up as a separate table to store just the townships and sections.  Not sure what to change the name Sections to, as it refers to the actual geographic area.   Here is a screenshot of how the table receives the data:
TRS-.jpg
0
 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41773799
great!! change Section to Sectn ... just something different that a human can still understand ;)

here is code to loop through a recordset and combine values, and sort them, that you can call. The first routine can be changed for what you want so that you can test it. The secondroutine is what you would call in SQL:
 '~~~~~~~~~~~~~~~~ RunLoopAndCombine
Sub RunLoopAndCombine()
'test LoopAndCombine
   Dim sTablename As String, _
   sIDFieldname As String, _
   sTextFieldname As String, _
   nValueID As Long
   
   sTablename = "usys_qPIDFriendlyT"
   sIDFieldname = "PID"
   sTextFieldname = "FriendlyT"
   nValueID = 138
   
   MsgBox LoopAndCombine(sTablename, sIDFieldname, sTextFieldname, nValueID)

End Sub

'~~~~~~~~~~~~~~~~ LoopAndCombine
Function LoopAndCombine( _
   psTablename As String _
   , psIDFieldname As String _
   , psTextFieldname As String _
   , pnValueID As Long _
   , Optional psWhere As String = "" _
   , Optional psDeli As String = ", " _
   , Optional psNoValue As String = "" _
   , Optional psOrderBy As String = "" _
   ) As String
's4p
'loop through recordset and combine values to one string
   
   'NEEDS REFERENCE
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   
   'PARAMETERS
   'psTablename --> tablename to get list from
   'psIDFieldname --> fieldname to link on (ie: "BookID")
   'psTextFieldname --> fieldname to combine (ie: "PageNumber")
   'pnValueID --> actual value of ID for this iteration ( ie: [BookID])
   'psWhere, Optional  --> more criteria (ie: "Year(PubDate) = 2006")
   'psDeli, Optional  --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
   'psNoValue, Optional  --> value to use if no data (ie: "No Pages")
   'psOrderBy, Optional  --> fieldlist to Order By
   
   'Set up error handler
   On Error GoTo Proc_Err
      
   'dimension variables
   Dim rs As DAO.Recordset _
      , vAllValues As Variant _
      , sSQL As String
    
   vAllValues = Null
  
   sSQL = "SELECT [" & psTextFieldname & "] " _
       & " FROM [" & psTablename & "]" _
       & " WHERE [" & psIDFieldname _
       & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _
       & IIf(Len(psOrderBy) > 0, " ORDER BY " & psOrderBy, "") _
       & ";"
       
   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
      
   'loop through the recordset until the end
   With rs
      Do While Not rs.EOF
         If Not IsNull(.Fields(psTextFieldname)) Then
   
            '~~~~~~~~~~~~~~~~~~~~~~~~~ CHOOSE ONE
   
            '---- if field value is numeric
            vAllValues = (vAllValues + psDeli) _
             & Trim(.Fields(psTextFieldname))
   
            '---- uncomment if you want quotes around data
            'vAllValues = (vAllValues + psDeli) _
             & " '" & Trim(.Fields(psTextFieldname)) & "'"
            '~~~~~~~~~~~~~~~~~~~~~~~~~
          End If
         .MoveNext
      Loop
   End With 'rs
   
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
 
   
Proc_Exit:
   'close the recordset
   rs.Close
   'release the recordset variable
   Set rs = Nothing
    
   LoopAndCombine = Trim(vAllValues)
   Exit Function
   
'if there is an error, the following code will execute
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
 
   Resume Proc_Exit
   Resume
End Function

Open in new window

If you want help on error-handling, here are some links to video on EE:

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Error Handling Part 3 - Run and Fix Bugs (7:51)
http://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html
0
 

Author Comment

by:Linda Nichols
ID: 41773804
Thank you!  I will try this out.  I need to get my report done for tomorrow, sigh.
0
 
LVL 22
ID: 41773807
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:
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

Open in new window

**********************************************************
*** 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

Open in new window

If you have more questions, we can help ... you will get there :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question