Conditional Macro
May 13, 2006
I am trying to do a conditional macro. I have a form that has a calculated field (Order Total) and based on this amount (greater than $2,500 or less than $2,500) I want different events. If the amount is greater than $2,500, I want the action canceled but I want a report printed out. If the amount is less than $2,500, I want the action to continue on. I want a report printed. I want the report e-mailed to others. I am a novice Access user. Please help, how do I do this? Thanks You
View Replies
ADVERTISEMENT
Nov 2, 2006
I have a form named frmtestpkg which has a subform named pkgsubform. The control the subform is in is called pkgsubform1. I have written a macro which works very nicely to hide or show the field [Days2process] if I attach it to "on current" on pkgsubform and open pkgsubform as a form. If I try to open pkgsubform as a subform I get a message stating "The object you referenced in the Visual Basic procedure as an ole object isn't an ole object."
How do I change my macro to refer to the form and controls as a subform?
my Macro looks like this:
condition: [Forms]![Pkgsubform].[CrPkgStatus]<>"Accepted"
Action: SetValue
Item: [Forms]![pkgsubform]![Days2Process].[visible]
Expression: No
condition: [Forms]![Pkgsubform].[CrPkgStatus]="Accepted"
Action: SetValue
Item: [Forms]![pkgsubform]![Days2Process].[visible]
Expression: Yes
View 2 Replies
View Related
Dec 8, 2013
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"
[code]....
View 1 Replies
View Related
Apr 17, 2006
Good morning, all...
I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.
Could someone please get me started with the code to accomplish this?
Thanx in advance for your assistance.
Karen :)
View 2 Replies
View Related
Jul 13, 2005
Trying to setup a conditional format for a field on a form based on conditions from another field.
I have a form with [Field1] and [Field2]
I have a 3rd field called [New SMF]
What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.
How can I do this?
Jon
View 4 Replies
View Related
Apr 18, 2006
Hi Guys,
I've a question and I hope you can help me out.......
I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?
Thanks a lot for you help!!!!
Ciao Ciao
View 1 Replies
View Related
Aug 13, 2007
Hello
On my Database I have used Conditional Formattion to change the colour of the qty to highlight when the qty is getting low however its not as good as I would hope as there are different type of definition of quantity (D of Q) e.g. Each (single items such as a screw) and Roll (such as cord and tape)
Conditional formatting doesnt work well because if I have it as less than 10 the qty is in red, its ok for the each items however not the roll as 10 rolls of tape is more than we would need.
I am just wondering if anyone knows what the code would be to change the colour depending on the qty and the D of Q
All i have got so far is:
If me.qty <10 and DofQ = EA then
Now im stuck for the change colour part
Any help would be great
View 13 Replies
View Related
Sep 2, 2007
Hi Guys,
I'm after an easy way on being able to change the colour of a field once it a certain figure is reached
For instance
Standard field colour for anything 1-99
100+ changes to red
Would any be able to help me out please?
View 2 Replies
View Related
Mar 2, 2005
hi all,
how do i create view based on a condition.
For example my database consists of a Employee table
*Token ID
Name
AGE
Location
Division.
Now the users of this table would be employees from different divisions.
And i want to create a view for each division for records only belonging to that particular division. And use this view to update records of that division.
Or should i create tables for each division and then give access of each table to the respective division user.
Any response is welcome..
View 2 Replies
View Related
Sep 8, 2005
Does anyone know if there is a way in a report to display an entire row in a different color? I'm running a report based on a query and I need specific information to come up in RED. I read about conditional formatting but you can only apply it to a field in a report, but not to an entire row. Let's say I have a group of managers and I need to know the ones that are making more than $50,000 a year. I don't just want the salary to come in RED, I need the entire row (like name, last name, title, date of hire, salary, etc).
Any ideas?
thanks!
View 2 Replies
View Related
Jul 13, 2006
Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)
I have tried to acheive this using the code below:
[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--
IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--
when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?
Many Thanks
Ian
View 4 Replies
View Related
Aug 19, 2006
I have the following situation:
1. A Homeroom table which includes student id, academic year, and homeroom.
2. A Student Grades table which includes student id (linked to Homeroom table), term, academic year, progress grade (15 pts), and progress grade (A-F).
The contents of these tables are imported from excel, and that works fine except for one thing: in order to keep the field [progress grade (15 pts)] numeric, I had to put a 0.0 as a grade for any student who got an "I" (Incomplete) or "N/A" as his or her progress grade.
The problem arises when I want to produce an average progress grade for the student for that given term/year. How do I not include the 0.0's of students who got I's or N/A's?
Any help would be much appreciated.
View 7 Replies
View Related
Apr 14, 2007
I want to join two tables together base on two criteria.
Table Customers
(Ids, Name, ...)
Table Addresses
(Ids, Billing Address, Shipping Address...)
I want to join Customers Table to Addresses Table base on this condition,
If Billing address exit,then join Table Customers with Table Addresses,
If Billing address is NULL, then join Table Customers with Table Addresses.
Can someone help me understand the query syntax?
View 1 Replies
View Related
Aug 24, 2007
My Access is very rusty and I really can't get my head round how to do this!
I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.
This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.
I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?
Thanks
View 3 Replies
View Related
Oct 10, 2007
Hi,
I need a help. In my Store table I have Store# and MaxRange. In my CenterStore table also I have Store# and Max Range.
Data in Store table will be like
Store# MaxRange
10 1
20 2
Data in CenterStore will be
Store# MaxRange
10 A
20 B
The Store# is common between the 2 tables but the MaxRange differs.
Now in my employee table I have EmpId, Grade and Store#
I have SalaryRange table which has Grade and MaxRange.
My query is how to attain this with access
Select E.EmpId,E.Grade,MaxRange
From Employee E
Inner join Store S
On E.Store#=S.Store#
Inner join CenterStore CS
On E.Store#=CS.Store#
Inner Join SalaryRange SR
ON (E.Grade = SR.Grade AND S.MaxRange = SR.MaxRange)
OR (E.Grade = SR.Grade AND CS.MaxRange = SR.MaxRange)
I know this is possible in SQL. What is the equivalent in Access 2003?
View 2 Replies
View Related
Oct 23, 2007
I currently have two queries. One calculates emissionsperhour based on a results field and the other calcualtes emissionsperperiod based on the emissionsperhour query and the inspection date. I need to go a little deeper and seperate the emissionsperperiod into two categories. Category 1 will sum the emissions for the year up to 5 (Results). Category 2 will sum the emissions for the year from 5 and over.
My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5. Cat2lbperhour needs to first look for any results >5, subtract 5, then calculate lbperhour based on the difference.
Any ideas as to what this looks like in SQL?
Thanks
View 8 Replies
View Related
Nov 30, 2007
i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.
when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.
so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.
thanks
View 1 Replies
View Related
Dec 27, 2007
Hi,
I have an access database that contains information on donations made to our non profit organization.
I'm wondering how I query for people who have donated in categories (a,b,c) between 01/01/05 - 01/01/07, but have NOT donated in categories (a,b,c) in >01/02/07.
In other words I'm trying to sort out the people who have donated to us in certain categories in the past, but have not yet done so this year.
There are fields in my tables for donor id, as well as donation type, and donation date.
Any help or info is appreciated!
View 7 Replies
View Related
Mar 13, 2005
I have 2 subforms on a main form. I would like to compare the value in field 1 og subform 1 to field 1 of subform 2. I can't seem to get the conditional foramtting between the 2 subforms to work. Any ideas?
Jim
View 1 Replies
View Related
Mar 22, 2005
Good afternoon everyone
I am new to this forum. I have not done access development for about 6 years so I appologise for my rustiness.
I have created a form based on a query. The form is of type Datasheet. I have assigned Conditional formatting to the grid fields based on an expression evaluating a check box. This works fine and when I open the form the conditional formatting works great.
However, I am now trying to add this form as a subform in A.N Other form. The parent form has no datasource and is purely there to display some text values linked to unbound controls. I did this because apparently you cannot show form header for a datasheet view.
When I insert my child form into the parent there is no logical link. The behaviour of the form and subform works fine but the conditional formatting does not appear in the subform grid.
Why is this and how can I best sort this?
Any help would be appreciated, but please be aware I am not fluent in VB as I have been away for quite some time.
Bets regards
Philm
View 1 Replies
View Related
Jun 17, 2005
I have a subform which shows records... usual stuff. However, when I turn on the scrollbar, it always shows the scrollbar... also when there are no or a few rows in the subform.
Can I make the scrollbar of a (sub)form conditional? -- so, if the form needs more than so many space because of records shows: it turns on the scrollbar.. else: no scrollbar.
Any help is appreciated,
Jazz
View 1 Replies
View Related
Nov 3, 2005
I'm kind of banging my head against this one. I've searched the forums and outside, and think I have the idea, but Access say it can't find the forms.
I added an "Notepad" for end user to make notes of each clients if they wanted to. It is a pop up form, has its own table, with ClientID FK to the primary table's PK of same name.
The code I tried is
DoCmd.OpenForm "Notepad", , , Forms![Notepad]![ClientID] = ClientID Or acNewRec
It wouldn't accept an If... Else statement which makes it problematic, because I only want one note per client, so need to make sure Access check whether there is already a record that corresponds to the client, pull that one up. If there isn't any, then create a new record.
The other code I used allowed me to run and can get the ClientID, but does not create a new record when there should be a new one for a new client. It was
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Notepad"
Let Forms![notepad]![ClientID] = ClientID
End Sub
If I add an Do.cmd.Gotorecord, , acNewRec, it returns a error message that there would be a duplicate record, which is expected.
So how do I get Access to simply
1) Check if there is a note already written for so and so client then pull it up
2) if there isn't any for that client, create a new record
View 3 Replies
View Related
Mar 8, 2006
Hello!
I am stuck with a problem in my database.
I got a table that is looking like this:
On_Time
95%
96,88%
100%
100%
97,56%
There is more data there but this is the field i want to use. I got a form where i want a little box or something to be coloured in red if the average of all those percentages are below 95%. If its above 95% it should be green.
Can anyone help?
View 1 Replies
View Related
Apr 25, 2006
the following code retrieves a default value for a textbox on a continuous subform:
Me.BodyText.Value = DLookup("[DefaultBodyText]", "[tblHeading]", "[ID] =" & Forms![frmClient]![sbfrmClientDocument]!Heading.Value)
This works nicely except it overwrites any value typed into .
[B]So how can I code it so that it will not perform the DLookup if there is a value already in [BodyText]?
I've tried using various Nz() without success.
Many thanks
View 8 Replies
View Related
May 15, 2006
Hi. I have a problem with my conditional formatting. I have two fields field 1 (hours worked) and field 2 (hours per task). Field 1 shows the hours worked based on the difference between a start and finish time and field 2 shows the hours per tasked based on values assigned to the 10 task fields. I set the formatting of field 1 to show up in red if it did not match the value of field 2, which works fine most of the time. However, for certain values i.e. 5 or 6 field 1 still shows up in red even though field 2 has the same value i.e. both equal 6.
Could someone explain to me why this happens and offer a solution if possible.
Thanks for any help.
View 7 Replies
View Related
Jun 5, 2006
I am trying to make a form that will append when you click on the button I have made, but i want to make it so that the form wont let you append the table until you have filled a few "Required" fields.
I am new to VB and am pretty much clueless, please help!
View 2 Replies
View Related
