Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Field Value Based On Other Fields

Here is what I am trying to do.

I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)

How would I code for the value in the carrier field to populate based on the state and service level values entered.

Should I write a query to get the results? Or should it be code in an event on a particular field?

Whichever way works, any ideas on how to get started?



View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Update Field Based On Other Other Fields
drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.

if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"

Right now I'm getting a circular reference error :/

please advise. Thank you.

View Replies !   View Related
Auto Data In One Field Based On Other Fields?

I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table.

ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes

I'm open for any ideas, I'm new to access and am not sure how to get this started.


View Replies !   View Related
Automatic Field Entry Based On Several Fields

I am an amature with databases and a bit rusty too. Can anyone help me with this problem?

basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables

e.g. for audio visual eqipment

The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc

The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.

( i would also have a customer table and an asset repair table)

In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number.
(Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed).
(Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)

The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.

When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.

View Replies !   View Related
Auto-filling Fields Based On Another Field
hi all...

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.

thanks a bunch.

View Replies !   View Related
I'm Sure This Is Very Easy: Update Multiple Fields Based On One Field...
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...

What is the code to have multiple fields updaterd based on what is input into a field?


A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)

I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.

Thanks a million!!!

View Replies !   View Related
Autofill Fields Based On Results From Another Field In The Table?
I have an Access database with several tables and a multitude of subforms which are displayed on a single master form. The subforms are used to facilitate data entry. In several of the tables there are fields which are related and I would like to have some of these fields updated based on the results entered in the related field (i.e. the answer for one field depends on the other).
Field 1Field 2

Field 1 is a simply a description of the basic dataset. This field is already set-up on form as a combo box that allows the user to choose one of three options. Field 2 is a code number used by another piece of software to identify a particular symbol. It is a new field being added to the database. There are 200 codes that identify a wide range of symbols for different types of data and I donít want to have to look them up when I, or my assistants, are doing data entry.

My question is this; is there anyway to have the DISC_CODE value, Field 2, automatically entered in the table when the value for Field 1 is selected in the combo box on the form?

View Replies !   View Related
How Can I Configure A Table To Auto Data Entry Into Fields Based On A Previous Field
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.

I am trying to create another table that I can capture daily Out Of Stock data for products.

What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.

How can this be done?

View Replies !   View Related
Autofill Query Field Based Upon Other Fields Within The Same Query?

Baisically I got this query which controls the selection of Software within a system. i.e. It contains the fields Vender_ID, Application_ID, ApplicationEdition_ID and ApplicationVersion_ID. These auto-filter based upon the users selection (e.g. Vender=Adobe so Application=Acrobat/Photoshop/etc...)

Right some from the user software/application selection i want it so that it will automatically assign a License_ID defined within a table tblLicense_Information.
Baisically im asking whether i can put this in the 'criteria' box:

"SELECT tblSoftwareOverview.License_ID FROM tblSoftwareOverview WHERE ((tblSoftwareOverview.Vender_ID=qryHardwareSoftwar e.Vender_ID) AND (tblSoftwareOverview.Application_ID=qryHardwareSof tware.Application_ID) AND
(tblSoftwareOverview.ApplicationEdition_ID=qryHard wareSoftware.ApplicationEdition_ID)
AND(tblSoftwareOverview.ApplicationVersion_ID=qryH ardwareSoftware.ApplicationVersion_ID));"

I know the sql is not exactly right but you get the idea of what i want to achieve! But im guessing you cant really have an sql statement within an sql statement so any other suggestions on how i can do this would be much appreciated as the INSERT INTO command didnt like inserting into a query and when inserting straight into the table, the query failed...


View Replies !   View Related
Beginners Question - Auto Update Fields Based On Fields In Another Table
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.

I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.

The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".

Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)

XserveFile Server107203.30.144.75

AddressIP TypeDevice

What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".

I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.

thank you

View Replies !   View Related
Auto Rearrange Fields Or Hide Fields In DataSheet Based On Criteria
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.

This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.

What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.

If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)

Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then

Me.CASETIF.Visible = True
Me.CASETIF.Visible = False
End If
End Sub

And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form

Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap

Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria


View Replies !   View Related
Adding Fields Based On Values In Other Fields

I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.

When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.

View Replies !   View Related
Make Field Auto Populate Based On Value Of Field In Another 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:

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 Replies !   View Related
Automatically Populate A Date Field Based On Value Entered In Another Field
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 Replies !   View Related
Calculation Of Field Based On A Field In Access 2000
KTYPE=text; EXPIRATION_DATE=date/time; OVERAGE=date/time

im doing a report based on a query (which has a few fields, including KTYPE, EXPIRATION_DATE, OVERAGE). im trying to determine the OVERAGE based on the following conditions:

here are the conditions:

if KTYPE="PO", then OVERAGE:=(EXPIRATION_DATE + 6 months)
ie.. KTYPE=PO, OVERAGE:=(01/04/2004 + 6 months) = 07/04/2004

if KTYPE="IAA", then OVERAGE:=(EXPIRATION_DATE + 20 months)

if KTYPE="FA", then OVERAGE:=(EXPIRATION_DATE + 12 months)

and so on and so forth.

I'm very new to Access. What formulas do I use and do I put the formula in the query or the report.

Please help. thanks in advance

View Replies !   View Related
Code To Disable A Field Based On The Values Of Another Field
If I have the following Code to disable a field based on the value of another field:

Private Sub lstAgreementType_AfterUpdate()

If Me.lstAgreementType.Value = "BN" Then
Me.txtSenateAandCDate.Enabled = False
Me.txtSenateAandCDate.Enabled = True
End If

End Sub

But I also need it to disable the field is equal to "BA" or "BT" as well, how would I add that to my code?

View Replies !   View Related
Using SetValue To Change Field Property Based On Another Field Value
I would like to change the property of a text box to Visible and Required when a user chooses a specific value in a drop down box of a different text box. Is this possible using a macro or some other way?

Thank you,

View Replies !   View Related
Due Dates Based Upon 2 Fields
I'm creating a db that provides project listings in the work queue, along with the due dates and a bunch of other data.

I have created a table [recurring projects] that stores the projects that are done frequently. Once I do some field manipulations on this table, I append them to a master table with all projects [projects] - ad hocs, non-recurring, etc.

Most, not all, recurring projects are due 14 days from the date of assignment, however it varies. I've created a query that populates the due date 14 days or whatever the user has inputed for working days from the assignment date.

All works good with this functionality.

Here's my problem:

There are some projects that are due on the 1st, 15th or some designated date each month. Typically the projects are assigned a due date prior to the month due (i.e. February projects are assigned in January). Since the due dates change each month, is there a way to code a query to look for the first process - 14 days - and if it is null, then populate with a day of the month due.

For example, the field [days_to_complete] = 14 so the query will populate 14 days from 1/31/07 resulting in [due_date] = 2/14/07 OR [days_to_complete] is null, but [day_of_month] = 1 , which I need to create the [due_date] = 2/1/07

I'm racking my brain and pencils are being cracked!


View Replies !   View Related
Lookup Value Based On 2 Fields
I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.

I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".

Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.

So the TblLevels looks like this:

100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3

So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"

any ideas on how i can do this one?

View Replies !   View Related
Activating Fields Based On Another

Dopes anybody know how to activate/deactivate fields for data entry on a form based on another field.

I have a check box whcih when checked I would like to activate two other fields and when unchecked to grey them out.

Can I do this?

View Replies !   View Related
Conditional Format Based On 2 Fields
Hello All,

I have searched the forum but I don't quite get what I am looking for.
I have a subform that has these 2 fields:
- Status
- LastFollowup

What I want is if the Status shows "Pending" and the date entered on the LastFollowup is more than 7 days. Then it should turn Yellow.

I tried this code in the expression box but it doesn't seem to work

[Status]="Pending" and [LastFollowup]>7

Could anyone please assist? I know this is simple but it's not working for me.

Thank you

View Replies !   View Related
List Options Based On Other Fields

I've been using this forum for a couple of weeks but this is my first post, so apologies if I've put it in the wrong place!! I have a table with some drop-down lists to fill certain fields, and I would the available list options to change based on information in other fields. To provide an example:

Field One: The drop down list lets the user choose "Alphabet" or "Number"

Field Two: The drop down list lets the user choose A-Z if "Alphabet" is selected in Field One or 1-100 if "Number" is selected in Field One

Hope I've been clear enough. Any help is greatly appreciated!!



View Replies !   View Related
Query Two Fields Based On One Criteria
Hi all,

I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.

I have an underlying query that is populated by a combo box on an unbound form.

This has worked when generating reports for individuals but I cannot get the required result when using the form.

Thanking anyone in advance who can assist me with this.



View Replies !   View Related
Return Info Based On Other Fields
Hi All

I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.

I have the following in a field called PROBLEM STORE NAME:

I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:

I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:

Any suggestions would be greatly appreciated



View Replies !   View Related

Copyright © 2005-08, All rights reserved