By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

AutoFill Multiple Items from Table by Reference Number

P: 5
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
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 5
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
Expert Mod 10K+
P: 14,534
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

P: 5
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
Expert Mod 10K+
P: 14,534
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

P: 5
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
Expert Mod 10K+
P: 14,534
Have a look at this tutorial.

Normalisation and Table structures
Mar 21 '07 #8

P: 5
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
Expert Mod 10K+
P: 14,534
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

Post your reply

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