Multi Column Report
Feb 15, 2005
I have a reporting need that I believe (hope) is not unique
My customer needs to print out information about a customer that is compused of information specific to the customer (keyed on customer ID) and sets of information about their purchases in different categories (food, cars, clothes and pets). Each of these categories has different identifying characteristics. (These are represented by subReports on the report).
No problem printing out the report looking like this
-------------------------------------------------------------------
Customer Name - Mike Lester
Custoner Address - 101 Main St.,
AnyTown,CO 11111
Food
Beets 10 lbs $1.25 Green
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow
Cars
Mustang 1966 $23,888
VW 1955 $222
VW 2002 $23,000
Cadillac 2004 $44,999
Clothes
Belts 5
Shirts 9
Socks 11
Pants 5
Hats 1
Pets
Goldfish 2 Fish
Beagle 1 Dog
Great Dane 3 Dog
Cocateil 2 Bird
Macaw 1 Bird
------------------------------------------------------------------------
The problem is the customer wants the report to use the entire page. I tried positioning the subReports in two columns (left and right) but the problem is based on the number of items in each subReport the report ends up looking dumb and wasting paper (also it doesn't look very professional)
What I want to happen is the report to print as many subReports as it can on the left column (without leaving any hanging orphans) then start printing then next subreport at the beginning of the right column. See example below: (Sorry about the ~ Symbol being used to represent a space, for some reason the text box wouldn't save my formatting)
-------------------------------------------------------------------------
Customer Name - Mike Lester ~~~~~~~~Clothes
Custoner Address - 101 Main St., ~~~~~~~Belts 5
AnyTown,CO 11111 ~~~~~~~~~~~~~~~~~~~~~~~Shirts 9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Socks 11
Food ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Pants 5
Beets 10 lbs $1.25 Green~~~~~~~~~~~~~~~~Hats 1
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow ~~~~~~~~Pets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Goldfish 2 Fish
Cars ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Beagle 1 Dog
Mustang 1966 $23,888 ~~~~~~~~~~~~~~~~~~~Great Dane 3 Dog
VW 1955 $222 ~~~~~~~~~~~~~~~~~~~~~~~~~~~Cocateil 2 Bird
VW 2002 $23,000 ~~~~~~~~~~~~~~~~~~~~~~~~Macaw 1 Bird
Cadillac 2004 $44,999
------------------------------------------------------------------------
This is probably the longest question of the day. Does anyone have any
ideas or examples. I am hoping that this doesn't involve any massive VBA
or using Word. There are 200 other reports, and the customer doesn't currently use Word.
The entire application is 100% Access.
View Replies
ADVERTISEMENT
Apr 3, 2013
How do I select the first column of a multi-column list box (called "List1") for a query.
A single column list box works fine.
Code:
SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;
View 6 Replies
View Related
Apr 19, 2006
Hi there,
I'm sure I'm not being dim, but I really can't figure how to do this.
I have a list box with two columns. One column (bound) is a name, the other is a unique ID. I need to transfer both the Name and the ID to a second list box (again, two columns).
I can get it to do this with single selections, but not multiple ones, and this is what I need.
Any ideas?
Cheers
View 1 Replies
View Related
Sep 15, 2005
I have a combo box with 5 columns. When I click it I want to display columns 1 to 4, together with the headings. But after making my selection, I want column 5 stored in the field.
How can I do that? Currently it is storing column 1.
Thanks,
Dave
View 2 Replies
View Related
Oct 29, 2005
How can I get a form to have multiple colums.
View 2 Replies
View Related
Oct 1, 2004
I have a list box derived from a two column SQL select. When I try to use the user selected element I only get the first column data value.
In debugging I have tried ...
Ret = MsgBox(Me.List6.Value, vbOKCancel)
and...
Ret = MsgBox([Forms]![MeetingStatus]![List6], vbOKCancel)
Both of which return the selected row, first column value only.
I can find no help on how to return the content of the second column or the selection as a whole.
View 1 Replies
View Related
Oct 22, 2013
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies
View Related
Apr 14, 2015
I have combo boxes that contain attorney names and phone numbers. The attorney name is what is filled into the field when a selection is made from the combo box into the form. The phone number is just there for the occasions that the person managing the data in the database needs to call that attorney. Just a quicker way instead of having to go to another form to find the phone number. I created a query to combine the first and last name to one field (FullName) and this is where the combo box gets it's info.
When I run the query, the phone number is shown formatted as I want it to ((###)###-####), but in the combo box in the form, the phone number has no format to it. What I need to do to get the phone number to show up formatted in the combo box list?
View 2 Replies
View Related
Sep 17, 2012
I'm not familiar with listbox yet and i want to filter my form using it.
I have two separate listboxes which display "category" & "type" data from the same table.
Here is the situation i wanted for my listbox.
1. Select one data from "category" listbox.
2. Then it will automatically filter data from "type" listbox or it will list all related "type" data corresponding to "category" data.
3. Then select one data from "type" listbox and it will filter all related data on the form/sub-form.
Is it possible to do that way? Can i do it on multicolumn listbox instead of using 2 listbox?
View 1 Replies
View Related
Aug 16, 2005
My form's 10 column combo-box (based on a query) won't break into text cells. Some of the columns contain numbers, some test, some phone #s. All columns show when I click the Combo-box in Form View. Access Help File said to put:
=Forms!Customers!CompanyName.Column(1) in the ControlSource for the text cell. Tried that and was returned #Name? for any of the fields in which I tried it.
My expression was:
=frmSpartanStudentBehaviorLog!Last_FirstUC.Column (3)
The other properties in combo-box are set correctly: ColumnCount=10, Column Widths are correct.
I also tried the expression:
=Last_FirstUC.Column(2) but got the same result #Name?
I'm baffled. As a very new user, I'll appreciate any assistance. Thanks.
View 2 Replies
View Related
Feb 6, 2015
I have two listboxes. One for Activities and the other for Organizations.
I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.
To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.
Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :
Code:
ListBoxOrganizations.ItemData(varItem)
and I don't know how to do the equivalent of
Code:
ListBoxOrganizations.ItemData(varItem's column(2).value)
yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.
I've tried using the column property to then cruise to the proper record in the listbox, e.g. :
[code]
txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
[code]
But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored. I believe the loop is working properly, as when the inserts are happening correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.
I just can't get any other column's value for some reason.
MS Access 2010 and this is an accdb.
View 3 Replies
View Related
Dec 12, 2012
So I have a list box that lists organizations. I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization. When the list box was not multi select, the expression [forms]![media]!
[List30] made the default value of one of the fields in my contact form the bound column from the selection in the list box. However, now that the list box is multi select, the contact input form does not seem to be able to get the value from the bound column in the list box. When multi select is turned on, is the bound column stored differently.
To even get the contact input button to work, I had to change the code from:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contactsqry"
stLinkCriteria = "[Organization ID]=" & Me.List30
[Code] .....
View 14 Replies
View Related
Apr 11, 2014
Is it possible to create a query that asks for multi entries in one column .....
For example : If I have parts that I purchase and some of them get rejected I want to inform someone of those parts, these parts could be on order numbers, 1,2,8,10 ....
I cannot just search on rejected because some parts could have been reject say 2 weeks ago but new ones have not arrived yet so the flag "rejected" is still showing ....
Is it possible to set the criteria that when asked for the order number/s you could type in ,1,2,8,10 ....
View 1 Replies
View Related
Aug 19, 2013
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title
- Document Number (unique identifier)
- Responsible
- DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String
[code]....
View 9 Replies
View Related
Jan 4, 2013
create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.
View 3 Replies
View Related
Oct 3, 2013
I'm using the following code to filter a report by using a multi-select box.
Code:
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
[code]....
I have a report that contains a subreport. The report is simply an image of a word document. The subreport will contain the actual data.When I open the subreport, the IN query works perfectly. When I open the main report, I only get the first record listed in the IN query. I have the master/child links set properly.
View 14 Replies
View Related
Jul 22, 2015
I am running MS Access 2010 on Windows XP and my access skills are limited.
So I have a db with tables, forms and reports and would like to give my users the option of opening a filtered report (from a form).
The form (frmSelectStatus) and report (rptStatus) have been created and both open correctly by normal selection from the 'All Access Objects' side bar.
frmSelectStatus has a multi-select list box (lstStatus) and a command button (cmdStatus). When I click cmdStatus i get the correct report opening but it is all status records and not filtered by the selection made in lstStatus - and i know this is a result of not referencing 'lstStatus' in the code. i also have a query (qryStatus) which does not contain anything.
My 'on-click' code for cmdStatus is
Private Sub cmdSelect_Click()
DoCmd.OpenQuery "qryStatus", acViewNormal, acEdit
DoCmd.OpenReport "rptStatus", acViewReport
DoCmd.Close acQuery, "qryStatus"
End Sub
I know I need to reference 'lstStatus' but am not sure where or how to do that in the code.
View 1 Replies
View Related
Oct 10, 2014
I'm using pbauldy's code to filter a report by a multi-select listbox. The only issue is..how do I include more (7 to be exact) listboxes to the code? It seems the OpenReport vba is only allowing 1 where clause?
Code:
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'add selected values to string
Set ctl = Me.VP_ListBox
[Code] .....
View 7 Replies
View Related
Jun 29, 2013
Send only one or two pages of a multi-page report. I have a report that has three pages. I only want to send the first two as a pdf file. Can this be done with VBA? I know it can be done using Exporting Data on the External Data ribbon, but how can I do it via VBA?
View 4 Replies
View Related
Nov 15, 2011
I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.
I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".
Private Sub Run_Report_Click()
Dim StrWhere As String
Dim varItem As Variant
If Me.Market.ItemsSelected.Count > 0 Then
[Code] .....
Questions:
1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box.
2. What is the syntax to add in the other combo box selections?
View 6 Replies
View Related
Jun 5, 2014
i have created a MultiSearch query witch puts my results in a list box.under it i have created a button wich i want to use to print a report with the criteria i select from my list box if there is more than one result.
Following is the Code i used for my button
On Error GoTo Err_Command60_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ReportLable"
stLinkCriteria = "[ProductID]=" & Me![SearchResults]
DoCmd.OpenReport stDocName, , , stLinkCriteria
Exit_Command60_Click:
Exit Sub
Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click
Every time u press the button i get a message syntax error(missing Operator) in query expression
Now i have not used any code in my Query except for
Like "*" & [forms]![frmSearchFor]![SrchText] & "*"
Where i made the error
View 3 Replies
View Related
Mar 9, 2013
I know that multivalue fields are not the best to use, but I have a simple database that they work fine in. My question is this: Is there a way to show the entire list of choices in the multivalue field on a report? Not just the ones selected, but all of them, whether checked or not? The multivalue field has a list of names and the user selects each name if they repond to a incident (fire, car accident, etc.). My report would show the entire list and count number of responses by each name during a date range. I want to show all of them on the report, with those not selected showing "0".
View 3 Replies
View Related
Oct 23, 2014
I'm trying to have users enter multiple IDs from a table into a text box separated by newline chars, and then (once they press a button) have some sort of macro or code read each line and pull a report I've created based on the ID. Is that possible?
Example:
Table
ID
DataPoint1
DataPoint 2
DataPoint 3
1
d1
d4
d7
2
d2
d5
d8
3
d3
d6
d9
Text Box (User will enter values and hit OK)
2
3
Report
ID: 2
DataPoint 1: d2
DataPoint 2: d5
DataPoint 3: d8
-------Page Break------------
ID: 3
DataPoint 1: d3
DataPoint 2: d6
DataPoint 3: d9
View 2 Replies
View Related
Apr 5, 2013
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
View 8 Replies
View Related
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View 5 Replies
View Related
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 Replies
View Related
