Reports :: Dynamically Include Columns Based On Check Box
May 28, 2013
I have a report with many columns in the detail listing data, and their associated titles above in the page header.What I would like to accomplish is the ability for the user to check boxes off on a form which will remove/add specific columns to the report. The form will essentially be a "build your report" style, with a button on it opening the print preview of the custom report.
Im assuming this is accomplished by the "on load" part of the form through VBA. How is the sizing handled? If the user un-check's a box to remove a column of data, how is the header and detail data "moved over" or re-arranged to accomplish a relatively decent looking layout? Any example or example code to deal with one column/setup (which I can copy and modified)??
View Replies
ADVERTISEMENT
Aug 13, 2015
I have a report that pulls a date from a query, but they'd like an option where if they don't want to enter the date, they can check one of 2 checkboxes. Check box 1 would return "N/A" and checkbox 2 would return "TBD.I've never had to do anything with more than 1 checkbox.
View 5 Replies
View Related
Mar 16, 2014
got the following issue:
There are two tables A und B.
Table B contains columns like 1FC, 2FC,...., 12 FC
standing for forecast sale quantities.
Now I want the user to decide how many FC columns he wants, always starting with 1FC.Let's say he wants three FC columns.Now I want to map these 3 FC columns to the table A. Both tables contain column Product_Classes.Can I shrink table B to the 3 FC columns ?
View 3 Replies
View Related
Mar 5, 2007
Hi
I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al
Each table currentnly has 3 columns.
I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused:
Thanks
View 1 Replies
View Related
Aug 4, 2012
I have table with following Columns:
Banker
Status
Week1
Week2
Week3
Week4
......
Week48
Week49
Week50
Budget
YearMonth
I need to extract the value only for few columns dynamically from result set. I have written following code for the same:
Code:
i = 0
strColumnsSQL = "Week" & CStr(gsReportingWeekNo)
Do While i < 4
i = i + 1
strColumnsSQL = strColumnsSQL & ",Week" & CStr(gsReportingWeekNo) - i
Loop
[code]....
View 1 Replies
View Related
Nov 12, 2005
I coulnt figure out how to reply to a reply. This is a follow up question to a previous post.
I can now create a new column in my access db through my web form.
Here is the script:
ALTER TABLE FAQ ADD City varchar(30)
this adds a new column called city.
Here is my problem:
Every time a user asks a question, that question will be in the new column created.
I need the newly created columns to have unique names.
I tried creating a form with a text file (to simulate where they type in their question) then post to post.asp.
post.asp holds the script.
I replaced city with ::T1:: (the variable of the text file), but got an error.
Creating columns dynamically is worthless unless there is a way to make the column names unique.
Can anyone shed some light on this problem for me?
Much appreciated
Mike
View 3 Replies
View Related
Feb 18, 2014
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies
View Related
Jun 27, 2013
Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.
In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.
View 4 Replies
View Related
Nov 12, 2013
I am creating a PDF version of a Report so that I can send it as an attachment. I use the following code to do this:
Private Sub EmailWorkOrderQuote_Click()
On Error GoTo Err_EmailWorkOrderQuote_Click
Dim stDocName As String
Dim mFilename As String
Dim mEmailAddress As Variant
Dim mCompanyName As String
[Code] .....
I first create the report with a specifically generated name and save it to a folder.
I then do a SendObject to send the actual email. Unfortunately, it generates its own name for the file that it attaches and I don't think it is possible to specify an attachment.
Anyway, how to include the name of the PDF report in the actual report preferably in the footer.
View 5 Replies
View Related
Jun 20, 2013
I have a report "AFR" that includes workorder fields. I would like to also include the subform information for the parts and labor.I was able to put the subforms on the report but it will only display the first part and not all parts from the parts subform.
View 1 Replies
View Related
Apr 29, 2015
I'm trying to export either a report or a query to excel with a field name that has special characters "()". I wouldn't normally ever do this (everyone knows not to use special characters) but we're using this to import the excel document to a website, and the field name MUST be what they specified in order for the import to be successful. Is there any way to rename the field name at export, since I can't use special characters on the query or report itself?
The field name is currently DepType, but it must be
"
*Dependent Type
Spouse/Partner or Child/Dependent
(Required for Dependents only)
"
EXACTLY or it will not import.
View 1 Replies
View Related
May 31, 2015
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
View 4 Replies
View Related
Oct 14, 2005
Hi.
Is there a way to set the record source of a textbox control from an access report to something else then a field from a table or query ? I didn't found one.
I wanna to display in a report some processed and parsed data from a memo field (the processing and parsing is done in a function from a module). In the report, I wan't to display this data, BUT can't, because the only dinamically value that can be changed to a textbox is record source (I cannot change it's value when the reports opens), and the data processed isn't stored anywhere in a table ... so ... I'm stuck. I've also tried to display the data in report in a label (not in a textbox) and dynamically resize the label, but at some point, I riched the maximum label height :((
Is there a way to do this ???
Thanks,
Mihai.
View 1 Replies
View Related
Nov 24, 2004
I have created a report to display a bill of materials (boms). The report works as I have designed. I have also figured out how to filter the report using the docmd.openreport vbasic call with filters. This works fine. I use a form to select the filtering I need in the form of 2 Listboxes populated from tables. The form opens, the report generates and displays in preview mode. What I cannot do is the change the title text (Text Label on the report Header). I would like to use the selected items in the listboxes as sources of text for these controls on the report so I can use the report over and over for different boms. ANy help would be greatly appreciated! Vbasic I hope!
View 1 Replies
View Related
Apr 15, 2014
I am creating a database and need to produce a report that will form an itemised quotation.There can be up to 8 items on the quotation along with their associated costs to the customer. I am trying to make the "item" field dynamically expand if necessary when longer text is entered into it and then expand the corresponding "Totals" fields to match the new size.I have tried using the "Can Grow" property however for some reason the formatting does not cascade down to the fields below and the "Totals" fields remain the same size.
To try and explain better I have included a screen shot of the relevant part of the report and how it looks when printed. I should point out that the "QuotesQuote" box at the top of the screen is set to "Can Grow" and when that expands the boxes below automatically move to accommodate this.
View 2 Replies
View Related
Mar 30, 2013
I need to validate if a Field in my table is NULL, details of which will not be included in my report.
I attached sample report wherein the data is sorted by EXPIRATION DATE, first rows displayed the data of NULL EXPIRATION DATE FIELD ...
View 3 Replies
View Related
Jul 1, 2014
I have a table in Access 2010 containing the results of a survey. One of the fields in table is the actual quotes from customers who gave direct feedback. I have created a simple report containing all of the quotes. My manager would liek them included in a summary Word doc but wants quotation marks around every quote. It's too many to manually after the report is exported to Word. Is there a way to include quotation marks around the field in the report design view?
View 3 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
Nov 6, 2013
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies
View Related
Feb 12, 2008
Hi,
I want a user to click the report, and an input box appears asking for a product number. This product number is used in a recordset (VBA code in the report) to fill the text boxes in the Detail section.
Code:Private Sub Report_Open(Cancel As Integer)Dim rst As DAO.RecordsetDim db As DAO.DatabaseDim itemCode As StringSet db = CurrentDb()itemCode = InputBox("Enter the Item Number", "Complete Catlog Prices")Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Catalog Prices Complete] WHERE Prefixprodno = '" & itemCode & "'", dbOpenDynaset)Do Until rst.EOF Me!txtCat = rst("Volume") 'error 02448 cannot assign to this 'Me!txtPrice = rst("Price") rst.MoveNextLooprst.CloseEnd Sub
I'm sure my text box is called txtCat though. Is it an array I should try to fill? If so, any suggestions :S
Thanks,
View 3 Replies
View Related
Jul 13, 2015
Could I change my unbound textbox (txtperiod) on the Report to display the time period.i.e.
Weekly, Monthly, Quarterly
Criteria is BeginDate and EndDate on main form.
View 1 Replies
View Related
Jun 28, 2013
I must use XP/Access 2003 to solve this problem for reasons I won't get into.
I have a report with a Group by on City.
The users need to be able to sort by Coordinator within that OR by Client Name based on a selection they make on the form that opens the report.
-- I have tried to set .OrderBy
-- I have tried to set .RecordSource to different queries with order by hardcoded in them (I only have 2 sort options fortunately)
Is there a way to dynamically change the "Sorting and Grouping" defined in the report? This seems to be the only sorting that the report is responding to.
View 1 Replies
View Related
Oct 18, 2014
I am trying to add a feature in my database dashboard that shows a list of hyperlinks that are the most frequently used forms and reports based upon the user. I can figure out how to capture the event each time the form or report is opened and update a record to increase the count, and build a recordset that will query the table filtering by user and order by frequency...
However, I am stuck on building the hyperlinks that will point to the correct form or reports dynamically.
Specifically, I am not able to update the subaddress using VBA. I get an error that the hyperlink cannot follow to the new form...
Here is my code:
Code:
Private Sub Form_Load()
Dim strUser As String
Dim strApp As String
strUser = Environ("username")
strApp = "frmTest1"
Me.txtUser = strUser
Me.txtLink3 = strApp
Me.txtLink1 = strApp
Me.hypTest.Hyperlink.SubAddress = strApp
End Sub
View 3 Replies
View Related
Apr 18, 2013
I have developed a database using Access 2010. This is split into a Front End and a Back End.
In the same folder as the Back End I store a number of user modifiable files in either bitmap (.bmp) or MS Word (.doc to retain compatibility with older versions).
Unfortunately, the drive letter where the back end resides will change from location to location and I can't do anything about that.
I have set up a function that returns the location of the folder where the docs reside irrespective of the back end drive location, and this works perfectly with OLE linked pictures but not with OLE linked Word documents. User changes show correctly in Word document changes at locations with the original drive assignment but other locations show only the original doc contents even though they have been changed on the local back end location. - even if I delete the Source Item info on the report!!
An example of the code I am using for the Source Item is <GetBackEndPath() & "Footer.doc"> without the angled brackets of course, where GetBackEndPath() is a global variable storing the location of the documents folder ending with a back slash.
I have tried the above in the reports Source Doc property but Access won't accept this and says it isn't valid for this property.
View 2 Replies
View Related
Dec 2, 2013
I would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.
If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.
I found multiple examples on the internet for using multiple criteria but something similar to what I require.
View 6 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related