473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

AutoFill Multiple Items from Table by Reference Number

I'm working on a database for comment cards (similar to the ones at restaurants with the number scoring) I have 3 boxes at the beginning of each entry I would like to have filled automatically. The boxes I want filled are Area, County, Center. Each individual center has a specific number. I have entered all that information onto a table with it's ID Number, Area, County, Center. In that order. How can I get it to automatcially fill in the Area, County, Center when I type in the ID Number? Is it even possible? I'm new to access, I don't know much about coding. I've played around a little with information I got from the Step By Step Microsoft manual but just haven't been able to get it right. I had one VB class about 5 years ago but other then that I mainly do things in Excel so the macros and events and such are a little confusing to me.

Thanks!
Mar 20 '07 #1
9 1873
MMcCarthy
14,534 Expert Mod 8TB
Are you entering data through a form?
Is the form bound to the table?
Is the ID field an Autonumber?
Do you want to find existing records?

Sorry but it's really not clear from your question exactly what you are trying to do.

Mary
Mar 21 '07 #2
I can answer those. Sorry I couldn't be more clear.
*The data would be entered through a form.
*The form is bound to a table called SurveyData that straight stores all info with autonumbers on them.
*The ID field is an autonumber at the moment but I"m not sure that's what it needs to be.
*The only thing I want to do with the existing/entered records is put them on reports. I will not be using a form or such to search for them.

I'm not really good at all this stuff. But What I had last year was a form, it had Area, County, Center, Q1 - Q9, and Comments. I'm entering over 2000 survey cards. Each Center is assigned a specific number that stands for that Area,County, and Center which are all in seperate fields. I want to be able to enter that number on the form instead of going through a combo list for Area, County and Center.

I hope that helps.



Are you entering data through a form?
Is the form bound to the table?
Is the ID field an Autonumber?
Do you want to find existing records?

Sorry but it's really not clear from your question exactly what you are trying to do.

Mary
Mar 21 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
I can answer those. Sorry I couldn't be more clear.
*The data would be entered through a form.
*The form is bound to a table called SurveyData that straight stores all info with autonumbers on them.
*The ID field is an autonumber at the moment but I"m not sure that's what it needs to be.
*The only thing I want to do with the existing/entered records is put them on reports. I will not be using a form or such to search for them.

I'm not really good at all this stuff. But What I had last year was a form, it had Area, County, Center, Q1 - Q9, and Comments. I'm entering over 2000 survey cards. Each Center is assigned a specific number that stands for that Area,County, and Center which are all in seperate fields. I want to be able to enter that number on the form instead of going through a combo list for Area, County and Center.

I hope that helps.
So in the After Update event of entering a number in this field you want the other fields to populate.

Something like ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub FieldName_AfterUpdate()
  2.  
  3.    Me!Area = DLookup("[Area]", "TableName", "[ID Field]=" & Me!FieldName)
  4.    Me!County = DLookup("[County]", "TableName", "[ID Field]=" & Me!FieldName)
  5.    Me!Center = DLookup("[center]", "TableName", "[ID Field]=" & Me!FieldName)
  6.  
  7. End Sub
  8.  
Mary
Mar 21 '07 #4
Okay. I'm sorry bare with me here. It's been a long time since I've messed with this program.

So what I need to do is put in a text box, and in the After Update event of that box I need to put in the code you attached in correct?

Now Do I use three more text boxes below for the information to appear in?

If so, are those bound to the SuveryData table where I want all my information to go or to the table where I have each of the Centers listed?

So in the After Update event of entering a number in this field you want the other fields to populate.

Something like ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub FieldName_AfterUpdate()
  2.  
  3.    Me!Area = DLookup("[Area]", "TableName", "[ID Field]=" & Me!FieldName)
  4.    Me!County = DLookup("[County]", "TableName", "[ID Field]=" & Me!FieldName)
  5.    Me!Center = DLookup("[center]", "TableName", "[ID Field]=" & Me!FieldName)
  6.  
  7. End Sub
  8.  
Mary
Mar 21 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Okay. I'm sorry bare with me here. It's been a long time since I've messed with this program.

So what I need to do is put in a text box, and in the After Update event of that box I need to put in the code you attached in correct?

Now Do I use three more text boxes below for the information to appear in?

If so, are those bound to the SuveryData table where I want all my information to go or to the table where I have each of the Centers listed?
Assuming Area, County and Centre are in another table you only need to bind the number referencing them to the Survey data table as a foreign key.

Actually in that case you are better off creating a query joining these two tables to get the data into your form.
Mar 21 '07 #6
Oh wow, this may be a lot harder then expected.
I listed the Reference No, Area, County and Center all in a table. The information is stored to another table though. I am not familiar with doing foreign keys or how to do queries. Is there any chance of there being a website that may be able to walk me through a procedure like this? I have a step-by-step Access 2003 book but it's fairly simple and non-complex.


Assuming Area, County and Centre are in another table you only need to bind the number referencing them to the Survey data table as a foreign key.

Actually in that case you are better off creating a query joining these two tables to get the data into your form.
Mar 21 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Have a look at this tutorial.

Normalisation and Table structures
Mar 21 '07 #8
Alright after looking at that tutorial some and playing around I might be getting close. The issue I'm having now is the code isn't working. When I start the form I get the text "#Name?" in the boxes where I should have data appearing AFTER I enter the number in the Code Area box.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CodeArea_AfterUpdate()
  2.  
  3.    Me!Area = DLookup("[Area]", "AreaCodeChart", "[Code Area Field]=" & Me!Area2)
  4.    Me!County = DLookup("[County]", "AreaCodeChart", "[Code Area Field]=" & Me!County)
  5.    Me!Center = DLookup("[center]", "AreaCodeChart", "[Code Area Field]=" & Me!Center)
  6.  
  7. End Sub
  8.  
I feel like I may have an error in my coding.

The boxes that are receiving the information from the Code Area, should they be bound to anything other then the sheet I want all my data going to which is the SurveyData table?

Should the area I have "AreaCodeChart" be the table that is pulling the Area, County and Center from the number or should it be where I want the info going?

And should the [Code Area Field] be the name of the field the number for the center is in or something else?

Hopefully that makes sense.


Have a look at this tutorial.

Normalisation and Table structures
Mar 21 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
Assuming you now have a field in the Survey Data Table which corresponds to the ID field in the Area table then try the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CodeArea_AfterUpdate()
  2.  
  3.    Me!Area = DLookup("[Area]", "AreaCodeChart", "[ID Field]=" & Me!IDField)
  4.    Me!County = DLookup("[County]", "AreaCodeChart", "[ID Field]=" & Me!IDField)
  5.    Me!Center = DLookup("[center]", "AreaCodeChart", "[ID Field]=" & Me!IDField)
  6.  
  7. End Sub
  8.  
Mar 22 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: shortbackandsides.no | last post by:
I'm having a lot of difficulty trying to persuade the Google toolbar autofill to act consistently, for example ======================= <html><head> <title>autofill test</title> </head><body>...
0
by: Ray Holtz | last post by:
Is it possible to autofill a field based on what is entered into another field in a form? My form has an employee field, and department field. In an Items Table, I have fields FldEmployee, and...
3
by: D. Shane Fowlkes | last post by:
Sorry for the length of this post. I have created a rather complex form which has a header/line item (parent and child records) structure. It's for an intranet. A screenshot can be seen here: ...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
4
by: jayscott1 | last post by:
What I would like to do is have the user start typing in the last name and as they were typing it would retrieve names from the database and give a drop down like autosuggest in google. That much...
2
by: karups | last post by:
Hi, I've got listbox in my .aspx page where the users can make multiple selection. So, Users can select any number of items in listbox, I have to take value from items and pass it to stored...
5
by: Sherry | last post by:
Is there a way to automatically, or by using say a function key, call/paste into a current table or query's field the same value entered for the same field in the immediately preceeding record,...
2
by: englishman69 | last post by:
Hello, I have been banging my head against this one for a while... Searches online have revealed many different proposals for correcting my issue but none that I can follow! My basic situation...
8
by: preeny8 | last post by:
Hi guys, So I'm editing an existing database (Access 2003), and I need a bit of help in making a validation rule. My table has many fields, 3 of which are indexed (location, number & revision) ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.