Auto Fill Fields

I have a database of contacts (names, addresses, phone numbers, emails). I
have created a table with this info already filled out (imported from Excel).
All I want to do is create a form with a single drop-down list where, when
the last name is selected, all the other fields automatically populate with
that person's correct information.

I have tried DLookup but that seems too cumbersome and doesn't work unless I
go to design view and back to form view. Is there not a simple function in
Access that lets me do what I need to without having to write code. I am NOT
a code writer, don't understand it, but willing to try if that's the only
option.

0
Carlnlola
4/21/2010 6:44:44 PM
access.forms 6864 articles. 2 followers. Follow

6 Replies
875 Views

Similar Articles

[PageSpeed] 52

It is easier to use a form filter. Install a combobox. Allow the combo wizard 
to do this for you. Set it up for Last Name. Then right click on the combo, 
go to the afterupdate event, click the button to the right, and start the 
code builder. Then type:
Me.Filter = "[lname] = """ & Me.combo# & """"
Me.Filteron = True

The combo# will be displayed, like combo10 or combo22.
-- 
Milton Purdy
ACCESS 
State of Arkansas


"Carlnlola" wrote:

> I have a database of contacts (names, addresses, phone numbers, emails). I
> have created a table with this info already filled out (imported from Excel).
> All I want to do is create a form with a single drop-down list where, when
> the last name is selected, all the other fields automatically populate with
> that person's correct information.
> 
> I have tried DLookup but that seems too cumbersome and doesn't work unless I
> go to design view and back to form view. Is there not a simple function in
> Access that lets me do what I need to without having to write code. I am NOT
> a code writer, don't understand it, but willing to try if that's the only
> option.
> 
> .
> 
0
Utf
4/21/2010 8:29:02 PM
You are AWESOME! Thanks...works like a champ!

-Carl

golfinray wrote:
>It is easier to use a form filter. Install a combobox. Allow the combo wizard 
>to do this for you. Set it up for Last Name. Then right click on the combo, 
>go to the afterupdate event, click the button to the right, and start the 
>code builder. Then type:
>Me.Filter = "[lname] = """ & Me.combo# & """"
>Me.Filteron = True
>
>The combo# will be displayed, like combo10 or combo22.
>> I have a database of contacts (names, addresses, phone numbers, emails). I
>> have created a table with this info already filled out (imported from Excel).
>[quoted text clipped - 9 lines]
>> 
>> .

0
Carlnlola
4/21/2010 9:01:19 PM
golfinray:

Ok...your solution works great but there's a small issue. My database
contains over 500 names and inevitably, there's duplicate last names. How do
I get the thing to show each person's info (right now, it filters out stuff
and the same info is displayed for 3 different Jones')

Thanks for taking the time to help out

-Carl

Carlnlola wrote:
>You are AWESOME! Thanks...works like a champ!
>
>-Carl
>
>>It is easier to use a form filter. Install a combobox. Allow the combo wizard 
>>to do this for you. Set it up for Last Name. Then right click on the combo, 
>[quoted text clipped - 9 lines]
>>> 
>>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

0
Carlnlola
4/22/2010 11:54:22 AM
One of the problems with using name is that there may be 4 or 5 Jones'. Can 
you assign a unique ID number to each person, then search on the ID number. 
You could even set up an ID like 
001 Jones L
002 Jones T
003 Jones B
etc.  Even better would be just to have  cascading combo boxes (2) and have 
a reference to the individuals, like Jones. Then the second combo you pick 
T., B., or L. Look at the search feature Allen Browne has at 
www.allenbrowne.com or Martin Greene at www.fontstuff.com
Good Luck!
-- 
Milton Purdy
ACCESS 
State of Arkansas


"Carlnlola via AccessMonster.com" wrote:

> golfinray:
> 
> Ok...your solution works great but there's a small issue. My database
> contains over 500 names and inevitably, there's duplicate last names. How do
> I get the thing to show each person's info (right now, it filters out stuff
> and the same info is displayed for 3 different Jones')
> 
> Thanks for taking the time to help out
> 
> -Carl
> 
> Carlnlola wrote:
> >You are AWESOME! Thanks...works like a champ!
> >
> >-Carl
> >
> >>It is easier to use a form filter. Install a combobox. Allow the combo wizard 
> >>to do this for you. Set it up for Last Name. Then right click on the combo, 
> >[quoted text clipped - 9 lines]
> >>> 
> >>> .
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1
> 
> .
> 
0
Utf
4/22/2010 12:55:01 PM
Thanks for getting back to me. I already did what you suggested and just put
the Last Name with first initial. Seemed the easiest way and it works for
what I need it to do.

Thanks again for all your help.

Kindest Regards,
Carl

golfinray wrote:
>One of the problems with using name is that there may be 4 or 5 Jones'. Can 
>you assign a unique ID number to each person, then search on the ID number. 
>You could even set up an ID like 
>001 Jones L
>002 Jones T
>003 Jones B
>etc.  Even better would be just to have  cascading combo boxes (2) and have 
>a reference to the individuals, like Jones. Then the second combo you pick 
>T., B., or L. Look at the search feature Allen Browne has at 
>www.allenbrowne.com or Martin Greene at www.fontstuff.com
>Good Luck!
>> golfinray:
>> 
>[quoted text clipped - 16 lines]
>> >>> 
>> >>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

0
Carlnlola
4/22/2010 2:17:40 PM
You are only deferring the inevitable if you do not use a unique identifying
field as suggested.  Unless the list will never grow, you almost certainly
will get duplicate combinations of last name, first initial.

Carlnlola wrote:
>Thanks for getting back to me. I already did what you suggested and just put
>the Last Name with first initial. Seemed the easiest way and it works for
>what I need it to do.
>
>Thanks again for all your help.
>
>Kindest Regards,
>Carl
>
>>One of the problems with using name is that there may be 4 or 5 Jones'. Can 
>>you assign a unique ID number to each person, then search on the ID number. 
>[quoted text clipped - 12 lines]
>>> >>> 
>>> >>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

0
BruceM
4/22/2010 3:17:11 PM
Reply:

Similar Artilces:

Exchange 2000
Hi all I've recently renamed a user. Now I found that everytime I type that user name in the outlook, it shows me the name like this NewUserName <OldUserName>. I've checked all in the AD, but not found anythings related to the old user. How to correct it? Please help. Thanks BT Just a few points ... 1 - you can delete the names from the autocomplete by highlighting them (use the keyboards arrow keys) and hitting the DEL key. 2 - the <OldUserName> is displayed because that is the user's LegacyDN value. This value can be changed by using adsiedit however if...

New field in Workflow manager
Hi! I have added new schema field in Deployment Manager. I can see it also in CRM application and add this field in form, but I can not work with this field in Workflow manager. Can You help me someone with this problem? Thanks. Dusan Hrusovsky. Did you restart the workflow service? if not, try that and restart the wf manager. That should help. Regards, shashi. "Dusan Hrusovsky" wrote: > Hi! > > I have added new schema field in Deployment Manager. I can see it also in > CRM application and add this field in form, but I can not work with this > field in Work...

Conditional Formatting: Fill Cell if Formula Overwritten
Dear all, Cell A1 contains a formula. How would I go about conditonally formatting A1 such that the cell will be filled if the formula is overwritten by a hard number? Thanks in advance, Neil Any reason not to lock the cell and use worksheet protection to keep people from accidentally overtyping the formula? Here's one option, I'm sure there will be others. Start in your cell of concern (lets use A1), it has a formula in it such as =A2+B3 In another cell somewhere put in the same formula, but subtract 1 from the result as: =A2+B3-1 Let's say that ...

Excel should allow auto shape boxes to be fixed to points on the g
I have constructed a graph that has data added to it daily. On the graph I have used several auto shape boxes to highlight key data milestones. Problem is every time I add new data the auto shape boxes move and have to be manually adjusted to repoint them to the relevant data point. Is there any way they can be fixed to the relevant data points on the graph so that they remain fixed to that point and therefore don't move/shift when new data is added? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for ...

Auto Format
Can you create custom Auto Formats in Excel -- Message posted from http://www.ExcelForum.com ...

multi-value field
I'm still learning Access, so I must apologize in advance for my question. I have four tables, tblBrochure, tblStaff, tblOrganization, tblResourceDistribution. tblBrochure has two fields -BrochureName -Active (y/n) tblStaff has two fields -StaffName -Active (y/n) tblOrganization has two fields -OrgName -textbox (0 and 1, explained below) tblResourceDistribution has 5 fields -DistribDate -StaffName (lookup from tblStaff ->Staffname -Organization (lookup from tblOrganization ->OrgName) ...

How to Auto duplicate cell contents?
Suppose if I type 'ABC' in 'A3' & 'DEF' in 'C4' on worksheet1 cells an I wish them to automatically show in certain cells in other worksheet (and also may be other workbooks. I tried to enter =Sheet2!D19 in sheet1 A1 cell. It works fine if had contents in sheet2 d19. But if I changed the contents in Sheet2 D19 The content in sheet1 A1 doesn't change to new content. It still show the old content! It also only show me '0' if I enter the formula befor I type in any content. And it also just remain '0' even after I entere text in sh...

Displaying multivalue field data in report
I have an Access 2007 database that was built upon the Access Contacts template. A multivalue field called Tags was added to the Contacts table that may have any or all values available in a table called Attributes. I have some sample data of contacts where the tag field for a record may contain a single tag like smart, or several values like smart, tall, agressive. When I try to display the tag values for each record in a report the displayed value is numeric like 2, 4, 7. How can I get the report to show the actual values instead of what appears to be an index value for eac...

merging a value in a text field with each line in a memo field
I am trying to merge a value in a text field with the information in a memo field using a query to create a report. This is the query I am using: SELECT AVRelay+","+IPRange FROM T_Sites AVRelay is the text field and IPRange is the Memo. IPRange has multiple lines, each of which I need appended with the value in AVRelay for the report. It should look like. 2,192.168.1.0/24 2,192.168.0.0/24 2,192.168.2.0/24 Instead it looks like 2,192.168.1.0/24 192.168.0.0/24 192.168.2.0/24 I have searched far and wide but has come up empty. Please help! I would gues...

Running total field
I have built an education assistance database for tuition reimburstments. The running total field I have on the data entry form needs to reflect how much each employee has received in the current calendar year. The school information, semester and dollars are in a sub-form of the data entry form for employee information. I have a running total control on the employee form that pulls from the subform school, however I can not get it to calculate only between dates of 1.1.2008 and 12.31.2008. Any help will be appricated. Thank you Michael S. On Wed, 26 Dec 2007 06:48:01 -0800, Michael ...

CRM should allow auto numbering of the products
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=9c8e0ba6-f37a-4fc1-84fd-ff5b578e2c76&dg=microsoft.public.crm ...

Taking sum of a field from sub report
Hi On a sub report I have a field datetot which is the sum of two fields those two fields are in turn coming from two further sub reports of the sub report. How can I take the sum of datetot field to the main report? Thanks Regards See: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" <John@nospam.infovis.co.uk> wrote in message news:OCWbU...

how to create a calculated field from another calculated field?
In a pivot table report I would like to add a calculated field that is like the following: = count( columnA ) / count ( columnB) But when I create a calculated field like the above, all I get is a value of 1 in every row of the pivot table. I realize after some experiments, its really performing = count( sum ( columnA ) ) / count( sum ( columnB ) ) So , how do I get what I want? ...

Auto Sum + Conditional Formatting
I would like to take one cell and have it with the Auto Sum feature. Here's the catch: I have applied a simple Conditional Formatting to a number of cells. The formatting is setup for blank formatting: equal to ="" There is nothing between the quotes, which tells the formatting that if there isnt anything in the cell, then give the cell a color. If ANY value is placed in the cell, take away the color. So with that said, is there a way to have the Auto Sum feature add up those cells and tell me how many that are left that need a value? For example, if there are 60 cells to...

Field Level Security not on Menu
I tried to locate the "Field Level Security" option, but it is not on the menu, Tools>Set Up> System, even with the sa log in. Is it a separate module that need to be purchased? We are using the Advanced Security. Our server spec is GP9.0, SQL Server 2005, Win 2003. kka, yes, Field Level Security is an exra-cost option. Frank Hamelly, MCP NOVA Solutions LLC Melbourne, FL KKA, Your Dynamics.set File must have a Dictionary 3104 Advanced Security as one of the product. Uou must have licesence keys to use it. as Frank Said. Note: License keys for Advance and Field Leve...

Field-level Help Files
Has anyone implement this with their dexterity third-party applications? The Winhelp Functional Library mentions a field-level help capability but it seems that even GP does not implement this feature. I'm currently using version 8.0. Thanks. ...

Find Last instance of carriage return In a Memo Field
Hi, I have a form where the user usually pastes a glob of text into it a memo field called "Comment". After he pastes the text, the cursor is obviously at the end of the text. I would like the user to be able to click a button and for the comment to be highlighted from the very end of the text to the first instance of a carriage return going backwards. In other words, I want to automatically select the very last line of the comment. I was thinking that I could use seldown, but I would need to first find the Last instance of Chr$(13). Or, can the instr function can be ...

auto populating comma seperated data in one cell to multiple cells
I want to be able to copy a csv file into a single cell in an excel spreadsheet. I then want to apply a formula to various cells which result those cells being automatically populated with relevant data from the csv file when I paste it. The formula needs to take into account that the relevant field in the csv file could be empty or could have 1+ chars. The reason I want to do this is that in the future when I run data extracts, I can check the contents of the csv file by pasting it into a spreadsheet I have set up with all the database tables and column names in it. I hope this makes sense...

Crystal XI will not show all possible values in a parameter field
Hello: We have a Crystal XI report that has one parameter value that is a "static" rather than "dynamic". It works fine and, when you type in a value upon refreshing the report, the data is correct. We made a copy of the report, because we want a second report where the parameter value is dynamic rather than static. We do not want a range, but we do want a parameter that will show all possible values on the left and allow you to pick and choose one or more of those values. For some reason, not all of the possible values are showing in this dynamic parameter field...

Populating a field using a pull-down/combo box menu
Hello all, I'm working on MS Excel. I am trying to find the source code for something that will allow me to do the following novel procedure with a pull-down menu and a text box: There is a pull-down menu, and another text box right besides the pull-down menu. The pull-down menu has the names of all the states in the USA in it. When one state is selected, the text box is automatically populated with the capital of that state. In MS Excel, I've created a novel way to handle this issue using Boolean logic. Unfortunately, I can only have 7 states in my pull-down menu, and the sour...

Custom baseline fields
Hello, I have created a custom table which includes extra cost columns for each task. I use these columns to direct enter material and travel costs. I then have another cost column which totals the labor ([COST] column and these other cost columns. I would like to save this subtotaled column as a baseline since it represents my total cost for that task. Is there any way to add additional fields to the original 9 baseline fields available? I can't seem to find a way to do this and it would be very helpful. Thanks No there isn't. You will need to manually c...

Field can not be updated....Why?
Dear All, Why does access send me below error message "! Field can not be updated " I type some value to my field but the error message appear,I have to click OK 4 times and then the value I type is typed.And this condition occur for the first typing only then others work normally.?Any guidance? I think this error occur cause I input the data into a child table,but although the relationship is deleted, the problem still exist.Why? I use access 2000 and my form type is continuous form. I plan it to become subform.Please help.Any assist would be greatly appreciated.Thank's...

auto-fill Contacts
Is there an add-on from Microsoft or others that can populate a New Contact with info from an Outlook 2002 e-mail, Word document, etc.? Bernie Unclear post. Clarify what you want to do and in what format the information currently exists. Look At File > Import and Export to see what file types can be imported. -- Russ Valentine [MVP-Outlook] "baltobernie" <bernie@pennstar54.com> wrote in message news:ARh4e.3622$%b1.630@trnddc08... > Is there an add-on from Microsoft or others that can populate a New > Contact with info from an Outlook 2002 e-mail, Word documen...

Inser the current date into a field in a Table
Hi, I want to insert the current date into "LastUpdateCost" field fromthe "CYCLETICKETNUMBER" table. I have the following code but is notcomplete. Can you help me????? strSql = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) Date ;" db.Execute strSql, dbFailOnErrorThe code is incomplete because I don't know the what code I need to dowhat I want. Thanks for your help. Hope you have a nice day.JC jeanhurtado@gmail.com wrote:> Hi, I want to insert the current date into "LastUpdateCost" field from> the "CYCLETICKETNUMBER" table. I h...

Changing Pivot Table Value Field Settings
Hi All, I'm using the Visual Reports feature in MS Project that produces reports based on Excel pivot tables connected to cubes produced by Project. I know that when using a cube as the data source I (unfortunately) cannot use a calculated field, so I'm trying to change the Value Field Setting instead, but the result is always the same, it just produces #N/A. In Project, I've selected to use the Assignment Usage cube. In Excel I have made the following selections: Row Label = Resources Column Labels = Values Values = Baseline Work, Actual Work What I'm trying to do is disp...