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?
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.
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.
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.
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.
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!!!
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?
On a data entry form, is there a way to deny data entry into fields until one particular field is entered into first?
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?
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
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...
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)
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.
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
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
A form has ten fields including Field A and Field B.
Field A is a combo box with 2 values, “Event” and “Non-Event”. Field B is a combo box also.
On each row, I want Field B to be populated only when Field A has a value of “Event” for that row only. How can I do this in Access?
Thanks a lot for your help.
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.
This is probably an easy one:
I have a table which includes about 20 yes/no fields. I want to show, either as a field in the table, or as the result in a query, a field which would be "Pass" if all the 20 fields are "Yes", and "Fail" if at least one is "No".
I can think of convoluted ways of doing this, but is there anything simple that I might be missing?
Hi, I'm very new at creating forms. I need a control to lookup a record in a table. I want my form to be able to lookup a Vendor ID number from a table based on what I select from a drop-down list of Vendor Names. I can't figure out how to use the Dlookup function, and that may not be the correct method anyway. I did create a combo box which includes my Vendor names, so the next control on my form I want to automatically populate with the Vendor ID number from the vendor table. Is this even possible?
Any help is very much appreciated. I'm just not very good at this yet!
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:
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 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.
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.
I am trying to set validation to say that the currancy entry in one field must be less than or equal to the entry in another field.
On the General tab in Table Design View on the Order table I have put a validation of <[price], I have also tried adding in the current field so [amount_to_pay]<[price]
I keep getting the following error message though.
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK contraint. Can anyone help?
I have 2 fields one yes/no for Deletion and one that says deletion date. Is there anyway that I can make it so that if a user marks yes for deletion that they must enter a deletion date.
I know this will be simple for a lot of you out there...that's what I'm hoping for...
I want to look up an ItemPrice based on the ItemName chosen from a combo box, but I also don't want an error to pop up if no ItemName is chosen. This is the code I have written:
Private Sub ItemName_Exit(Cancel as Integer)
If IsNull(Me.ItemName) Then
MyCriteria = "[ItemName] = ' " & ItemName & " ' "
ItemPrice = DLookup("[ItemPrice]", "JTemplate", MyCriteria)
Could someone lead me in the right direction? It does look up the price perfectly, but I keep getting run-time error 2424 if I move to the next line on my continuous form and not enter anything in. BTW, I want to move to the next line so my total at the bottom of the form can finish calculating.
I have a form with a field called "Change Type". The options are "D", "N" or "T". If a "D" is entered for change type, I would like the field "Change Description" to autofill "dropped". Please give me and example of the code to use and where to place it. I can have it display in the field but it is not saved. Thank-you!
I am trying to show/hide a field based on the value of another field on my form. I have a "Catagory" field that is a drop down with "desktop, notbook, printer and monitor" and I have a "computer name" field. When the "Catagory" field is either "notebook" or "desktop" I want to show the "computer name" field and otherwise hide it. Any suggestions?
I have a report that is based on a query. I want to suppress the contents of field "Desc" if field "Type" is equal to "PM" or "PMI" (this is in the report). Does anyone know the best way to do this?
I am a new member. I hope I am posting this in the right place on this site. I have a table that contains 3 fields that are dependent upon themselves. I am populating one field with an English translation of a color, such as black/white/et al. I have 2 other fields that contain a Spanish translation and another with a French translation. I would like to populate the value for the French/Spanish fields automatically based on the selection of English color. What is the best way to do this?
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
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
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?
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?
I'm sure the answer is already here somewhere, but I can't turn it up searching...
I'm trying to create database to automate creating and storing production batch sheets.
I have 2 tables where I'm trying to pull data from one into the other. I'm sure some kind of query is needed, but what I'm doing isn't working.
The first table is tblProcessDescription. It contains fields for:
Process (a code for the type of process being performed)
ProcessType (one of several types for standardization and later use)
RawMaterial (the material needed to perform this process)
ProcessDescription (the detailed procedure to be performed which will be printed on the batch sheet)
The second table is tblBatchMaster, and is for storing the master batch sheets which contains the only the steps needed to produce a certain product and the order to do them in. It contains fields for:
Product (the name of the product)
Step (a number from 1 to xx designating the process' position in the manufacturing process
Process (selecting from a ComboBox from tblProcessDescription)
RawMaterial (this should pull from tblProcessDescription based on the Process selected in the previous field)
RawMaterialQty (the quantity of material to use, which varies from product to product)
So, when I select a Process using the ComboBox in tblBatchMaster, how do I make it fill in the Raw Material field? It seems like it should be a TextBox, but then I can't find any way to make it populate the box. When I use a query to populate a ComboBox, I get all the raw materials in tblProcessDescription, not the single raw material that matches the process.
Please help. Let me know if this description isn't clear either.
I am feeling so brain dead. To make it simple...I have a form with a field called Beg Date (Beginning Date)...once the user inputs the beg date I want a field End Date (Ending Date) to automattically poplulate with a date 3 years from the beg date. I know the function DateAdd("yyyy",3,Beg Date) but I just CANT get the syntax right or don't know the code to write to make this happen.
The form is based on a table that I want all the data stored in when the form is completed.
I have a report with two fields: Text fields which includes names of people, and another field which includes numbers.
We like the report which prints out all the names and their corresponding number.
However, we would like to include some totals at the bottom of the report:
Brad - total number
Charles - total number
Fred - total number
What is the syntex to do this?
In the numbers field i tried =sum(IIF([name]="Brad",0))
but nothing works??? Is there a better way? I want it to add the numbers field based on the results of the text field only.
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!
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
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?