Tables :: Populate Value Of Field From Value Entered In Another Field?
Oct 25, 2012
automatically populate the value of a field [Industry] in a table after a user has entered the value in another field [IndustryCode] in the same table. The [IndustryCode] and "Industry" are stored in a separated table [2012IndustryCodeTable]. I have tried using DLookup and cannot get it to work.
How can i get the [Industry] to populate the information from the [2012IndustryCodeTable] once a user enters the code into the [IndustryCode] field in a table?
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 have a form with a text box that the user enters their name into. When they click submit a menu form is opened. I want the name that was entered to be carried over to a field on the menu form. This will welcome the user, eg: WELCOME John Smith.
Im thinking that i should use the INSERT INTO function, but im not sure of how to go about it.
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
--- OR ---
Can it notify the user that they need to change the Option Group?
I have a form with a text box where the user enters a date and then clicks a button titled "Add." I've added an on-click event to the Add button that runs an append query that adds several records to a table [tblTracking]. I have a field in tblTracking called EndDate. I want the date that's entered into the text box by the user to be populated into all the new records added to the tblTracking when the append query is run. Currently, all fields in tblTracking are populated when the append query is run, except the EndDate field.
Is this possible? If so, how?
I've experimented with adding a separate on-click event that adds a record to a separate table containing only the date entered in the textbox and an auto-populated ID field. I thought there might be a way to utilize the ID field to pull the associated EndDate into the Tracking table, but I can't figure that out, either, since I don't know how to tell it to look at the date field in the last record of the table. That sounds unsafe anyway.
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
I have a calculated field that is a total based on 5 other fields. These fields are rankings of priority on individual categories, while the calculated field is a total priority ranking for the entire record. (A ticket) The 5 categories are prioritized, and the calculated field runs it through our equation to determine the overall priority of each ticket.
Now, some tickets don't have individual priority numbers in their categories. Therefore, no total priority number is calculated in the calculated field. We would like these tickets to be given a manual total priority, but we can't manually enter numbers into the calculated field to do this.
Is there a way of saying this to the calculated field... "Use the equation to run the individual categories to come up with your total. IF there are no numbers in those categories, reference the field 'Manual Priority' to find your number."
This way, we'd like to keep all of the total priorities in the calculated field. It's just that some of them have to be manually entered because there will be no individual priority categories to calculate the total by.
The current formula for this total priority field is...
(([Size of Financial Risk?]*20)+([Impact on Internal Customers]*15)+([Non-Financial Impact]*20)+([Impact on External Customers]*30)+([Estimated # of Clients Impacted]*15))
I'd like to say, "Do that... or use this manual field if that equation turns out to be nothing."
I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)
Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.
I have a small database to keep track of some student information. Right now I have one table where a student is first entered with their contact information and then other tables that house specific information about other areas related to the student (if there's no longer with the school, paperwork is complete, etc.). The primary key for all of it is their student ID. The tables are linked parent/child with forms, etc., so that when I open a form having to deal with one of the related tables (student active status), and pull up the student's ID from the first table, it'll then populate that ID into the status table, even if something else isn't added.
What I need is really that when I create a new student on the first table with their contact information, that their ID is populated into all of the related tables, without waiting for additional information to be added. This is because I need to run reports to see who is missing information, etc., and without their ID showing up in that second table, I'm not getting accurate results. How would I set this up?
Sample of tables with smaller number of fields:
Table 1 - Contact Info (always done first for a new student) Student ID Name Address Email
Table 2 - Student Inactive Status (want the ID numbers to be the same quantity as table 1, even if Student ID is then the only completed field in this table) Student ID Inactive status type Date
Table 3 - Paperwork (same issue as table 2) Student ID Submission Date Approved By
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
Both tables are joined with 1 to 1 relations based on apm_id as both of the fields are same. The table [APM] is pre filled with data. The idea is to list data from [APM] and collect data from user to be saved in [DATA] based on the info from [APM].
I created a form which list all the fields from both tables. The fields from table [APM] are only for displaying data and created a combo box based on [APM].apm_id to display the fields by chosing an APM ID from the drop down box.
A user adds a record and moves on to second record, completes the second record. Now if the user wants to list the first record by chosing the APM ID from the drop down box it only populates the fields from table [APM] but not the fields from table [DATA] which the user just added. Infect the fields related to table [APM] changes, but the fields from table [DATA] stays the same.
How ever if I click on the small arrows on the bottom which lets you cycle through records than it populates all the fields from both tables correctly.
How can I fix the issue so that all the fields would populate based on APM ID selected from drop down box.
First hurdle in my database tables, I have a field called Date_Created which I want populated just once, whether from a form or an import.
I have another field called Date_Updated which I want to update any time the record is changed. I know I can do this by using the default value of Now(), but how can I get the Date_Created field just to update when the record is created and not any time after ?
I also want to show these values on the form, but have them as view only, hoping that is possible ?
How does one cause certain data to appear in another field based upon the data entered in another field.
For example: I enter in the field the word "Carrot" in a field called food and the word "Orange" appears in the field color. And then if I enter the word "Beans" in the field food then the word "Green" appears in the field color.
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
I would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.
The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.
SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.
I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1 FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I am wanting to populate a field by entering a date in another field. I am trying to determine age(years, months, and weeks) of something by entering a date in another field. Is that possible in Access?
I have a form with a main form and two subforms. The first subform automatically populates it's linked field with the data in the key field of the main form, but the second subform does not auto populate it's linked field with the data from the key field of the first subform. I have created main forms and subforms before and this was not a problem. Is there a way to cause the second subform to automatically populate it's linked field with data from the key field of the first subform?
My Edit info form has several fields. The two of interest are: Filestatus - text - open or closed Dateclosed - date format When the file is created, the filestatus field is set to "Open". What I would like to do is when a date is entered into the dateclosed field, automatically change the filestatus to "Closed".
I have created a text box for people to enter search criteria, e.g. customer surname. The results appear in a list box underneath. When I select the record it should open up the customer's details but in Access 2013 it produces an error "The value you entered isn't valid for this field". The first sub-routine pulls the search data into a Row Source:
Private Sub txtCustomerSearch_Change() On Error GoTo Err_txtCustomerSearch_Change Dim strSource As String strSource = "SELECT DistinctRow CustomerNo, [End Date], Surname, FirstName, Address1,
