MS ACCESS :: Autofill Fields Based On Results From Another Field In The Table?

Autofill Query Field Based Upon Other Fields Within The Same Query?
Hi,

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...

cheers

View Replies !

Autofill Field Based On Previous Value
I am trying to autofill a field based on the value of a previous field on a filtered form. I think the fact the records are filtered is throwing me off. Any help for me?

View Replies !

Question About Autofill Fields From One Individual Field
I have a form with the following Fields: Customer Name, Address, City, State, Postal Code, and Phone Number. I also have a field called License Number. This question is that once the data is entered the first time, is there an easy way so that if an individual that has been entered into the database comes back you can enter there License Number only and the other fields will fill in automatically. If so How. I am assuming so kind of query but i need help. Please Help..thanks

View Replies !

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 !

Locking A Table Based On Query Results
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!

View Replies !

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)

DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15

IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static

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
-Tim

View Replies !

Autofill Based On Selection - Please Help
I am learning Access fairly quick, but still a newbie and stuck on an autofill problem. I have read everything I could find and it seems there may be code involved, which I'm not sure about.

The problem seems simple enough: I want to populate field "Rate" in the table "Services" based on the selection from a dropdown menu referencing ClientID in the Clients table. Specifically, my form uses a drop down menu to select ClientID for the Client table to associate with fields in the Project table and I have imbedded another form at the bottom which contains fields from the Services table including the Rate field I want autofilled based on the ClientID selection. Here are the tables and fields I'm working with...

...table...
Client
...fields...
ClientID (PK)
Rate
<and more>

...table...
Project
...fields...
ProjectID (PK)
ClientID
<and more>

...table...
Services
...fields...
ServicesID (PK)
ProjectID
Rate
<and more>

Please be easy on me if this is a simple problem. Thanks!!!

View Replies !

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?

Thanks.

Gary:confused:

View Replies !

Link And Autofill A Name Based On NumberID
I have a main form for a tblCommittee and a subForm 'sfCommitteeOfficer' that will eventually take an entry or show -if its existing, a list of Committee officials. The officials come from a 'tblMembers'. There are too many members to use a drop down list box to select the MembNumber. What I wanted to do was be able to enter the MemberNumber in a txtBox 'MembID' on the form in the row containing Memb#, MembName, Position i.e chairman, Date elected Date retired, and have the members name automaticlly entered into the field MembName of the subform to limit the amount of entering the user has to do!
Is this possible? I have a tblCommiteeType with key field CommitteeID, and foreign Key MembId, so I dont think I need a tblCommitteeMember because names are already stored in tblMembers.

View Replies !

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 !

Autofill Text Boxes Based On Combo Box
:confused: I have a combo box on a form that is used for data entry that allows the user to pick the date. This is based on a table that includes date, week ending date and month, all very specific for our business. I used a function that says Me.txtBox=ComboBox.Column1 for example. So my combo box would include all the information, and all but the actual date is not visible. I want this to feed into another table that will be used for reporting, and that table has the same fields of date, week ending date and month. I keep getting an error saying that the value selected from the combo box doesn't fit the criteria, and I believe what is going on is that it is taking all the fields instead of just the date. Any suggestions how I can still have the text boxes update if I only have the date in the combo box?:confused:

View Replies !

Auto Data In One Field Based On Other Fields?
Hi,

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.

Thanks

View Replies !

Automatic Field Entry Based On Several Fields
Hi

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 !

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 !

Autofill Fields
Hello everybody!
Im new in Access as well as in this Forum! Glad to be here :)
Im currently developping a small application in Access 2003 and here is my problem:
I am trying to autofill the value of the field OrderID in this form : "FRM/SupplierID/CurrentYear/OrderNumber"
Where the SupplierID is charged once the SupplierName is selected from a combobox and the OrderNumber entered from another textbox.
Note: SupplierName values are from other table: Suppliers

Any help is highly appreciated,

Thanks in Advance

View Replies !

Autofill Fields
Hi

Need some help with a table, or possible more to do with a form design..

I'm looking to autofill in a field depending on data that's in the previous field.

E.G. I have too fields.. One for employee and one for department.. I want the department field to automatically select the department whenever the employee is selected from the drop down list.

It's probably a simple query or something I need to do..

can anyone help..

Thanks
Saifon

View Replies !

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?

---
Example:

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 !

Autofill Fields In Form
Hi,

I have a form that has a field called scope of work which is a combo box that you have 3 options to select from.

I have set up a table called scope of work which has other fields
ie: Scope of work, Est Man Hours, Normal Rate, Overtime Rate and Est Due Date.

The combo box works fine selecting the scope of work but I want the fields on the form to automatically fill in the other information. Say they select option 1 in the scope of work based on this option it will fill in 40hrs at $150 per hour normal rate and $180 per hour overtime rate and this will take 4 weeks to complete. Based on the start date field it will also calculate 4 weeks from now for the completion date.

I have tried everything from queries using SQL statements, joining tables one to many etc.. and for some reason I cannot seem to get it to work for me and I've spent hours searching around for a solution..

If anyone has any ideas it would be greatly greatly greatly appreciated!!

Thanks a Bunch

View Replies !

Autofill Other Fields In A Form Or Report.
Please help. This might be a simple way to do this, but I'm not really good at coding so here it goes...

I want to make a form or report when a person enter a vendor (example) the other fields will atomatically fill-in depending on the data that cooresponds to that vendor. Can you show me how to do this? Example below:

Vendor------> (Autofill) address------>(autofill) contract rate-------->(autofill) contract rate---> etc.

View Replies !

Create A Table Based On 2 Tables With Different Fields
Hello,

I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks

Richard

View Replies !

Adding Value In Table Based On Values In Two Other Fields
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

View Replies !

Enable/Disable Fields Based On Table
Hi everyone,

Need some help here! I have a table where different types of records are recorded. Let say for argument's sake my tblMain looks like this:

RecID (autonumber)
EntryType (Combo box linked to my tblEntryType)
Field1 (text)
Field2 (text)

I then have tblEntryType which lists the different types of entries to be made and which fields are required on my form and what the lables should be. The structure looks more or less like this:

EntryID (autonumber)
EntryName (text)
Field1Req (true/false)
Field1Tag (text)

On my form, my record source is tblMain. When my user makes a selection in the combobox EntryType, there is a lot of code behind the AfterUpdate function like this:

If EntryType.Column(2) = True Then

Me.field1.Enabled = True
Me.lblfield1.Caption = Me.entrytype.Column(3)
Else
Me.field1.Enabled = False

End If

I find this setup a bit labour intensive, especially as my form has quite a few fields! Is there a way that I can "look up" which fields are required from the table and enable/disable and label based on my tblEntryTypes without me having to creating so much code/use the "column" function.

Let me know if I am not making myself clear and I will be glad to try explain so more!

Tx in advance,

Mel

View Replies !

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:

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 Replies !

Seeking Advice! Populating Fields Based On Other Entries In Table
Last week some very altruistic folks here helped me solve a many-to-many relationships quandry, and I am hoping that there may some others out there who can help with another question.

I have a feeling this is simple, but I can't quite get my head around it, and have not been able to find it in the past forum threads, but....

I want to create a tblJobs that has information about our clients' Companies, as well as Contact. As there can be many Contacts for each Company, my current setup looks like this:

tblContacts
ContactID (Pkey)
Name
Lookup_to_qryCompanies_to_Contact(shows Contact's company)

tblComanies
CompanyID (Pkey)
Company Name
(Client info is included as embedded table based on qryCompanies_to_Contact)

qryCompanies_to_Contact
Company (from tblCompanies)
Contact (from tblContacts) (one-to-many)

In my new tblJobs, I would like to have a combo box in which I can input the company name, and then the next field will have another combo containing ONLY names of the contacts affiliated with the chosen company. (I will also eventually like to make a form that allows the same function, but am assuming that the process will be the same for updating the table trhough a form as it would be updating it directly).

Thank you for any advice!!

Blair Sly

View Replies !

Update Table Field Based On Another Field.
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 Replies !

Automatically Set A Field Based On A Field In Another Table
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 !

Autofill Of 1 Field
I have 2 forms (school and venue)

I have a button on the school form that opens the venue form. I want the school field that is on the venue form to auto fill with the same information that is in the school field on the school form. Any help would be awesome.

Martha

View Replies !

Autopopulate A Field In Table Based On Another Table
Hello everyone. How do I go about automatically inserting a date from one table into another table. This are my tables:

tblPAF (Personnel Action Form)
PAFNo
EmployeeNo
Date
Department
Division
Salary
Comments

tblBenefits
BenefitsNo
EmployeeNo
Date
BenefitType
CoverageDate

Every time an employee’s salary or benefits changes we fill-out a PAF (paper format) on it we write all the changes. In my PAF db form I’m thinking adding a command button to open my Benefits Form. Once my Benefits form is open I don’t want to renter the date that was entered in the PAF form I wanted to automatically insert that same date. Hopefully I made myself clear.

Thank you for your assistance.

HR

View Replies !