Help - help with autofill fields in ms access

[selder]

I am sorry to take the liberty to email you directly, but I am in a really tight situation that I cannot seem to figure out. I am creating a database in MS Access. I have created several tables that I would like to link with each other. I created a form called users, within this form there are several fields that I would like to be autofilled the moment I choose an option from a combo box. (i.e. there is a table called Location, within this table I have the following fields: location name, address, city, state and zip code/postal code. I have entered three different locations. Corporate, Spaid and Remote. Each location have different address information. Then I went into my User table and created a combo box named location, I want to be able to choose one of the three locations above, and have the fields, address, city, state and zip code/postal code automatically filled based on the chosen location.

I have no clue how to do this. Can you please help? Also, do I need to create the fields address, city, state and zip code in the User form in order to be autofilled? Please advise.

Thank you so much.

Sincerely,

[timothybard]

How do you have your tables set up? Are there address, city, state and zip code fields in the User table or are they only in the location table?

[selder]

How do you have your tables set up? Are there address, city, state and zip code fields in the User table or are they only in the location table?

Dear Timothy,
First thank you for replying to my thread. The way I have this setup is as follows;
I have the first table is called Location, within this table I have the following fields:
Location name
Address
City
State
Zip Code

Then I have another table called Departments, within this table I have the following fields:
Department Name

Then, I have one more table setup called Users, within this table I have the following fields:
User Name
Location (this info I wanted to be a combo box, with the data already entered from the location table, which by the way there are only three locations).
Address
City
state
zip Code (all these fields are being reference I have them setup based on the information chosen from the location combo box)
Department (this I wanted to be another combo box where the user will pick the department that they belong to. There are 15 departments loaded in the table.

Here is my problem. I created the first combo box using the wizard, it auto populates the correct fields above, but I notice that as I add new users, the choices in the combo box start to increase. Here is what I mean, in the location combo box, as previously stated there are only three locations: Corporate, Home, Remote. As I add a new user, and choose a location, the fields address, city, state and zipcode are auto populated, thus far so good. Then I go and choose a department for my user, also so far so good. Here is the problem, next time I go into to add a new user, when I click on the combo box to choose the location for this new user, now the location options lists the following: Corporate, Home, Remote, 3. And the next user I add, then it says the same thing plus one more number, Corporate, Home, Remote, 3, 3 (this happens if it is same location, if I choose another location, let's say home, then it lists a 1. Thus, Corporate, Home, Remote, 3, 3, 1. The same symptom with the departments.

Finally, I have loaded about 20 users, and next time I access my users form to add a new user in the bottom of the form where the navigation buttons are, it always says 1 of 1, but when you go into the table you see all 20 records.

Can you please tell me what I need to do??

Thank you in advance for your help.

[timothybard]

Please find attached a database I put together. Without a copy of your database, it's hard to tell exactly what is going on. Therefore, I put together a database to do what I think you are trying to get your database to do.

My database has two forms: a users form and a locations form

You are able to modify locations in the locations form. The users form has two dropdown boxes: one for locations and one for departments. When you select a location, the text fields are populated with the values in the locations table; please note that, since these text boxes only reference the data in the locations table, they can not be updated. This database can be designed so that you can, but I wanted to show you this version first.

Please let me know if you have any questions with what I put together.

[selder]

Dear Timothy,

Once again, I thank you for your reply. I opened your database and I did see both forms, however, when I went into the user form, and choose a department and a location, it did not auto pupulate the city, state or the zip code, they stayed blank.

If you wish, and have time, I could send you a zipped version of my database for your review. I am going bald! trying to figure this out, which I have a feeling may be something very simple, yet I need to learn it so I do not bother people like you, with my silly questions.

What I noticed in your sample database was that in the location form, there was no field for location name, so when I went into the user form, and was time to choose a location, it only show the data from the address field and not the location name, since there is none to choose from, but even so, even if I choose an address it will not populate the rest of the fields.

Let me know what should be the plan of attack...

Regards,

Sandy

[timothybard]

Thank you for your replay. I have attached an updated version of the database. I included a Location Name for the location table. I also updated the vba code so that the address fields auto populate with the form is loaded, when you change records and when you choose a different Location.

Please let me know if this database works the way you would like yours to. If it does, then I can go into more detail about the vba code and database design with you.

Don't worry about asking your questions here; that's why the forums are here! If it was a bother to us, we'd just ignore the forums. I was fortunate enough to work under someone as a programmer and learn from him; we all started as beginners and learn from somewhere.

[asifrshah]

Thank you for your replay. I have attached an updated version of the database. I included a Location Name for the location table. I also updated the vba code so that the address fields auto populate with the form is loaded, when you change records and when you choose a different Location.

Please let me know if this database works the way you would like yours to. If it does, then I can go into more detail about the vba code and database design with you.

Don't worry about asking your questions here; that's why the forums are here! If it was a bother to us, we'd just ignore the forums. I was fortunate enough to work under someone as a programmer and learn from him; we all started as beginners and learn from somewhere.

Thanks for the sample DB Timothy. I am trying to use your sample DB and recreate it but I keep running into problems.
I just want to select a part number from a combo box and it should autofill another field with the part name. Your DB is doing that but when I create the same thing it isnt. I think I am missing something in the code...

Option Compare Database

Private Sub Part_Number_AfterUpdate()
Me.Text4 = Me.Part_Number.Column(2)
End Sub

Thanks.

[gmalsack]

Hello,

I'm having a problem with auto-fill as well. However, mine is different than this one. I want to enter a last name in a field, then a first name in another field, then have a 3rd field that automatically populates with the previous 2 fields formatted as such: lastname, firstname.

Any suggestions...

[HI2Japan]

gmalsack do you want to just have the name display that way, or do you want the names to be entered into your database permanently after typeing them in like that.

If you want to just have them display that way, you can do so on a form or report by having an unbound textbox and putting in a value of =[Lastname] + ", " + [Firstname]

You might need to use & rather then + depending on the data in your table.

Have both the last and first name controls on the form or report and just set their visibility to No.