This is probably very simple to do, but, I am most likely missing the obvious...
I have 3 fields,
UnitPrice,
NumberPerUnit, and
NumberPerUnitCost.
I would like to have the NumberPerUnitCost field filled automatically based on what I have entered into the other 2 fields. I would also like to have this saved in my table as I will use this single unit cost when calculating some production costs.
Any and all help would be appreciated. If I am going about this the wrong way, please redirect me.
I have 10 fields in a form where I put in years. is it possible that if the first year is filled in as 2006 (for example) that the next nine years fill up automatically as 2007, 2008, ......etc.
I have a combo box for rooms: single, twin, double, suite, grand suite
This is found from a lookup table
In the next field on the lookup table is the cost per night for the room, how can i get the cost per night to automatically appear in a text box below the room type combo box.
I asked this the other evening and somebody said it was to do with making the table the bound source or something but i didn't understand.
OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID". "Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on. "Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
I am creating a database tracking physicians and their contracts. I currently have two tables: PhysicianT and ContractsT, with corresponding forms to enter information in them. I have an issue with the Contracts form; I want to be able to select a physician from a dropdown list (looked up from PhysicianT) and have Access autofill their Physician ID #.
PhysiciansT looks like this:
physicianID (AutoNum) name (Calculated) 1 Barker, Bob 2 Burgundy, Ron 3 Stark, Tony
Upon selecting Barker, Bob from my dropdown list, I want "1" to appear in the Physician ID # field in my Contracts form.
What I am trying to do is fill a field with a date until a new date is encountered. Basically, I have imported some csv data which only has the date when it changes. I have created a new field and have a row with only the date in it. Is there a way to fill the rows below the date row with that date until the next date row is found and then continue on with the next date and so on. Here's what the database looks like, basically:
Date Field1 Field2 Field3 01/01/01 null null null null DATA DATA DATA null DATA DATA DATA null DATA DATA DATA null DATA DATA DATA 01/02/01 null null null
Hopefully this makes sense and someone can give me a hand with this! Thanks so much!!
This is simple Access but I am quite simple so help would be cool all I want to know is how to put text from one field into another, but not every time. lets explain I have an order form and two fields , one date booked , and one date requested. if date booked is empty I want to copy the info from date requested to it, if its full I want Access to say its already booked.
its porbably something along the lines of where datebooked.txt = "nothing" then datebooked.txt = date requested.txt or something like that anyway
So I went searching through the forums and found a thread that provided coding for getting the # of years and months from two dates:
Function fAgeYM(StartDate As Date, EndDate As Date) As String 'Purpose: Returns the difference between StartDate and Date in full years and months 'To call: ' ? fAgeYM(#1/21/04#, #1/19/06#) 'Returns: ' 1 years 11 months
fAgeYM = Int(intHold / 12) & " years " & intHold Mod 12 & " months "
End Function ------------------------------------------
That's perfect for what I'm wanting but I'm doing it with Hire/Term dates. So I want to be able to see how long past and present employees have worked in the company. Obviously that makes the Term field Null at times. With the coding above it requires a date to be in the EndDate field. How can I change it to allow for a null field in which the field would essentially be the current day's date? [Now()]
i am the biggest of all newbies when it comes to access and understand only the graphical parts of the process (no SQL knowledge etc.).
how would i go about auto filling a certain field based on what's in another field? for example, i have a field called TimeZone that i want filled with either West, East, Central, or Mountain based on another field for State. can i build a query to automatically take care of any empty fields for TimeZone that haven't already been filled out by me manually?
if i need to use SQL, that's fine too as long as the directions are clear and precise as i have no prior knowledge.
I've imported some historic user data from some spreadsheets and I have a field which is a unique PIN code for each user. This is set as the primary key on my new table. Duplicates are not allowed. It's a text field with values ranging from 0005 through to 9576. The maximum allowed range of values will be from 0001 to 9999. There are currently only 300 records in the table so as you can see, there are lots of PINs available for use.
I'm building a form to allow a new user record to be created and want to automatically allocate the next available PIN. So if I was entering a new record now, the PIN to be allocated would be 0001.
how I can create an event for creating a new record that looks up the next available free PIN.
The database I am building is for Student & Alumni administration at a non-profit culinary school. In the various forms for entering student information and for defining tests and recording test scores, I would like to not have to re-select things like Class Number or Test number.
I was able to use the Test Number field as Link Master, Child in a Test Results sub-form where Test Parameters is the Master form. But I am not able to link with the Class Number, getting the error message: "The setting you entered isn't valid for this property" even though it is one of the Suggested Link Fields. The form record source is a query linking the Class Table, Test Parameters, Test Results and Students.
I noticed that I can eliminate both Class Number and Test Number from the sub-form. In the underlying query, both these fields are updated. However, the underlying Test Results table does have the Test Number but not the Class Number. While there is still a "queryable" link from the student to the Class Number, I would very much want the Class Number to be stored in the Test Results table.
Is there a way to accomplish this through the sub-form Link Master/Child property or any other/better way to inherit both the Class Number and Test Number in each Test Results record entered?
How do I set up a field so that when the record has been modified, the field is automatically filled in (or replaced) with a date? In other words if I open up my member's detail form, and go to Sally Smith and edit her information on 11 July 15, that date is saved into a field called last modified and then updated every time I go into her file and edit & save it.
I am working with an inherited database. When this database was created, a large amount of data was imported. Over the course of time, I have added additional fields for tracking information. One such field is "Date Started."
Unfortunately, there are almost 500 records without this information and that is skewing some report results.
I would like to do is insert the date of 9/9/1999 into all records that have no data in this field. (This date is well before the creation of the database and would serve to indicate old records, whether or not they are still active.) Copy and pasting isn't working, and I can't do a find and replace, since there's nothing to find.
I need to create a New Form control for this situation:
If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.
For example:
If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.
I currently use forms for adding new pupils, and updating pupil subjects/arrangements/levels.
I also have forms to search for specific pupils, and to create lists for faculty heads showing which pupils are taking subjects within their faculty and the arrangements we expect to provide.
I use the forms to run queries, which can then output to reports for printing.
Where I am currently having an issue is the faculty field in the tbl-SubjectLevelArrangements. (If I didn't have to report to faculty heads I would just leave it out, but management will insist.)
Currently I have a form with dropdowns for adding subject, faculty, level and arrangement manually. This is acceptable for the subject, level and arrangement because they are completely interchangeable and dynamic throughout the academic year as pupils may drop down a level, or change the type of arrangement they require.
However as faculties are inextricably linked to subjects, I want to remove the possibility of human error. i.e. when a user (me) chooses either geography, history, or RE, then the faculty will always be Humanities, likewise if the user chooses French, German, or Spanish, then the faculty can only be Modern Languages etc.
I'm convinced there must be a very simple way to ensure that the faculty field prefills based on the limited keywords available in the subject field, but I just cannot figure it out.
I made a report that have 'txtRemarks' field, I just want to get remarks automatically in 'txtRemarks' field based on the other field in report. that is why I used a function like below:
Code: Private Function Estd_Remarks(Estd_Point As Long) As String If Me.Estd_Point < 20 Or Me.Estd_Point = 0 Then Estd_Point = "Earlier Established" Esle Estd_Point = "OK" End If Estd_Remarks = Estd_Point End Function
And I wrote in properties 'On Format' event this code below:
Code: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Estd_Remarks = "Ok" Then Me.txtRemarks = "Ranked & Sortlisted" Else Me.txtRemarks = "Estd_Remarks" End If End Sub
When preview the report then it shows Compile error Argument optional
I have two tables, each has a "status" for a project. In the first table there is only one instance of each project name, in the second table there may be more than 1, will always have the same name but may have a different "Status" (field).
I need the Status field of the first table to be set to "Assigned" if ANY of the records in the second table pertaining to that project are set to "Assigned".
Can I do this with my table or will I need to do it with a query/form combination?
In Access 2013, in a table named DOCSDETAIL or form, I want the UTENTE (meaning user) field (Currency type €) automatically receives the same amount of PVP field (Currency type €) but can be modified as needed.
Example: Registration of documents in which the amount paid by the USER has two possibilities;
1. DifferS from PVP (Public Sale Price) 2. Equal the PVP
Looks like it might be used
= "Update your_table set your_field_new = your_field_old"
= "Update DOCSDETALHE set UTENTE = PVP"
But do not know where to put and if the syntax is correct!
I have an employee field in my purchase orders from and when I do stock transactions - i have an employee field against each item in the purchase order. I would like to just copy the employee field from the purchase order form automatically next to each product listed instead of having to keep typing the same thing.
the reason i am doing this is because when people come to take stock out - i would like to see there name against each item in the products from so people can not not take more than they ordered.
i have 3 tables: tblVolunteer, tblMedicalCondition, tblVolunteerMedicalCondition.
in tblVolunteer i have: volunteerID, volunteerName, HEALTHY (yes/no)
If a volunteer doesn't have a record in tblvolunteerMedicalCondition, i would like the HEALTHY field in tblVolunteer to default to 'yes', and if they do get a related record in tblVolunteerMedicalCondition, i would like the HEALTHY field in tblVolunteer to change to 'No' automatically.
i have 3 tables: tblVolunteer, tblMedicalCondition, tblVolunteerMedicalCondition.
in tblVolunteer i have: volunteerID, volunteerName, HEALTHY (yes/no)
If a volunteer doesn't have a record in tblvolunteerMedicalCondition, i would like the HEALTHY field in tblVolunteer to default to 'yes', and if they do get a related record in tblVolunteerMedicalCondition, i would like the HEALTHY field in tblVolunteer to change to 'No' automatically.
(*It really isn't code, I just couldn't figure out how else to keep the columns in place!)
------------------------------------------------------------------- Store Name Invoice Number Customer Name Customer Address Store1 1 Bob PO Box 55 Store1 2 Joe PO Box 789 Store2 3 Chris PO Box 1254 ------------------------------------------------------------------- On my input form, for entering a new invoice, when you select the customer name, I want the form to automatically fill in the correct address in the "Customer Address" field.
I think there has to be a way to do this because there is only ever going to be one address for every customer. And the same customers will be comming back very often.
I am currently handling an insurance operation. I have 5 Sales Executives (SE) who receives certain percentage of commission for each sold insurance policy. SE receives their respective commission on a pro-rata basis. Meaning, if they give 4 equal monthly payment scheme to their clients, they will also receive their commission -- 4 times.
Example: SE Commission for one sold policy is: 100.00. (Granted SE gives 4-month-term, SE will be receiving 25.00 monthly, upon cleared payment)
On my 2 tables lies the following fields: [Table1] SECom1 SECom2 SECom3 SECom4
My "MainMenuForm" contains a button that imports a specific csv on a specific location.Someone should do this import once per day. The problem is that there is no way for me, once the import has been done, to know which records are "new".There is no unique field that distinguishes the new records from the old ones.
I was thinking of adding like a Date() field to that csv (= the date where it was imported). Can this be done automatically? So if I import a file today, the final column would be 05/02/2015If i import a new file tomorrow, it would be 06/02/2015.
