Automatically Set A Field Based On A Field In Another Table

Aug 12, 2005

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?

Thanks,

View Replies


ADVERTISEMENT

Automatically Populating A Table Field Based On LOOKUP Value?

Nov 15, 2013

I have two tables, "Membership" and "Class." The Class table has a class ID, class name, and cost of the class.

In the Membership table, I have a field to accept a class ID that is entered by a user. I also have a column called Tuition. When the user enters a classID in the Membership table, I would like the dollar amount associated with that classID to automatically appear in the field called Tuition in the Membership table.

I tried the LOOKUP data type and calculated fields, but no luck. I know that DLOOKUP can be used on forms, but I don't see how it can be used directly with tables.

Is this even possible?

View 1 Replies View Related

General :: Automatically Update Field Based On Entry In Another Table

Jan 14, 2013

I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.

When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.

View 1 Replies View Related

Automatically Populate A Date Field Based On Value Entered In Another Field

Nov 10, 2005

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.

Any help is greatly appreciated.

View 2 Replies View Related

Tables :: Automatically Fill Field Based On Keywords In Another Field

Dec 3, 2013

I'm trying to complete a database.

It is to manage details of pupils with additional support needs, and plan for the extra arrangements the school will provide for assessments.

It has 2 Tables

tbl-PupilDetails
-ScottishCandidateNumber primary key (Unique number which identifies pupils to the exams board)
-Forename
-Surname
-DOB
-YearGroup
-Class
-NatureOfNeed (memo)
-EvidenceOfNeed (memo)

tbl-SubjectLevelArrangement
-ID Primary key, Autonumber
-Pupil foreign key to tblPupilDetails
-Subject
-Faculty
-Level
-Arrangement

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.

View 6 Replies View Related

Reports :: Get Remarks Automatically In Field Based On Other Field In Report

Dec 15, 2013

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

View 3 Replies View Related

Forms :: Automatically Fill Field Based On User ID

Jun 30, 2015

I have a table called tbl_post_staff, within this table I have an id number (autonumber), user id (text) and user full name(text). Then I have a form called ec_main with a combobox linked to tbl_post_staff, but stores its value in a field called officer_is in a table called tbl_extensions. also on the ec_main form is a text box that displays current user.

What I am trying to do is; on my form ec_main; default the combobox (Called officeris) to the staff full name based on there userid that is logged in. how can I do this?

View 12 Replies View Related

General :: Having Field Automatically Populate Based On Selection?

Aug 5, 2013

I have a training log that has 4 tables, the employee table, the training course table, and the department table.

These tables all contain the names of employees, training courses, and department in one field and their respective ID codes in the other.

The 4th table is the actual log where the manager logs in who took the training courses. When the manager goes to select the employee name, course name, and department name is there a way not using VBA for the respective ID number to appear in the 4th table (they use the same field names and are related)

View 4 Replies View Related

Field To Automatically Fill With Correct Value Based On Information In Multiple Fields

Apr 16, 2012

I have a database in which we are trying to have a field "Headcount" automatically fill with the correct value based on information in multiple fields. The "Headcount" field will be based on about 6 or 7 rules. Most of the rules i can figure out, but im having trouble figuring out what the best way would be to search for a single individual's projects when they are on more than one project at a time. I thought about using a counter of sorts to count how many projects someone was one, but how do I pick them out of the entire table once i have found out the number of projects? Would a for loop work best or just many if statements?

View 5 Replies View Related

Tables :: Update A Field In One Table Based On Status Of A Field In Another Table

Aug 2, 2013

I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.

I want to prepare a report from the projects table that lists projects IF they have any open tasks.

In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'

View 7 Replies View Related

Update OnHandQty Table Field Based On Qty Field From Another Table / Form

Oct 30, 2012

I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.

View 5 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

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.

View 8 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

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?

View 1 Replies View Related

Modules & VBA :: Updating A Field In A Form Based On Another Table Field

Sep 13, 2013

Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.

What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).

View 1 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Tables :: Autopopulation Of Field From Another Table Based On Another Field Criteria

Dec 5, 2012

I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.

This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.

If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If

View 3 Replies View Related

General :: Autopopulation Of Field In Table Based On Entry In Second Field

Jul 22, 2014

Please see attachments.

POST.zip (384.0 KB)
database.zip (58.8 KB)

View 4 Replies View Related

Update Table Field Based On Another Field.

Mar 3, 2008

Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....

I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52

View 7 Replies View Related

How To Automatically Update A Table Field From A Form?

Feb 19, 2006

hi there
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.

What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.

It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!

Any tips much appreciated - Thanks
Mike

PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)

View 3 Replies View Related

Would Like Field To Automatically Pull Data From Other Table

Apr 28, 2006

Hello Everyone,

I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out.
Thanks so much for any assistance!
Amy (monet1369);)

View 6 Replies View Related

Forms :: How To Create A Field In Table Automatically

Jun 29, 2015

I have a form and it has a field as question. I also have a field as I'd in this form from the same table that is autonumber. I want that for each field the question will be created automatically in the table as below:

Is epm ( my Id field) is created
Is epm and is created are string

They should also include Id number of each row. So It would be like:

Is epm -1 implemented
Is epm-2 implemented
.
.
For each row

View 7 Replies View Related

Define A Field So That It Is Automatically Filled In With Value From Another Table?

Jan 28, 2015

I would like to define a field (mailingaddresscounty) as type lookup using a field (county) from another table (Zip). But instead of the user selecting from a long list, I would like the selection to be automatically made based on the value of another field (mailingaddresszipcode).The two tables are Organizations and Zips. The user enters the 9 digit zipcode in the organization table (mailingaddresszipcode). I would like the county field in the organization table (mailingaddresscounty) to draw from (link to?) the corresponding 5 digit zipcode in the Zip table (zip) and return the correct county for that zipcode.

View 1 Replies View Related

Problem - Automatically Fill A Table Field From Another Table

Mar 14, 2008

I have a table which contains these fields

MAIN TABLE
Job_No - Looks up Job table
Source - I want this to be automatically filled from the record associated with the job number from the Job table
Test_Result - Looks up Result table PASS/FAIL etc
Technican - Looks up technican names table

JOB/SOURCE TABLE
Job_No 0001 0002 0003 0004 etc
Source Cust1 Cust2 Cust3 Cust1 etc

Note also that we also have different job numbers for the same customer

I know I can fill in a value on a form from a combo box looking up another table but dont know how to do this in the main table

Any ideas?

Paul

View 7 Replies View Related

Automatically Copy Data From One Field In A Table To Other Related Fields In Other Tables

Aug 7, 2014

I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.

View 2 Replies View Related

How To Make A Field Automatically Receive Same Value From Another Field

Apr 22, 2014

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!

View 5 Replies View Related

Copying A Field Automatically From Another Field

Apr 14, 2005

Hi

Forgive me - but i am new to db.

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.

thanks in advance.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved