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
Help m' help m'.....I'd like to create a form that allows the end user to fill in a field to search a table. Can't use the standard drop down box option. Needs to be open so it will search a few letters or a complete word. It needs to search the whole table.
Hi, I am currently doing an A2 level Coursework on Information Systems and part of this embodies a database and I am have a few issues with a certain aspect of the database that I am currently building.
The three forms that concern this answer are: "Frm_Main", "Frm_Customer" and "Frm_Projects".
"Frm_Main" is the main database form with a subform ("Sub_Main") displaying the content and buttons to the right for navigation etc.
Basically, within the "Frm_Customer" form that is viewed within the subform on the "Frm_Main" form there is another subform, this time showing projects specifically for that Customer.
I want to be able to select a record from the table, click a button and the "Frm_Projects" will open within the main subform on "Frm_Main" and use FindRecord to navigate to the record that was selected.
At the moment, I have it working properly, however instead of the "Frm_Projects" openning within the subform it opens as a new form.
This is the code that I am currently using for the button on the "Frm_Customer":
Code:Private Sub openrecord_Click()On Error GoTo Err_openrecord_Click If Forms![Frm_Main]![Sub_Main]![project_subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "There are no proposed projects entered for this Client." Else DoCmd.OpenForm "project_form" DoCmd.FindRecord Forms![Frm_Main]![Sub_Main]![project_subform].Form![Project ID] End IfExit_openrecord_Click: Exit SubErr_openrecord_Click: MsgBox Err.Description Resume Exit_openrecord_ClickEnd Sub
As you can see, I have also implemented an error message to generate if there are no records entered within the table subform.
If it's any use, here is the code I am using for the buttons on the "Frm_Main" to change the source of the subform ("Sub_Main")
Code:Sub_Main.SourceObject = "Frm_Customers"
Thankyou, ever so much for your time and it will be muchly appreciated.
When trying to use FindRecord I get a "Runtime error 2406 - the command or action 'FindRecord" isn't available now". The script up to that point is as follows:
Sub Test()
Dim Connection As New ADODB.Connection Dim Catalog As New ADOX.Catalog Dim rstRain As New ADODB.Recordset Dim ppn_0900 As Field
Set Connection = CurrentProject.Connection Call rstRain.Open("0800Rain", Connection, adOpenForwardOnly)
Hello, I would like to use the FindRecord Method, but something does not work. I would like to find a record which contains the data I entered in an unbound textbox in the form. The action should be started by a command button. As 'Find what'-object I used '=[text61].[text]. Thanks for your help.
I have a database / form with a hidden timestamp field [LastEdit] that is filled automatically with the current time Now() when updated. The purpose is that each time the form is opened I want it to "Goto" the last edited record.
I *think* I get around the find edit quotes because the smaller snippets below do work.
But the whole thing fails when I put it into one command as either one of these DoCmd.FindRecord DMax("[LastEdit]", "esn"), , , acSearchAll or DoCmd.FindRecord findit, , , acSearchAll perhaps because of the quotes around the variable.
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?
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.
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?
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
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.
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?
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?
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?
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?
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?
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.
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.
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.
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?
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.
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.
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?
