Queries :: How To Show / Hide Columns In A Query Using A Form Checkbox
Jul 16, 2015
Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.
I've got a main form with two lists boxes. I want to show the visible columns in my subform (which is a datasheet) in one listbox and show the hidden columns in the other. Also I want to allow the user to hide / show columns using right or left arrow buttons between the list boxes. My subform is bound to a stored procedure using ADO.
I have a sub form with checkboxes. Each check box represents one of the columns/fields in my query. If one of the boxes is checked, I only want the column that matches with the check box to appear. I have done some research and have came upon the ColumnHidden property.How would one do this in a macro or VBA format.
I would like to know if the following is possible: I have a table, I have a form based on that table (datasheet view) and I would like for certain columns to visible or not based on the input on a Combobox (list of countries) that is based on another form.
I have searched on the web and I found that the following:
On a button based on the main menu form:
Code: Private Sub Go_Click() Dim stDocName As String Dim stOpen As String stOpen = Me!cboDest stDocName = "Datasheet" DoCmd.OpenForm stDocName, , , , , , stOpen
and then on the event "on open" of the form Datasheet I have inserted the following:
Code: Private Sub Form_Open(Cancel As Integer) Select Case OpenArgs Case "USA" Forms![Overflow]![Tasks].Visible = True Forms![Overflow]![Constant Number (Min)].Visible = True
[Code] ....
I have done it as a test, but it appears that it does not work. It opens the forms, but it does not hide or show the columns based on the option, it brings all the columns.
Hi All. What I would like to do, is have a form popup when the main form is opened in my db, which will detail all the changes I have made to it since the last version (this bit is OK) and also, I would like a checkbox on the popup form, that the user can tick once they have read the details so it will not show up everytime they open the main form. (Just like the "hidestartupform" property within the Northwind Database)
I create the form and all that, but do not know how to do the checkbox so once it is checked, the form will not appear.
Hope this makes sense and someone can enlighten me and point me in the right direction?
I want to automate a query so when the user views it they see only the columns that have data. Columns that do not have data are not there.I am using MS 2007
My Access tool is setup to import data into tables and then the user views the data imported. However, if their's no data (and no column to import) I want the query report to not include the column.
I am pretty new to VBA and I am having the same problem of hiding and un-hiding columns on a datasheet of a split form. I am building a system that will be used in front of customer and therefore wish to hide columns that contain cost sensitive data. The same forms need to be able to show the Sales Rep the hidden columns simply by ticking a box. Basically a toggle on and off of hidden columns.
I have been playing around with the code below (which i found in another forum) to hide the Field called COO when I click the tick box called chkHIdeFields check box. This works on a Single form but not on a datasheet of a split form.
Private Sub chkHideFields_Click() ' Note: vbTrue = -1 If Me.chkHideFields = vbTrue Then Me.COO.Visible = True Else: Me.COO.Visible = False End If End Sub
I have been able to hide the column of my split form by using the ColumnHidden property of the On Load event just as a proof of concept that the ColumnHidden property actually works to hide a column, which it does. This is the code that im using to hide the column called COO.
Me.COO.ColumnHidden = True
However if I then set it to = False and then close and open the form, it doesn't unhide the column. the only way I can unhide the column is to do it from the Form view via the un-hide dialogue pop up box.
I have two issues here, the first one is getting the form to recognize when to hide and when to show the column of the datasheet on the split form and the second is to get the code to for the check box method.
Lastly once I get it working for 1 field I need to be able to define a list of 7 or 8 other fields all at the same time.
I've got Form with some drawings on it and I'd like it change depending on what the user has entered in certain fields on the same form. For Example: when the user selects a certain type of item in the "Item1" field, I'd like the graphic to change (or another one appear) in the form. Basically an "If this is true, show this picture" type thing. I've been trying a few things to make this happen, but to no avail... :( Any incite anyone could give would be most appreciated.
I want to show or hide a box on a continuous form. If there is a date in the ShopOut field i want the label to show up , if not then it will not appear.
I am trying
If IsNull(Me!ShopOut) Then Me.StillinShop.Visible = True Else Me.StillinShop.Visible = False End If
however, it shows the box whether there is a date in ShopOut on not. Is it possible to do this in a continous form or do i need somekind of me.record#.ShopOut or something.
Code: Outfits: ID | Top Color ID | Bottom Color ID 1 2 1 2 3 4
Colors: ID | Name 1 Red 2 Blue 3 Green 4 Orange
I'd like to have a query in design view to have the following result
Code: Outfit ID | Top Color Name | Bottom Color Name 1 Blue Red 2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training 1/1/2010 5/1/2010 1 John Smith Python Training 1/30/2011 4/1/2011 1 John Smith Leadership Development 6/27/2013 9/1/2013 1 John Smith Sensitivity Training 9/5/2010 - 2 Hank Joel MS Office Training 8/1/2010 10/1/2010 2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010 2 Hank Joel WHMIS Training 11/15/2010 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
To turn like this:
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, - 2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:
However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:
Quote:
WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")
I placed the following in VBA module:
Code: Public Function ConcatRelated(strField As String, _ strTable As String, _ Optional strWhere As String, _ Optional strOrderBy As String, _
I'm currently building a database in Access 2013. I've used Access many years ago, and not really in anger since maybe 2003, so I've been re-learning quite a lot. Here's an overview of my scenario, and issue:
The main purpose of my database is to allow engineers to log various bits of information when building, servicing and testing manufactured products for offshore exploration. Because some of the engineering team aren't so great with data entry etc, I've decided that, rather than deleting data, I'm going to use a 'deleted' flag: user clicks what he considers to be a delete button, but in reality an update query will simply update the flag and, because the form is built on a query which includes a 'where deleted = 0' filter, it'll no longer be shown. This will allow us more control over the data and will also allow me to check that everything is running okay.
My main forms include a number of subforms, including one which includes a second tier of nested form: Pin Maps for (electrical) Connectors.
What I need to achieve is fairly straightforward: as the user navigates around the form, I'd like a 'delete' button which is specific to that subform to become visible and for any other 'delete' buttons to become hidden. Once tested, the buttons would be 'stacked' in the same position at the top of the main form, so the user just thinks they're deleting the relevant record in the sub, or main, form.
My tab name is SPA and it is the third tab in my form. I want to hide it if the AddToSPA checkbox is checked. This is what I have but I'm getting error 'Compile error: Method or data member not found"
Code:
Private Sub Form_Load() If Me.AddToSPA = True Then Me.SPA.Pages(2).Visible = True Else Me.SPA.Pages(2).Visible = False End If End Sub
How can i do that ? If i shall write a code, in which event ?? Or can i use in Query Build option ?
I searched on internet and found some answers but they are not useful
(i found this code and updated for my report but still did not work)
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) (<- what is that ??) If Me.Skyliner = 0 Then Me.SkylinerCover.Visible = True Else Me.SkylinerCover.Visible = False End If
I want to create form with 10 checkbox, each checkbox is linked to a field "name, Address, TelephoneNumber, BuissinessName" I would like to know if it is possible for the user to check the box and then the field will show in query. if only name is check then only name will show in the query.
I have tried using [Forms]![Formname]![CheckboxName] as criteria but this didn't work it when I had more then one checkbox and when the one check box was not checked nothing showed in the forms.
I'm trying to create a report where I can use a section header as a hyperlink to show/hide detail, but only for that section. For example, my customer names are:
Code: ABC Co. ZYX Co. 123 Co.
If I click on ZYX Co., I want it to show the contracts for ONLY that customer:
I have a datasheet as a sub-form on a main form. Because the main form is accessed from 2 different forms in the system (let's call them A and B), I hide a column on the datasheet that is not relevant when the access is from Form B. This was working fine.
Until I added a new field to the datasheet/subform with some conditional formatting (background colour change dependent on field content). Now the hide column function causes an error which locks the database. I removed the conditional formatting and the hide column functions works fine. Please note the column being hidden and the column with the conditional formatting are not the same column.
Seems the combination of hide column AND conditional formatting in the same datasheet is the problem.
I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.
Here's an example of the a duplicate, all 4 first rows are duplicates, but the last value is null for the last duplicate. I want to hide the one the duplicate that has null value!
I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.
When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].
I've tried setting up an IIF statement to filter out the values that are empty but it's not working.
I have a question on hiding duplication record using query.
The fields in the query are:
full name(trainee), NRIC(trainee), gender(trainee), preferred language(trainee), company(trainee), course name(course), course date(course), competent(course), class(course), L3 survey(trainee), L4 survey(trainee), num of month(course)
When the query is being run, it will show all the people that have not done the L3 and L4 survey after 3 month. The the query will be convert into a form. However the problem is that the record will show a few same name due to one person can take more than 1 course. therefore, the data in the course table will always be different.
How can i make the record only show 1 name even though they have different course name.
I had tried putting 'yes' for unique record and unique value but it did not change the result.
current SQL query:
SELECT DISTINCT trainee.[Full Name], trainee.NRIC, trainee.Gender, trainee.[Preferred Language], trainee.Company, course.[Course Name], course.[Course Date], course.Competent, course.Class, trainee.[L3 survey], trainee.[L4 survey], DateDiff('m',[Course Date],Date()) AS [Num of Month] FROM trainee INNER JOIN course ON trainee.NRIC = course.NRIC WHERE (((course.Competent)="c") AND ((trainee.[L3 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=3)) OR (((trainee.[L4 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=6));
Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.
I have some VBA code that generates a query and saves it. The query is a list of people who will be sent a communication along with the formatting for the communication. After producing the query I want to look through it and decide if there is anyone I don't want to send the communication to. I can't delete records from the query without deleting them from the database or I would simply do this.
I thought I could add a checkbox in the query that I can tick to stop a communication being sent to that person. I only want the setting saved within the query and then when I have finished it be deleted along with the query.