UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Checked Boxes Selection, Access 2016    
 
   
ccman69
post Apr 16 2018, 08:55 AM
Post#1



Posts: 439
Joined: 5-July 07



I am needing a means to create a button to build a concatenated string based on selected checkboxes for an individual record.
Here is the situation:
I have a form for hardware dealers that contains a number of checkboxes for customer services that the hardware store provide for their customers.

Example:
ABC Hardware provides the following services (Checkboxes that are checked): Key Cutting, Screen Repair etc. There are 39 services that can be checked.

I want a button that will concatenate a comma separated string of these services for keyword generation. I was thinking would need to be accomplished through VBA. How can I capture only the checked boxes to be listed in the string?

--------------------
ccman69
Go to the top of the page
 
theDBguy
post Apr 16 2018, 10:12 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hi,

How are you storing these services in the table? If you're using separate fields for each one, you may not have the most efficient table structure. For example, what happens if a 40th service becomes available? You will probably have modify the design of your form to add another checkbox. Is this correct?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
doctor9
post Apr 16 2018, 10:12 AM
Post#3


UtterAccess Editor
Posts: 18,254
Joined: 29-March 05
From: Wisconsin


ccman69,

You might want to check out the demo file in my reply in this recent thread.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
projecttoday
post Apr 16 2018, 10:28 AM
Post#4


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


Do you want this to be output as a .txt document?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
ccman69
post Apr 16 2018, 10:38 AM
Post#5



Posts: 439
Joined: 5-July 07



the records are stored in a table and each record has a column related to each service. So, yes, if there where additional services, such as 40, I would have to modify the table.

In reference to the output. I will be writing to a temp Long Text Control. I can use cmdCopy at that point.

--------------------
ccman69
Go to the top of the page
 
theDBguy
post Apr 16 2018, 10:44 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Is it too late to change the table structure? The recommended approach is to store the services as records rather than as fields in a table.

Just wondering...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Apr 16 2018, 10:52 AM
Post#7


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


I agree with theDbguy. Having a field for every possible service is not dynamic.


--------------------
Robert Crouser

My company's website
Go to the top of the page
 
moke123
post Apr 16 2018, 11:10 AM
Post#8



Posts: 1,240
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
what happens if a 40th service becomes available? You will probably have modify the design of your form to add another checkbox


My first thoughts were to use a multi select list box with the table of services as a rowsource.

here's an example with a few ways to manipulate a multi select list box to get concatenated strings.
Attached File  ExampleMultiSelectLbx.zip ( 27.52K )Number of downloads: 7
Go to the top of the page
 
ccman69
post Apr 16 2018, 01:02 PM
Post#9



Posts: 439
Joined: 5-July 07



I did look at everyone's advice and I thank you for your input. However, I end up with a different solution that did work. I added the Long text control as mentioned and named the control TxtServicesKeywords. This code cycles through each control and captures the name of that control if it is checked. Then it writes the name to the control TxtServicesKeywords. For each box that is checked, it appends with a comma and the next name.



Private Sub CmdKeywordCapture_Click()
Dim cCont As Control

For Each cCont In Me.Controls
If TypeName(cCont) = "checkBox" Then
If cCont = -1 Then

NotesMsg = cCont.Name
If Me.TxtServicesKeywords = "" Or IsEmpty(Me.TxtServicesKeywords) Or IsNull(Me.TxtServicesKeywords) Then

Me.TxtServicesKeywords = NotesMsg

Else

Me.TxtServicesKeywords = Me.TxtServicesKeywords & ", " & NotesMsg

Me.Refresh
End If
End If
End If

Next cCont

End Sub


It may be a may be a strange way of doing thing's in the minds of great coders such as yourselves. However, it does meet my needs.

Thanks again for your help.

--------------------
ccman69
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:47 AM