Modules & VBA :: Concatenating Data From Multiple Checkboxes
Apr 15, 2015
I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:
Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"
I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.
I'm having a rough time finding information regarding filtering using multiple check boxes that are not part of an option group. I have 4 "sets" of check boxes that can each have multiple selections made.
For example I have:
12 check boxes for each month 6 check boxes for a selection of years 6 check boxes for order types 5 check boxes for order company
I have written code that successfully creates a string depending on what boxes are checked that looks like this.
[Ship month] = "1" OR [Ship month] = "2" OR [Ship month] = "5" AND [Ship Year] = "2013" OR [Ship Year] = "2014" AND [OrderType] = "SO" OR [OrderType] = "SM" AND [Order Company] = "10430" OR [Order Company] = "10440"
The problem is that it does not filter correctly. After playing around with it I found that as long as the entire is using all AND operators or all OR operaters it works fine, but as soon as I mix them it doesn't work.
I have a field 'Payment Types' with values (Cash, Cheque, Debit/Credit Card) and a field 'Payment Received' which is Yes/No.
When putting the order through the user selects the payment type and ticks a box if payment has been received.
On a report for delivery drivers, the owner wants it simple for the driver... he wants all the payment types listed with a checkbox next to each one, then wants the appropriate box ticked if payment has been received.
So I need something on the report (or underlying query) which ticks the appropriate box, i.e.
If payment received = true then payment type checkbox = true.
Or should I put the payments into a separate table with both fields so multiple payment types can be marked as paid?
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3 1 x 1 m 1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3 1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?
My db has three tables: table_a, table_b and table_c. Each table has the following fields:
table_a a_id (PK) a
table_b b_id (PK) b
table_c c_id (PK) a_id (FK (many to one)) b_id (FK (many to one)) c
I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.
Can anyone advise me how I do this?
By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code: Private Sub Email_Button_Click() 'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home")); Dim emailsList As String DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True End Sub
Dim i As Integer, c As String With Me.ListBox.Column(0) For i = 0 To Me.ListBox.ListCount - 1 c = c & Me.ListBox.ItemData(i) & ", " Next End With c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String With Me.ListBox.Column(2) For i = 0 To Me.ListBox.ListCount - 1 d = d & Me.ListBox.ItemData(i) & ", " Next End With d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
I have a select query that has 10 columns which are checkboxes (yes/no) from the original table. I can apply filters for individual columns but I want the query to show records with ANY of the boxes checked and only omit those with NO boxes checked.
I have spent the whole atfernoon researching the forum but I can't find what I need. Help much apreciated.
hi, i've a form with 2 combo boxes (month and year) and 2 buttons "save" and "get". I'm trying to figure out how to use that button to perform 4 tasks.
task 1:
when i select the month and the year from the combo boxes and i click the button "get", the form should populate the number of checkboxes equavalent to the number of days of that month. e.g: if i select feb 2008, i should have 29 checkboxes (due to leap year) or i select apr 2005, i should have 30 checkboxes.
assuming that i've done the date validation function.. like jan, mar, may, jul, aug, oct and dec should return 31 days, leap year etc.
task 2:
then how should i assign the date value to the checkbox? say if i check on the 5 th box, it should return me a value of 05/09/2005 (assuming i select sept in my month combo box and 2005 in my year combo box earlier on)
task 3:
how should i code the label for my checkboxes? i wan to have 2 labels on top of the checkbox. the first label to show the name of the day (e.g: mon, tue etc) and the second label to show the day (e.g: 1, 2, 3)
task 4:
not sure if this task is a tall order.. but nonetheless, i need to consult the experts here. let's say when i check multiple checkboxes at one go, how to update into the table i want?
e,g: i check 9 boxes (e.g: 01/09/05, 03/09/2005, 04/09/2005... 23/09/2005) then when i click the button "save", i should have nine records in my table designated to save the data.
I have a form register with student names and three columns with checkboxes showing whether they were present, absent or late. The form runs an append query that records the data into a historical table, however on submission the checkboxes remain ticked. way to clear the checkboxes once the data has been submitted?
Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s
Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.
I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this
SELECT Customers.* FROM Customers WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check _partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));
However, the query only seems to want to pull data from all 3 combo boxes
My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.
I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"
I have a form. In that form I want to display a series of dates from a database table..ex. 12/15/2004, 12/16/2005, etc... Next to those dates I want to have a checkbox. The user should be able to click on that checkbox if they want to sign up for that date. So there could be more then 1 checkbox checked, but there will always be atleast 1. So my first question is, how do I make this work. I've tried several things and everytime I check on the checkbox it checks all of the checkboxes.
PLEASE HELP!
Subject 2: Regarding the above question, how do i reference those dates that I've checked the checkbox next to, to put into another table?
I have a form where you can select four different options: Health, Dental, Vision, and COBRA, with a button to run a census. Right now, the button runs a DoCMD.OpenReport to open a report named "Census" where there are four text boxes, "Health Coverage Type" "Dental Coverage Type" "Vision Coverage Type" and "COBRA Coverage Type". I want the user to be able to select a checkbox, then filter the report to only enable the text boxes of the corresponding names.
So if someone selects Health & Dental, I want the report to run with the "Health Coverage Type" and "Dental Coverage Type" textboxes enabled/visible, but the other two to be blank.
I created a Microsoft Access database and access to the system must be controlled by User Access Level Control. The level of the user determines whether the user can add, edit, delete or view a certain form.
I created three tables which are linked via foreign keys: tblUsers, tblUserRoles and tblPermissions. (See the Tables attachment)
I designed the Permissions form to be user friendly by adding checkboxes on the form so that the Administrator can select whether a new user has Add, Edit, Delete or View rights. (See the User Level attachment)
To test my code I added a user as an Administrator. The problem is that when I select the Add, Edit, Delete and View checkboxes, it only saves the last checkbox to the Permissions table. The Administrator must have Add, Edit, Delete or View privileges on the Employees form, but now he only has View privileges. My code does not generate an error. (See the Incorrect attachment)
The Permissions table is suppose to save four entries(See the Correct attachment)
Here is my code.
Private Sub cmdSave_Click() Dim rstPermissions As Object Dim dbFSManagement As Object Set dbFSManagement = CurrentDb Set rstPermissions = New ADODB.recordSet
I am using Access 2013, I have a recipe project with multiple one-to-many relationships. The main table in all of them is RECIPE. Child tables are HOLIDAY, SPEC_NEED, COURSE... each one of these child tables are comprised of multiple checkbox columns (yes or no)...
I need to filter the RECIPE records based on the selected checkboxes in these child tables..so for example if COURSE.Appetizer is checked and HOLIDAY.Christmas is checked RECIPE will produce the appropriate records. How to pull this one off...I am currently using the Options Group design tool and have used a separate Options Group design for each of the child tables...
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I've got a field in a table that is a multiple drop-down list. In the form, I don't want it to be a drop down list, but I want the options in the drop down list to be checkboxes instead (not within a drop down).
From a dropdown field in the form it's currently possible to choose a geographical region for which to generate a report. The data populating this dropdown is pulled in from a Value List as follows:
" ";"*";1;2;3;4;5;6;7;8;9;10;11;12;13;EU;WD
I now need the ability to choose various different regions simultaneously which is not possible with the current method. I've looked into a nested continuous form and a multi-select combo box or list box, but none of these are as user friendly as my preferred method.
What I would like is 15 checkboxes plus a 16th to select/unselect all. When any of these checkboxes is checked, I need to create something like a dynamic value string or temporary table to hold the list of chosen regions until the generate button is clicked at which point the data is used to generate the report and cleared. I also need a piece of code to check/uncheck all the boxes.
Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....
I have an issue with a couple of my combo boxes. When in form mode the drop down menu displays a list of options (taken from my source table), this is fine, but the problem is that it allows the user to select more than one of the options in the form of checkboxes. This is not what I intended.
I've attached to pictures to demonstrate the problem. The first is ComboBoxQuery (the one with the problem) and the second is ComboBoxQueryWorkign (the one without a problem).
I'm not sure why this is happening and I've tried comparing all the properties of both these combo boxes and changing some of them to see if I can eliminate the problem without any joy.
I use a very standard SELECT statement to pull the data for the combo box:
SELECT CountryID, CountryName FROM tblCountryInfo ORDER By CountryName
I'm trying to write some simple code to see whether two check boxes (named cbM001 and cbM011) have been checked and if so, then send out an error message. So far I have tried:
If cbM001 Is False And cbM011 Is True Then MsgBox "M011 cannot be selected unless M001has also been chosen." Exit Sub
[Code].....
With this I get "Run-time error 424: object require"
Code: Private Sub SetCheck212() If Me.[Check161] And Me.[Check169] And _ Me.[Check167] And Me.[Check181] And _ Me.[Check261] And Me.[Check189] And _ Me.[Check187] And Me.[Check195] And _ Me.[Check203] And Me.[Check201] Then Me.Check212 = True
[Code] .....
Some of the checkboxes are locked as I want to force the user to check them on another form (InvoicedF) but I want them to be displayed PlotF as well.
So I want to call the Private Sub from another form. So that the Check212 still automatically checks to true with out PlotF open on the screen.
Is this possible, Do I have to make this private sub a public sub? If so how do I do this?
I have a table tblMessages, with a yes/no column called Available I have a form with three checkboxes chk1, chk2, and chk3 the when i click chk1 the other two check should be checked which this code seems to do but what it is not doing is going to the table and selecting the data where the ID is 2. what am i missing here?
Private Sub chk1_Click() Dim strSQL As String 'Holds the SQL statement for this command only. If chk1 = True Then chk2 = True chk3 = True strSQL = "UPDATE'[Available]' FROM 'tblMessages' WHERE '[ID]'= 2" Else chk2 = False chk3 = False strSQL = "UPDATE '[Available]' FROM 'tblMessages' WHERE '[ID]'=2" End If Me.Requery End Sub
