Is it possible to have three different report footers on the same report? I want to show one footer at a time depending on a condition met in the detail section. I already know how to check the condition but I need to know how to do the rest.
Ex:
if condition1 = A then
me!reportfooter_1.visible = true
me!reportfooter_2.visible = false
me!reportfooter_3.visible = false
end if
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
I have an access form. It has
-Two textboxes with client first and last name
-Two textboxes with a contact 'email1' and 'email2' for the client
-A combobox with a list of templates to use for the email.
-A checkbox to include a copy of the statement on the email.
I want the user to be able to press a button which does the following
-Sends an email to both 'email1' and 'email2'.
-Attaches a secific report as PDF if the user has selected the checkbox
-The body of the email includes a greeting line using the clients name from the record.
-The body of the message includes below the greeting line a html email template depending on the users combobox selection.
I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.
Also, it appears in the old Database Table once imported as:
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
I have two drop-downs on a form along with a child form that shows data based on a query. If both drop downs have selections, say date and dept. then the query would change and the child would show the data - that works fine, but what if one of the drop downs didn't have a value selected, as in i select a date but just want all departments so select noting from the second dd - how would i write the query to reflect this optional selection?
View 6 Replies View RelatedI have a form with an option group that depending on which one is selected, opens a report with a specific query as it's source. Part of the query looks at a listbox on the form. Here is the criteria for a field: [Forms]![frmOnSearch]![tboCollDev]
I want to make this criteria OPTIONAL. So if there is nothing selected in the listbox, the query should not look at that criteria. FYI, the value that is returned from the listbox is numeric.
Thanks
ScrmingWhisprs
I've had a look around this forum and haven't seen anything really matching my needs, but please feel free to point me in the right direction.
What I am trying to do (using macros & queries - very new to SQL / VBA) is filter by multiple fields, but also allow the user to choose to filter by different combinations of fields - such as searching for first name and suburb, or salesperson and customer source, or first name and salesperson, or suburb and customer source, or three options or one, etc etc.
There will only be a certain number of search fields, probably five or six, but how do I get the query to recognise that if one of the search boxes is blank, it should allow all records in that field and only search by the other criteria, but if all the search boxes are filled in, the results must match all the criteria?
Does this make any sense?
PS - I am planning to use a command button as an "Apply Filter" so that the filter only updates after the user has specified all the options they want.
Hi,
Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.
Any help would be much appreciated.
I am using this criteria with my calender on my form...
>=([Forms]![frmReport]![txtFrom]) And <=([Forms]![frmReport]![txtTo])
but I have 2 other date fields that I would like to query, so how do I go about making this optional?
Normally with a simple reference like:
Like([Forms]![frmReport]![txtFrom]) & "*"
with the form control's default value as * I tried working with that configuration but either it is not possible or I have the wrong syntax.
I'm open to a VB solution if anyone has one?
Note: I would only be using one of the date fields at any given time
thanks
I currently have a bound form that adds a new student, however, I've decided I don't like bound forms as weird stuff can happen (like when the user exits in another way than I anticipated) so I'm making an unbound form which will add the student when a button is clicked. I already have this working on some other places so this should be no problem anymore.
However in my database a lot of student information is optional and only a first and last name are required (and ofcourse an autogenerated PK). So my question is how do I deal with these fields that might or might not be empty?
I could make a fairly big If ... ElseIf... construction where I slowly fill a String with all the optional fields but I was wondering if maybe there are some better ways to approach this?
How to get a third combo box on a form to be optional.
I have a form with two combo boxes which filter out the results on a query.
However I want the third combo box to be optional i.e. if it is blank then just the results of the first two combo boxes are shown. If the user selects all combo boxes then obviously the results are based on all combo box selections.
I have a subform and when you double click on any field it remembers 3 fields from the record you are in and stores them. I have 3 fields called [section], [losthrs], [stdhrs]. It works perfectly and stores the data from [losthrs] and [stdhrs] fields, but throws up "Argument not optional" for the [section] field.... Can not see a reason why, it is just a simple text field...
I have even tried 'pausing the code' and using the immediate window to show the field values, again losthrs and stdhrs are fine, section gives "Argument not optional".
Windows XP
Access 2007
I have followed Martin Green's steps (fontstuff) for creating an audit trail and am getting a compile error when I check my code.
This is my code and the error highlights 'Call AuditChanges' as the problem.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges("ImprovementID")
End Sub
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies View RelatedHow do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
I've done this once entirely by accident and can't seem to duplicate it...
I have a report. It has the following:
Report Header: Logo and title
Department Header
Supervisor Header
Group Header
Detail
Department Footer: Totals
Report Footer: Overall Totals for all departments
Here's my question.
I have combo boxes on my main form that filter this report. The combo boxes are referred to by the query that runs the report. How do I get proper unfiltered overall totals in my report footer?
I have a report that displays incidents, their details, consequences and a photo. Among the details is a severity rating high medium or low, I have been asked to make the report shorten the records which have been given a low severity (because it takes up as much space as the more important/severe ones).
The only method I can think of is to use the onformat event, to shrink and make invisible all the fields that I don't want to see if the severity field shows 'low'.
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.
I have an odd thing happening with my reports. There is a main report that gives class information (from query). There are two subreports (from queries also ) with scores (one for each type of test). There are some unbound controls on the main form that display a count of how many scores are recorded which are totals from the subreports. The controls all report the correct numbers however, when I scroll down to view each record, some of them change to #error, or #name, or similar for a moment. Sometimes they will stay in error form when I stop scrolling but change to correct if I scroll up just a bit. Is this because the controls won't calculate unless that report record has focus?
View 1 Replies View RelatedIs there a way to hide a label on a report if the sub report count is 0?
View 2 Replies View RelatedOn the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):
From Date
To Date
Port
Vessel
Voyage
---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------
However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.
I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
[Code]...
Is there a way to have say five different reports that give out information and at the bottom the totals. I would like to take each of those separate reports to create one report with just the totals.
View 3 Replies View RelatedTrying to create a calculation in a field (text25) that excludes weekends and holidays. I am getting an argument not optional error.
Code : Me.[Text25] = GetBusinessDay([Due_Date - Result_Date])