How to auto populate field based on other fields Results

I have a field on a form that I would like to auto populate with the (concatenated) contents of 3 other fields on the same form. The first 2 fields are drop down boxes and the 3rd is a hand keyed text field.

For example, the first field is Department (4 digit number from drop down box)
Second field is Document Type (3 letter code from drop down box)
Third Field is Document Seq Number (5 digit number hand keyed)

So if I pick 0100 from field1, PCS from field2, and hand key 10000, in my 4th field (called Document ID), I should wind up with 0100PCS10000.

Currently, I have the control source for the Document ID field as
=([Department] & [Document Type] & [Document Seq Number])

In the example above, instead of getting 0100PCS10000, my result is 1110000.

Any help in getting me to the result I am looking for would be greatly appreciated.

how to autopopulate other fields in my form based on the value of other fields..

1. I have 10 combobox field in my forms.. I need to autopopulate all other fields based on the selection i made on the first field.

2. there are times that i need to change the value in other fields..in my example below i need to change field # 6 & field# 9..

Field----Value
1 ------X123 == (First Field == Selection ==> autopopulate other Fields)
2 ------X123
3 ------X123
4 ------X123
5 ------X123
6 ------O554 ====> (from X123 - change to O554)
7 ------X123
8 ------X123
9 ------M887 ====> (from X123 - Change to M887)
10 -----X123

Im new to MS Access and don't know how to do this. Thanks

Hello everyone, I'm what you might call a new comer to Access but I find it quite interesting and am trying to learning a much as possible about it.
I am creating a form and what I would like to do is use the first letter of the persons' first, middle and last name as my primary key.
Is there a way in which access can take these letters from a field to populate my primary Key field. Example: the form would have a field titled 'initials', once filled in access would take these letters and automatically populate the next field while adding a unique number to distinguish it from other similar initials. Example: Sam John Williams will be entered in as SJW in the "Initial field". This would be use to auto populated another field becoming SJW01. And Samuel James Wilson will be entered in the "Initial Field" as SJW. But when auto populated in another field should be as SJW02. I have seen this in some access programs but don't know how it's done. Can anyone help assist me?

I'm having a couple different issues. A little background on the db: I am IT/Dispatch Manager a a small AC company and I have made a db to streamline their dispatch system, particularly the part where I need to debrief with the tech and gather information. I wanted to have a button on my form that pulls up a sub-form where I could select any of the 26 parts available a technician could recommend. So the sub-form has two rows of 13 Yes/No boxes, each Yes/No box has 2 text fields next to it representing the regular price(field is called RPrice) for the part and the price with a service agreement(field called SAPrice).

When you click yes on the box I want it to auto populate with the default values for RPrice and SAprice, but if necessary the prices should be able to be changed if the tech quoted somthing other than the default values. I have gotten this far just fine.

My problem is that I want to get the total (both RPrice and SAPrice of all of the parts selected and I want that to transfer over to a field on the main form(FrmDispatch.RecPartsTotal). I also want to be able to recall what parts were selected and what price the tech left. I am having difficulty just getting these prices to add up on the FrmParts sub-form. It seems it just wants to list the numbers one after another like I am adding text to a sentence. How am I supposed to be abled to get the SUM of these fields?

I also wonder how my tables should be set up for this. Should I be keeping a second table that links with the primary key(DispatchID) that has a field for all 26 parts and a field for the Rprice and SAPrice of each part selected? Ultimately (and I seem to have this down pretty well already), I want the program to auto generate the notes with the parts and both prices for however many parts the tech discussed with the customer. So when the notes are generating it would look something like, “Coil cleaning Reg. “ & RPRice1 & “ with SA “ & SAPrice1
Let me know if I have been too vague or if more info is needed. Thank You.
-Paul

It may help to understand what kind of tables I currently have in Access 2010....

I have an employee table that shows all employees and some basic information about each of them. This table has an employee ID field that is not an autonumber but it is unique to each employee (10004928).

I have other tables that also use that same field and I want the first and last names to auto populate based on the main employee table. These other tables are tracking OT and Vacation time used.

How do I make the later tables auto populate the name fields when you key the employee ID???

I have relationships set up, but I do not know if that helps....

Hello All,

Based on one entry into a form, how can I have the form auto fill other fields that are related to that first entry?

Thanks in advance!

Hello,

I know auto-populating has been asked before for forms, but I am looking to auto-populate a couple of fields in a table based on an ID Number that corresponds to another table. I realize it is not the best practice to duplicate data in a DB, however, I am using an ID card program that requires tables to populate the ID card information. My hope is to have two separate tables for 2 versions of ID cards.

The first table is called Firefighter info with fields of ID Number, First Name, Last Name, and Firefight Certs, EMS Certs, and emergency contact information. The second table is called EMS Personnel with fields of ID Number, First Name, Last Name, EMS Certs, and other fields that will be entered manually. I would like the First Name, Last Name, and EMS Certs fields to auto-populate in the EMS Personnel table when the ID Number is entered. I have a relationship already listed between the two table that links the ID Numbers in both.

The Firefighter Info contains the information for all members of the department, regardless of whether they are EMS, or strictly firefighters. The EMS personnel table contains information strictly regarding to the EMS members. Thus, the ID number you are entering into the EMS table will always correspond to an existing entry in the Firefighter Info table.

First off, is this possible? Secondly, if it is possible, could you offer some guidance on how to accomplish this?

Your help is greatly appreciated!!!

-Chris

Hello Members,

My name is Cyril. I'm designing a database for the memberships in my Company. I'm very new to database/access but thought to learn and do it.

I have created a table "CTAS Memberships" which has fields like *MemberID, Membership No, Member Name, Joining Date, Address, Phone, Family Background, Introducer No, Introducer Name, etc.

I want to have a report to see who introduced whom in every month because we give a small reward to the people who introduced others every month.

Now at first, I would like to have a small form which asks the user to input the Membership ID and it should auto populate the Membership Name so that the user can verify if its the correct Membership No and then a combo box which asks the user to input the month and year. Based on the Joining date from the table CTAS Memberships, and the Membership No, it should give me a report which shows all the members introduced by that particular Membership No in the particular month & year. I do not know how to proceed.....

Kindly help as I'm stuck up with this...Your help would be appreciated...Thanks in advance....

Regards
Cyril

Not sure if thats a good title.

What I am looking to learn how to do is a selection of a drop-down menu that will populate several other fields whenever a selection is made. Or how to auto-populate text field(s) based on a dropdown list or combo box selection.

Before I come here for help I search diligently for a solution trying to discover it myself. I have searched for this for a long time now and there are vague and various (vba and non) solutions that I just don’t know how to incorporate into my needs now and in the future.

I don’t think it’s so important to find out what exactly im trying to do because I have found the need for this solution many times and will continue to do so. But for now I have a form to populate a budgetEntry table. There’s are various budget “items” such as tractor, irrigation, transplant, fertilizer and so on. There are various orders associated with those item “types”. What Id like to do is have the user select a “budgetType”, say “transplant” for example, and that combo box selection yields a “transplant” query result in the next field which is essentially a query of transplant orders or items to select from. I have the query, I just want to ability for user’s to start by selecting budget type and their next series of choices is based off that type, or keyword/tag (bad way to put it in db language).

How do I do this? Moreover, what is a good desitnation to archive so I can have the steps in doing this in the future.

ap uk fourms is an awesome resource and I learn much here. Thank you ahead of time.

I've looked at many of the posts that already address this issue... but am still struggling.

I created a form (AddrChg) with New address information (stored in AddrChg table). On the form, the user will be required to select a county (County table) from a list box (County table - has three columns: county number, county name, fax number). I created another field (text box?) on the form that I would like to have auto-populate with the Counties' fax number (...as mentioned, stored on the County table). So, if a user selected Polk county, the corresponding fax number for that row will autmoatically display in the "Fax Number" test box. The data entered in this form will be written to the AddrChg table.

I've tried a few of the things that were on other posts but I am obvioulsy too new at doing this to get it right cuz I have yet to be successful. I don't believe what I need is a cascading combo box. I fthese two foelds need to be bound, how? Do I need to add an event? If so, to which box (field) and what's the event? If I need to add code PLEASE be very specific - I am NOT a VB programmer (so I would need an explanatin on what it is I am adding and to where and how, etc etc etc). If there is something else I need to do, please keep explanation as simple as possible...

Thanks!!!!!

I have a database that tracks when I ordered Durable Medical Equipment. I have the following fields:

Replacement frequency (i.e 6 months)
Last Replacement Date (1/1/2012)
Refill Date

All fields come from the table named combo box for CPAP
What I want to do is to have the field “Refill Date” to be automatically filled to show the increase from Last Replacement Date and Replacement frequency. (1/1/2012 + 6 months = 6/1/2012
How would that be coded?

is there a way to have a form set to either auto populate fields based on the FIRST FIELD? For example: if the user type in the STUDY ID, the other fields like (first name, last name etc..) are auto-populated/auto-filled?
and if the STUDY ID is new and not in the table, then the users simply fill in all the fields/complete the form and save?

And I would also like a message box to show when I am typing the STUDY ID that would say something like: this person is on the system already. Click YES for this person and NO to add the user?...



does any one have any idea how I should go about doing this?



Thanks!

Hi experts,

I have a table which has some fileds some of the fields is realy based on the selection of others
e.g.


Table Cat11

Day Number
Brand Text textbox value based on bdesc (after update of bdesc)
Bdesc Text combobox lookup table brand
Bage Number
Bsex Number (1)Male (2)Female

Lookuptable for Brand
BRAND
NOT STATED 000001
A & P 000002
A CUT ABOVE 000003
A LA CARTE 000004
A. RICHMAN & SONS000005
A-LINE 000006
A-LINEEVERDES 000007

when a entry operator selects A & P in the desc combo the textbox automatically filled 00002
But the DATA doesn't store in the table..............it only display on the form.

Simply what I did to display / relationship for this two combo box
control source of Brand =[desce]
but this action only display whatever chose in desc that doesn't store in the table



How to I store the brand value which is auto populated by desc.

Any help would be appreciated.

I looked at a video on how to write a DLookup, and came up with this, but I get a run time error, something about a missing operator. This is an after update event, and when working will help me auto populate to save lots of time, for data entry in a manufacturer of automotive parts. The database is designed to collect data on the part numbers that are run during one of the 3 8hr shifts, and report efficiencies of various conditions. After the part number field is updated I need 5 other fields to populate. I have the part number data, which is the data used to have the database go I recognize this part number, it has this cycle time, and this cavities, and this description, and this tool number, and belongs to this customer. Below is the code I made, and the Run Time Error I got when trying to run it, and I do not know how to debug it!!!!


Customer = DLookup (“Customer” , “PartNumbersT” , “PartNumber=” & PartNumber)
Description = DLookup (“Description” , “PartNumbersT”, “PartNumber=” & PartNumber)
Cavities = DLookup (“Cavities” , “PartNumbersT”, “PartNumber=” & PartNumber)
CycleTime = DLookup (“CycleTime” , “PartNumbersT”, “PartNumber=” & PartNumber)
ToolNumber = DLookup (“ToolNumber” , “PartNumbersT”, “PartNumber=” & PartNumber)

Trying to get 5 fields to auto populate from a table that stores data based on the part number field, and what is entered, when I try to run this code I get
Run time error '3075':


Syntax error (missing operator) in query expression 'PartNumber='



Thank you for your time, and have a great day!

I'm extremely new to Access, databases, AND VBA and need help with having a date field automatically update based on the selection of a combo box next to it on a subform.

This is how my DB is currently set up:

tblOrderspkOrderID txtClient - Client Name txtDesc - Description of Order
tblOrderTaskspkTaskID fkOrderID - Many to pkOrderID fkTaskID - Many to pkTaskID datSched - Date Task is to be performed
tblTaskspkTaskID txtTask - Task Name numTime - Length of time it takes to complete task
I've built a form that has 2 text boxes for txtClient and txtDesc.

Below is a subform in datasheet view which has a combobox for fkTaskID. This is where it gets a bit complex and I'm sure requires VBA to accomplish. Each task takes a certain amount of time to complete (numTime). I want to have the first record in datSched be populated with Date() + numTime for the task selected. I want the following record to instead use the previous records datSched + numTime to autofill.

Basically what should happen is as tasks are selected they are scheduled automatically. From there I think I can figure out how to have it only schedule business days (I was able to sum together the numTimes and have a "Finish Date that always landed on a business day) but can't figure out how to get each task to auto schedule. If worse comes to worst, I can have it so each task has to be scheduled manually but I feel like the amount of room for user error is much higher that way.

Any help is greatly appreciated. I've been scouring the internet for solutions and trying different things and this site seems to have the most answers for all of the other problems I've come across.

Hi All,

Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!

My problem is easiest explained by my requirements....

I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.

This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.

Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.

How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?

I would realy appreciate any help on this! If you need me to clarify anything please ask.

Thanks in advance!

RobV

Thanks in advance to anyone who can help.

The intent of my database is to collect collect as efficiently as possible information from a client and be able to autopopulate as much information as I can such as County, City, and zip code. So far, I have a table called intake which is the main table for my database.

Secondly, I have a table callewd Counties that contains the 4 counties that I have autopopulating the Intake table via a lookup field.

I also have 4 other tables that contain all the city and zips for each of the 4 Counties. Here is where I am having the problem. How do I llink the city/zip table to the specific county table so that I can only see (via a lookup) and autopopulate the city and zip for the appropriate County?

I relatively new to Access but I have a basic understand and have designed many simple databases and forms from scratch; however, I've been given a task at work that is requiring me to do something that is altogether new to me.

In a form I created, I need two text fields to auto populate their values based on a selection from a dropdown list. The dropdown list has a series of names that are drawn from a different table. I'll call that table the "Name" table for this post. The "Name" table has three fields in it, the name of the rep (these names are the list in the dropdown field), their ID number and who their manager is.

OK, back to my form. When I select a rep name in the dropdown list, I want the ID text box and the Manager text box to fill based on the name I select. In other words, if I select "Mike", I want Mike's ID and Manager to auto populate on the form. There are other fields to complete but these I need to fill in automatically. I've read about the "On Change" procedure but I don't know how to do it.

I hope I explained that right. Please post the “For Dummies” version is possible. I really need to get this working.

Mike

Hello, everyone:

I have a split form titled, frmPhoneLog. On the top half, I have multiple textboxs that allow the user to input information about a single phone encounter with a client (i.e., the client calls us, we call them, etc.) There are fields like CallerName, CallDate, CallSubject, CallNotes, etc. There also three command buttons: cmdAddNew, cmdSaveEdits, and cmdClear. On the bottom of the split form is the characteristic table. Here is what I would like to do:

1. Allow the user to log an unlimited number of phone encounters with the client by inputting information in the upper part of the window and having it show up in the lower part. cmdAddNew will create a new row each time different information is entered.

2. Allow the user to select any of the phone encounters listed in the datasheet, for that information to re-populate into the textboxes described above for the purpose of editing/changes, and then for cmdSaveEdits to rewrite the table data accordingly.

Now, here is the rub... this entire form view needs to be specific to a single primary key. In other words, the purpose of the form is ONLY to log phone encounters with client John Smith (one person). Client Patty Sue has a different primary key and, thus, a different set of phone encounters. How can I link MULTIPLE records in a split form datasheet like this to ONE primary key and have it show up accordingly?

Lastly, I will need to work this out in the backend, too. Can Access automatically create table fields based on how many phone encounter records have been created? Can you point me to some resource for learning more about this "auto-population" or "cascade creation?"

Thanks for your help. I have learned so much about Access using this forum.

I apologize in advance if this is a re-hashed question, I'm still only a yellow belt in search-fu.

Here is what I'm trying to do. I have a combo box that will auto populate many other fields. It's great for data entry for returning clients. However it is very easy to "accidently" change the value. I want to place a prompt on the box that asks the user if they want accept the changes or cancel after updating the box. So basically (in my mind) it would be something like this...

If Combo30.OldValue = "" then

((populate action 1))

Else

' this is where im kinda stuck. i need another if function based on a text box

Msgbox "Client ID has changed. Would you like to accept the change?", vbQuestion + vbYesNo

' from thispoint on im not actually useing code, just what i want to do.
If vbyesno = yes then
else
((populate action 1))
If vbyesno = no then
Combo30 = Combo30.Oldvalue

I've worked with access before, but now I need it to do more. So I am pretty new to VBA. Would anyone have suggestions on how to make this work? Any help would be appreciated.


Not finding an answer? Try a Google search.