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:
Code:
If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If
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.
View Replies
ADVERTISEMENT
May 2, 2014
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.
View 4 Replies
View Related
Nov 29, 2013
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?
View 6 Replies
View Related
Sep 13, 2013
We have a small lab database where we insert the results of a water plant test. The results can be either Before treatment or After treatment.
And it can be treated numerous times a year.
I have created form with a drop-down box to select the customer and a button to show reports for the selected customer.
But I also want to add 2 check boxes: a Before treatment and After treatment box. And also add a date range.
So when selecting to view reports our users can select a date range example:
01/01/2013 to 01/10/2013 and a check box so users can select if the results to be displayed are Before or After treatment.
View 1 Replies
View Related
Nov 25, 2005
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.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.
View 1 Replies
View Related
Feb 11, 2015
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.
View 2 Replies
View Related
Sep 15, 2005
Hi folks,
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.
View 8 Replies
View Related
Sep 3, 2013
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
Do I need to do a loop that concatenates?
View 11 Replies
View Related
Mar 18, 2013
I saw the following code on the forums...
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.
View 4 Replies
View Related
Mar 19, 2008
Hi Folks.
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.
Chalkie.
View 1 Replies
View Related
Aug 30, 2005
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.
pls help. thanks. :)
View 11 Replies
View Related
Dec 12, 2013
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?
View 13 Replies
View Related
Dec 1, 2005
Hi all,
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"
Thanking you all in advance for your help!
Younger
View 6 Replies
View Related
Apr 10, 2005
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?
PLEASE HELP!
View 1 Replies
View Related
May 7, 2015
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.
View 1 Replies
View Related
Oct 16, 2013
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
[Code] .....
View 14 Replies
View Related
Apr 25, 2014
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...
View 9 Replies
View Related
Nov 18, 2014
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?
View 10 Replies
View Related
Oct 19, 2004
I want to clear all the checkboxes in a certain field by using a command button on a form. I could use some help!!
View 3 Replies
View Related
Jun 30, 2014
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).
View 4 Replies
View Related
Mar 18, 2015
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.
View 3 Replies
View Related
Aug 27, 2014
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....
View 1 Replies
View Related
Oct 14, 2013
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
View 7 Replies
View Related
Dec 17, 2013
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"
View 6 Replies
View Related
May 12, 2015
Okay I have a Private Sub on form PlotF:
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?
View 8 Replies
View Related
May 5, 2015
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
View 4 Replies
View Related
