Forms :: Formatting List Box Columns
Aug 13, 2013
I have a table of data (codes & amounts) which I want to display on a form via a list box (purely for information purposes; the list box will be locked / disabled). Basically, the list box will mimic a pivot table as it would appear in Excel, albeit without any of the filtering functionality.Codes can appear multiple times in the source table, each with a different value assigned to it.
The list box should have 3 columns :the unique code strings
the number of instances of that code string (i.e. Count)
the total value assigned to that code string (i.e. Sum)
In descending order based on the number of instances of each code
I have the following query set up to pull the data :
Quote:
SELECT tblData.Code AS Expr1, Count(*) AS CodeCount, Sum(tmp.Amount) AS CodeSum
FROM (SELECT Code, Amount FROM tblData) AS tmp
GROUP BY tblData.Code
ORDER BY Count(*) DESC;
I want the 3rd column of the listbox (the summed value) to appear as a formatted $ amount rather than a raw floating point. So "$10,000" rather than "10000.00". And, if at all possible, right aligned.But I have no way of formatting the columns of the list box (that I am aware of) either through the listbox properties directly or by VBA indirectly.Is there another way I can do this, either by applying the formatting to the query itself or any other trick to somehow apply the formatting as I need it?
View Replies
ADVERTISEMENT
Mar 5, 2014
My table fields are as follows:
1. Issued? (This is a lookup field with the following lists "Yes"; "No"; "NA")
2. IssueDate
I created a subform bound to the table.I wish to accomplish the following on the form:
1. If "No" is selected on the [Issued?] field, I want the [IssueDate] to gray out.
2. If "Yes" is selected, I want [IssueDate] to be required.
I set Conditional Formatting as follows:
Expression is ([Issued?]="No"), condition is disabled.
When I tested it, [IssueDate] grayed out when "No" was selected. However, when I select "Yes", it still remained grayed out.
View 5 Replies
View Related
Feb 23, 2015
I want to add a conditional formatting to a combo box to highlight if the value is not on the list.There is a process to read in data from a 3rd party excel sheet that has truck arrival dates and times. My form displays this data and allows the internal users to change it. My form has the "Trucks" field as a limit to list combo box - so basically any data can be read into this field but internal we can only change it to trucks on the list (from the trucks table). I want to highlight where the trucks value is not in our trucks list.
View 2 Replies
View Related
Sep 10, 2014
I have created a list that has 5 column. Data is filled in list using a query. I know how to remove 2 columns, but I do not know how to increase the width of columns.
View 2 Replies
View Related
Jan 10, 2015
I'm a newbie with Access, how to reorder the displaying of the columns in a Combo Box dropdown list of have in my Access 2007 application.
The columns that are being displayed are (in the current order):
Part Name
Unit Price
Parts.Web_Category
I want the order to be the following:
Parts.Web_Category
Part Name
Unit Price
View 13 Replies
View Related
Feb 22, 2005
Hi All
Has anyone experienced this:
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.
Any ideas?
Cheers
Alan
View 1 Replies
View Related
Oct 2, 2013
The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).
I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.
View 1 Replies
View Related
Nov 21, 2014
Is there a way to change the formatting of a numbers column to the formatting :
1 = 0001
11 = 0011
111 = 0111
1111 = 1111
But that would have to apply to this also
1 - 2 = 0001 - 0002
11 - 22 = 0011 - 0022
111 - 222 = 0111 - 0222
1111 - 2222 = 1111 - 2222
If that makes sense ....
View 14 Replies
View Related
Mar 7, 2013
I've run into a situation with our Access Database where sometimes when we import information into it from an Excel sheet the dollar amounts get rounded out. For example, the amount of $726.68 shows as that in the database but when you click on the field/cell it's in it shows as $726.6799. It doesn't do this for every field which is weird. The data from the excel sheet is not roudned out either, it shows the amount as $726.68 so it appears to be something funny going on with Access. Under the formatting area, the data type is set as currency and format is currency.
View 8 Replies
View Related
Jul 24, 2013
I am making a classic sales over time crosstab query.
Rows: Customers
Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013
Is it possible to format the columns this way?
View 2 Replies
View Related
Aug 1, 2014
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
View 11 Replies
View Related
Oct 21, 2004
I have a phone number field defined in a table as text and use the input maks !(999") "000-0000 for entering and editing data. Is there a way to use a mask when using this field in a list box? It would be nice to be able to show (555) 123-4567 instead of 5551234567.
Thanks,
Ted
View 2 Replies
View Related
Jan 30, 2006
I am using a Multi Select List Box to display a list of names from a table, in order to select e-mail recipients. The names are in two fields: [Main]![First Name] and [Main]![Surname].
I have done this by using the following code for the rowsource:
SELECT DISTINCTROW Main![e-mail], Main![First Name], Main!Surname FROM Main;
This gives a rather undesired effect, in that shorter first names have a large gap before the surname. In addition, there is a dull separating line between the two columns, thus:
Jane | Smith
Bartholomew | Simpson
My questions:
Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written)
If not, can I get rid of the separating line?
Many thanks in advance if you can help!
View 4 Replies
View Related
Nov 14, 2006
Hi All,
I have a list box and I populate it with records from a "Select" query. I am using VBScript to do this. Here are my codes:
gr1_list.RowSource="Select Criteria from CriteriaList WHERE Criteria='" & cvalue & "';"
My "CriteriaList" table has two columns; Criteria and CriteriaDescriptions.
The question is, what if in my list box I want to be able to see both 'Criteria' and 'CriteriaDescription?' How would I code this?
Thank you.
View 4 Replies
View Related
Jun 3, 2005
HI
I have a list box which sometimes needs extensive scrolling to the right to see other data.
Im wondering whether I can freeze the first few columns (key info) so I can trace the other data when scrolling. (i.e. like in an Excel spreadsheet)
I dont mind what coding if I have to - any suggestions please?
View 2 Replies
View Related
Sep 25, 2013
I have a "Mailing Labels":
John Doe
100 NE Main Street
Portland, OR 97203
And I want to separate in columns:
Name | Address | City | State | Zip Code
John Doe | 100 NE Main Street | Portland | OR | 97203
View 2 Replies
View Related
Mar 31, 2015
What is the correct way to save the values in a listbox column to the fields of a table? I can use rec("field1") = Me.list16.ItemData(Varitem)and that records the bound column to field 1 but how can I save the unbound columns to other fields in the table?
View 4 Replies
View Related
Jul 19, 2013
Runtime Error 3075? I have attached a copy of my database. I am trying to make a calendar form with the list boxes having two columns. It works fine with the strFieldID and strFieldName but when I try adding the strDone I run into the error.
View 6 Replies
View Related
Oct 22, 2014
I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox
Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code:
Dim ItemIndex As Variant
For Each ItemIndex In Me.lstPayPeriods.ItemsSelected
If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then
Date1.SetFocus
Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex)
End If
Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code:
Dim CurrentRow As Integer
Dim FirstDate As Date
For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1
If Me.lstPayPeriods.Selected(CurrentRow) Then
Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
View 2 Replies
View Related
Oct 27, 2012
I am trying to obtain totals from two columns in the list box into text boxes on the main form, but my third argument is not working as expected.The source of one of the tex boxes is:
Code:
=DSum("Airtickets","T_Training_Participants.ProgrammeID=Me.lstParticipants")
I want to sum only amounts of the records that equal or belong to a selected programme (ProgrammeID) in the bigger list box above.
View 2 Replies
View Related
Mar 26, 2013
I'm having an issue getting all of my conditional formatting rules to run on my forms. For some reason it is only running the rule in the top position. I'm running different versions of this rule. Changing the field or table as needed to highlight duplicates in different colors. As you can see on page two of the attached PDF the entry for 33K6-4-3176-1 should be highlighted (yellow) because it is a duplicate. It only works if I change the order of presidance of my rules but then that negates my other rule. I'm not sure what is causing the conflict here.
If the field is red then the "Auth" field is a duplicte within that form if the firld is yellow then the entry in the "Auth" field appears on both forms. Red should take precidance.
Nz(DCount("*","tblRndfrm","[Auth] = '" & [Auth] & "'"),0)>1
Nz(DCount("*","OvrDrw","[Auth] = '" & [Auth] & "'"),0)>1
View 1 Replies
View Related
Apr 14, 2014
I have a drop down box on a continuous form called (cmbItem).When the users choose Option 6 (Other) and on the open event I wish to show another field txtOther which allows the user to give a description of what 'other' means but I only want to show the field for that record. (the user could potentially have more than one record as other)Using conditional formatting I have managed to change the colours of the box so that the background is the same colour as the form unless other is chosen when it reverts to white. However I can't do the same with the border as this is not one of the options provided.
View 2 Replies
View Related
May 20, 2013
I am wanting to preempt data in list boxes
listbox1
Fruit
Vegetable
listbox2 (If Fruit Is Selected)
Apple
Banana
Orange
listbox2 (If Vegetable Is Selected)
Potato
Peas
Carrot
If Fruit is selected in Listbox1 - Then Listbox2 should have the options
Apple
Banana
Orange
If Vegetable is selected in Listbox1 - Then Listbox2 should have the options
Potato
Peas
Carrot
View 12 Replies
View Related
Feb 2, 2014
I have a Form that links to a List Box which pulls from a combined (concatenated?) list. The list it is pulling from appears as follows: SELECT [Rank]+" "+[Last Name]+", "+[First Name] AS Expr1 FROM [T_Personnel Information]; My issue is that the Field in the Form that pulls from the Field in the Table defaults to the first name on the list when left blank, where as I want it to default to a blank value unless I select a name from the list.
View 1 Replies
View Related
Jul 15, 2015
I have a form with 2 list boxes, part number and modification. There is a subform containing another list box that is supposed to show the part information (bpn,vendor,status,etc.) that corresponds to the selected part number/modification in the parent form list boxes.
The part info list box has multiselect enabled and what i want to is be able to select multiple line items and press a button which then sets all of the selected line items status to "Request Removal". This is my code for the button:
Private Sub removeButton_Click()
Dim varItem As Variant
With Me.acbModList
For Each varItem In .ItemsSelected
MsgBox (Me.Status.Value & Me.[Part Number].Value)
Me.Status = 6
Next
End With
End Sub
The msgbox was for debugging purposes. Here's my issue; the for each actually does iterate through each selected item but the value for the line item doesn't change along with it. For example, when I selected 3 items, the msgbox will pop up 3 times but each time will have the same information (first item in the table) even when that item isn't selected.
My next issue is that I am receiving an error message with "Me.Status = 6" stating "You cant assign a value to this object". 6 refers to the id of the status i want to set it to.
View 4 Replies
View Related
Dec 2, 2005
:confused:
Hi. Does anyone know a way to change the colour of controls depending on their value in a continuous form?
I can change the colour of the current row in a continuous form, but I cannot adapt this so that it changes colour depending on the value (ie conditional formatting)
Ta
View 5 Replies
View Related