Form To Select Report Content
Nov 24, 2005
Hello,
I have a query set up as the record set for a form containing combo boxes.
The form has four combo boxes with various drop downs. I have linked these combo boxes to the query using this syntax in the query:
[Forms]![frmToolStats]![cboModel_Number]
I have a preview button report on the form to load my report template to be populated with the combo selected info.
If I fill all four combo boxes with specfic info then my report displays all data records related as you would expect. For example I can select A 'Tool Type', 'Manufacturer', 'Model Number' and 'Tool Condition' and my reports will show me three test records relating to these specific parameters.
How do I set the code to allow me to complete only some of the boxes and return the records i.e. only 'Tool Type' and 'Model Number'.
I am trying to get my report to lift the data for test records for each specific tool by model number and tool condition and then do some calculations. This forms the full report.
I have tried this code:
If Not IsNull(Me![cboToolGroup]) Then
where = where & " AND [cboToolGroup] Like '*" & Me![cboToolGroup] & "*'"
End If
Can anyone help?
View Replies
ADVERTISEMENT
Jul 30, 2014
I am trying to work my way into putting the content of my report into the body of an e-mail. I have used the following code to do so but I keep on getting the error: "An Expression You Entered Is the Wrong Data Type for one of the Arguments"
Code:
Private Sub Command278_Click()
On Error GoTo ER
Dim db As Database
Dim rstOpenPurchaseOrders As Recordset
Dim strSQL As String
[Code] .....
View 14 Replies
View Related
Apr 16, 2007
Can I create contents of one field based on the contents of others?
I have a database of pc systems which we've tested and was wanting to generate a single text line to identify the individual pc
The single filed would contain data from the following fields
Job Number
CPU Type
CPU Speed
RAM
HDD size
eg
So the new field would contain "Job345-P3-1000-256-20"
Can this be done?
View 3 Replies
View Related
Sep 18, 2013
I have a report that is opened via a Form that lets the user choose a date to filter the report. The report opens in Print Preview mode.
What I'm attempting is to give the user an easy way to export the report once they've verified the report is accurate.
One way I've tried to do this was to use the OnClose event to execute a vbYesNo MsgBox giving them the option to export. The problem here is that I can't do the export while the report is closing.
Code:
Run-time Error '2585': This action can't be carried out while processing a form or report event.
I tried to move my MsgBox to the OnUnload so that could cancel the Unload, but was met with the same results.
Code:
Private Sub Report_Unload(Cancel As Integer)
Dim Response
Response = msgbox("Do you want to save a copy of this log?", vbYesNo, "Export to PDF")
If Response = vbYes Then
Cancel = True
DoCmd.OutputTo acOutputReport, "rptWatchLog", acFormatPDF
End If
End Sub
Some research indicates perhaps the DoCmd.OutputTo is happening to quickly. Would including some type of pause in the code execution between the Cancel = True and the DoCmd solve my problem? Though frankly, even if it did it doesn't feel very elegant. I also recognize that I'd need to reinitiate the Unload>Deactivate>Close process after the export completed.
I initially began by having an Export Command Button on the form they use the choose a date, but was unable to have the exported report honor the user supplied filter from that form.
Here is the code from that form that is applying the filter:
'Open Watch Log Report with chosen date as filter
Private Sub cmdOpen_Click()
'use date even though it's not saved anywhere
If Me.Dirty Then
Me.Dirty = False
[Code] .....
View 8 Replies
View Related
Jul 8, 2013
I am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
View 3 Replies
View Related
Feb 4, 2015
how many elements matching to my primary elements from any records of my query and count match, if some element doesn't match then I need to add it to my primary elements, then at the end (rst.eof) count how many primary elements I have.
E.G
id colours
1 blue;red
2 purple;blue;green
3 red;violet;purple;blue
dim matching_elements as long
dim primary_elements as string
dim TheNumberOfPrimaryElements as long
First of all, if I open recordset primary_elements is empty so I need to assign a value form first record.
primary_elements = rst!colours (so primary_elements = blue;red)
Now I can start comparing my primary_elements with second record:
matching_elements= 1
primary_elements = blue;red;purple;green
comparing my primary_elements with third record:
matching_elements= 3
primary_elements = blue;red;purple;green;violet
It's my last record so I need to count primary_elements
TheNumberOfPrimaryElements = 5
I need "matching_elements" to count other function in my application.
View 7 Replies
View Related
Nov 25, 2013
As I began thinking about the data that I need to include in one of my reports I relized that I need to gather some extra data.Each design change has a lifecycle with 7 basic states from not started through to closed. States 4, 5, and 6 have two posible sub-states that I need to capture and report. It is almost like having options.
My data entry form already records the 7 basic states. What I would like to do is have another field that records the sub-states if the design change is in one of those three states.
Will a ComboBox do this?
Do I need a test routine for the After Update event in the first text box? Something that will check for states 1 - 3 and 7 move on to the next field and if states 4 -6 require users to enter the sub-state.
Would a nested if-then-else routine do the job?
View 1 Replies
View Related
Jun 12, 2005
I designed a Data Entry interface. I wish the system can automatically clear all the values of controls in the form after users click the 'save' button.
Is there any good solution instead of manually setting each control's value to null?
I tried Undo method, but it didn't work on either control (textbox) or form itself.
Many Thanks
View 2 Replies
View Related
Feb 7, 2014
I have an table with attachment field in which PDF files are attached.I need Access 2013 form to display PDF file content instead of PDF icon .While record changed should display PDF content for each record navigated
View 7 Replies
View Related
Apr 15, 2015
I am creating a database to house all the information for a box content label. For any given product it could have multiple items that go on the label.
For example:
My_Part_1
1. Part_1
2. Electrical Assembly
3. Instructions
4. Warning Label
I imagine my database being setup as follows:
ID Model# Contents
1 Part_1 Part _1
2 Part_1 Electrical Assembly
3 Part_1 Instructions
4 Part_1 Warning Label
5 Part_2 Part_2
6 Part_2 Electrical Housing
7 Part_2 Housing instructions
8 Part_3 Part_3
9 Part_3 Instructions
I would like to setup my form so that I could type in the Model # once and then type in each item that would go in the box. I don't mind typing in each item individually and then pressing an "add" button. But I would like it to show everything that is currently on that box content label and be able to delete items out of the label.
Is this possible? What would be the best way to do this?
View 1 Replies
View Related
Dec 31, 2014
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
View 3 Replies
View Related
Aug 13, 2015
I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.
I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.
The code I am using is as follows:-
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
[code]....
View 9 Replies
View Related
Jul 26, 2012
What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.
On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.
I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.
Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.
View 5 Replies
View Related
May 24, 2015
We have a shift log that includes both personnel actions during any given day as well as operational actions. (We recently switched from a word document to an Access Database to allow multiple users to input events while another has the logbook open already (which you couldn't do with Word))
At the beginning of each day, my manager reviews the previous days log and forwards up pertinent data (some personnel, some operational) to our higher authorities. Is there a way to allow him to select which records he'd like to include on that higher-authorities report straight from the local-level report?
I'm not a fan of allowing him a "Save As" feature because that kind of defeats the data integrity purpose of an events log where he could save as an RTF and then edit any of the log entries without any checks or balances.
View 2 Replies
View Related
Feb 26, 2006
in my form I created an unbound field with a requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form's unbound field to a field in the table?
your help is very appreciated.
Regards,
CS.
View 3 Replies
View Related
Jun 4, 2013
I am making a very simple 'registration' database for a children's event in a couple of weeks.I the table/form there is a checkbox field called 'consent' which, if checked, indicates that a child can leave the event without parental consent.
There is a report printed on each child (a registration page which the leaders get a copy of). I would like on this report a 'red box' to appear if the child cannot leave without permission (i.e. the consent box is not checked). I would also like this 'red box' to appear on the form. I had thought of doing it this way - but I'm not sure if it's the best, or if it's possible:
Have a field in my table called 'consentindicator'. When the 'consent' box is checked, there is a period ('.') placed into the 'consentindicator' field. It is set to turn red when a period is present. That way, when the consent box is checked, a get a red 'box'.
View 12 Replies
View Related
Sep 7, 2004
Hello! I am building a report that tracks excessive call-in's for my hospital's employees. Data is entered into a form which then is stored in the table (duh!) "Call-In's." I have a query that lists all those who have more than 5 call-in's.
Here is the problem: In order to get the sum of callin's per person, I did a totals field in my original query and counted on Name. But now I needed to know the sum of each person's, and so I had to do a cross-tab query that included the name, department, the count (from the first query) and then I summed on the count field. What a mess, right? Also, I added a field for If >5, Yes, No.
I based my Excessive call-in's report on the final (above) query. Like this:
Call In's table
Name, Department, Date of Call in, Call in type
Call-in's query
Name, Department, Date of Call in, Call in type, Count of Name
Call-in's crosstab
Rows: Name (GROUP BY), Department (GROUP BY)
Columns: Count (SUM) and total of Count (SUM)
Excessive Call-In's query
Name, Total of count, iif >5, Yes, No
There are already 54 records in the table, one of which has excessive call in's (more than 5).
Is there a way to run a report that not only lists the person's name and number of call in's but also the date of each one and the call in type? Both of these are fields in the call-in's table.
When I try to combine my Call-In's query with my Excessive Call In's query I get the matrix. Example: I get the same record for however many callin's they have. Thank for your help. I'm so sorry this is complicated. I've got to be doing something wrong, or at least inefficiently. Does any of this make sense? Thanks again.
Kelly (the clueless, but I'm trying)
View 3 Replies
View Related
Oct 26, 2006
I have a database that contained the following fields. VacEarned,VacUsed,VacPlan (Yes,No). In my report, I grouped VacPlan field (which is yes/no field) together and sum it up by VacUsed. However, I'd like to put another formula in my report to subtract the the Sum of the VacPlan from VacEarned...I have such a hard time doing that..Is there way that I can put if statement in the report option that would do the following: If VacPlan is Yes, than Sum VacUsed and use it to subtract from VacEarned..is this possible..
I hope this make sense...
Thanks in advance..
View 1 Replies
View Related
Dec 17, 2014
I have a master table located on the backend of the server with about 3 fields:
Software
Version
Description
I have 4 databases, one for each software on the front end accessing this master table.
So far I have in the master table a dropdown to select 1 of the 4 software's, the version number and a brief description.
One the front end I have a button that when pressed will bring up the report based on the master table.
Can I have the report just select the software that it refers to. For instance if the datebase is Sony when I click on the report it only grabs the records with Sony on it. For my next database when I open up Sharp's database it only makes a report for the Sharp records.
How would I do this? Is this something I have to code or something I can do in criteria or do I create a query?
View 2 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
Sep 3, 2014
I have a form with a field called "comRpt" listing all the reports available to run on AfterUpdate procedure. I would like to add a command button when clicked the selected report shown in the "comRPT" field runs then it is attached to an emailed. How I can accomplish this?
View 2 Replies
View Related
Aug 22, 2013
How to proceed and what is the "accepted" version of events.I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.
My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report. So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!
Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...
View 2 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
Aug 22, 2015
there are 3 options Yes Somewhat and Not really I want to count the records that only have Yes.
View 5 Replies
View Related
Sep 26, 2014
I have a report reporting events that occurred today, using =Date() in the query.I want to have a control/box on the report which shows today's date by default, but enables me to select a different date with the outcome that the report refreshes and shows the events on the selected date instead.
Maybe I need a form for this although I would like to do conditional formatting so a report is better.It would also be good to be able to sort by column values, as in a table on a webpage with sort controls in the column headings which work just by clicking.
View 2 Replies
View Related
Feb 26, 2015
For the report in question all data is taken from one table named "Completions ". The fields I will be using are as follows:
Code:
[Surname]
[Firstname]
[Branch]
[Quarter1_A]
[Quarter1_T]
When the records are added, the Branch is added using a combo box. There can be several rows of data with the same Firstname and Surname but with different Branches.The report layout must look something like this.I have tried using DLookup to select the data based on the data in the Name text box, but this just gets the first result and duplicates it.
View 2 Replies
View Related
