Access auto populating text box based upon combo box

[seb481]

I'm a complete noob at access, this is for a running club project in ICT.

I've got a form with 3 boxes
combo box for run id
text box for run date
text boc for location

once the Run ID is selected i want the two text boxes below to display the location and date for this run respective of what the used enters into the combo box.

How do i do this please?

Thanks in advance

 

[jdraw]

Did you forget part of your post/question?
Perhaps you could describe the application, or the table or location where the data
is stored. Maybe even post a jpg of your tables and relationships.

What's in the combo box? Where did it come from?

 

[seb481]

Did you forget part of your post/question?
Perhaps you could describe the application, or the table or location where the data
is stored. Maybe even post a jpg of your tables and relationships.

What's in the combo box? Where did it come from?

The database if for a run club

the tables are
runners
runs
locations
and results

The form i'm making is one that the user will select the run ID (from the run table) with a combo box whihc i've done.

then all i need is for the text box below to be updated with the location and date taht this run ID has. I need this so when the used presses a button it can be saved into the results table. i can do everything else (i think :s)

Is more information required? i can screenshot some things and put tem in a zip if neccesary. i was just hoping someone has a decent link that teaches me.

 

[yakjockey]

Join the club. I, too, am quite unfamiliar with Access and have virtually the same question.

I want to create a simple form which displays a combo box. The combo box displays three fields for a number of records in the table.When a user selects one of the choices (e.g. record ID) being displayed, I want to automatically populate other fields in the same form from the remaining displayed fields of the selected record.

For example, I have a table of cars which has three fields: ID, make, model. The combo box displays all the cars in the table by ID, make and model. When the user selects one of the IDs, I want to automatically fill in the forms text fields for make and model from the selected record.

I'm sure this is pretty much a text book issue, but finding a solution in the vast array of information on Access if formidable.

 

[Pat Hartman]

Start by creating a form that is bound to a query that selects the data you want to display. The wizard will do this for you. Once the form is created, open it in design view and add an unbound combo to the form's header. Again, the wizard will build this for you. When you put a combo on a bound form the wizard will ask if you want to use this combo to find a record. Answer yes and select the table/query that will be used to populate the combo's RowSource (the list of available values).

The important point to take away is that the search combo is unbound and is separate from the bound textbox that contains the corresponding value for the current record.

So, there will be a bound RunID control which shows the value for the current record and there will be an unbound combo that lists all the RunIDs in the table from which you choose the one you want to display.

 

[yakjockey]

Pat...I'm a professor of computer science so I'm reasonably familiar with all the concepts, but I must admit that I'm not totally sure I followed what you've suggested. For example, I'm not sure which wizards I should use and how I get them to do what you suggested.

Perhaps I should just send you a simple database and see what you mean. Would that be acceptable.

I don't want to take up too much of your time, but if I see what you did, then perhaps it would greatly clarify things.

 

[yakjockey]

Pat, perhaps this will help.

Consider two tables:

Vehicle
VehicleID
Make
Color

Owner
OwnerName
CarOwned
Make
Color

A one-to-many relationship will eventually exist between the Vehicle.VehicleID and the Owner.CarOwned fields.

I want a form which permits me to create a new Owner. I enter the Owner's name. Then a combo box displays the choice of Vehicles by their VehicleID, Make and Color from the Vehicle table. This is easily doable with a simple SELECT statement.

Once the user selects one of the displayed vehicles in the combo box, I want to automatically populate the Owner.CarOwned field with the Vehicle.VehicleID value of the selected record, the Owner.Make field with the Vehicle.Make value and finally the Owner.Color field with the Vehicle.Color field.

 

[Simon_MT]

This should be done on the Owners Form enter the Owner, VehicleID (AutoNumber) then with combi-boxes

Select Vehicles.Make, Vehicles.Desc From Vehicles;
Select Colors.Color From Colors;

Simon

 

[yakjockey]

Simon,

If you look at my attached simple database, you'll note that before someone enters owners, they have already entered a number of vehicles. By the time the owner's form is opened, specific combinations of make/color have already been associated with various VehicleIDs.

So, when entering owners, the user is supposed to be presented with a combo box displaying all the vehicleIDs, along with their respective make/color combinations.

In other words, I want to select one of the VehicleIDs and have the form's MAKE and COLOR fields automatically filled in from the selected Vehicle's record.

 

[Pat Hartman]

You said you were very familiar with Access. I'm not sure why you'lve never seen the wizards. I've attached a picture of the Create ribbon in Access 2010. the ribbon is similar in A2007. In earlier versions, you get to these wizards via the menus. If you've ever added a button or a combo or an option group among other things to a form or report, a wizard opens up and walks you through the process. Those are the wizards I was referring to.

 

[Pat Hartman]

Pat, perhaps this will help.

Consider two tables:

Vehicle
VehicleID
Make
Color

Owner
OwnerName
CarOwned
Make
Color

A one-to-many relationship will eventually exist between the Vehicle.VehicleID and the Owner.CarOwned fields.

I want a form which permits me to create a new Owner. I enter the Owner's name. Then a combo box displays the choice of Vehicles by their VehicleID, Make and Color from the Vehicle table. This is easily doable with a simple SELECT statement.

Once the user selects one of the displayed vehicles in the combo box, I want to automatically populate the Owner.CarOwned field with the Vehicle.VehicleID value of the selected record, the Owner.Make field with the Vehicle.Make value and finally the Owner.Color field with the Vehicle.Color field.

Your schema is not properly normalized. You have duplicated color and make in the Owner table. In addition to being a duplication of data that is associated with a vehicle in the vehicle table, it also limits you to a description of a single vehicle. Isn't it possible for owners to have multiple vehicles? That would be a one to many relationship so the OwnerID is placed in the vehicle record to indicate who owns a particular vehicle. You don't place the vehicleID in the owner record since that would limit you to a single vehicle per owner.

Also, all your relationships are wrong. Relationships go from a data field to a primary key. They are NEVER data field to data field. Plus your naming scheme isn't conducive to understanding the relationships.

 

[yakjockey]

You misread me. I said I'm very familiar with many, many programs but not very familiar with Access. I know what a Wizard is, but I wasn't sure which one you were referring to in your comments.

Having said this, I do appreciate your assistance.

I was putting together a simple example for some students and ran into this "text book" type of problem. I'm sure its easily solvable. Like all things, its always "obvious" once you know how!

Please hang in there for me.

Thanks.

 

[yakjockey]

Yes, in a real world situation, it's possible for one owner to own multiple vehicles. This was supposed to be a simple example to demonstrate some of the features of Access to students.

In a real situation, I would probably use multiple join tables and other techniques; somewhat as would be done in an order entry system with multiple items for the same order.

 

[yakjockey]

Pat, this is getting out of hand. I deliberately used different names in the fields to demonstrate that they don't have to be the same in two tables, BUT I emphasized to the students I was addressing that having them the same helps understand the relationships...just as you correctly stated. Furthermore, if I accidentally set up a relationship between two fields, and not a primary key and a foreign key, it was unintentional and probably due to haste. If you have a understandable explanation of how to do what I want to do, I'd appreciate it. But if you want to lecture me, let's call it a day (or night). I'm very knowledgeable but just not with Access's procedures and such. I can always search books and other sources for a solution. I just thought this would be quicker, but it's turning out to be otherwise!!!!

 

[yakjockey]

One more point. If you look at the snap-shot of the relationships, you'll note that the IDs in both the COLOR and MAKE tables are not their primary keys. I just left these fields there to illustrate autonumbering. My relationships are between primary keys and corresponding fields in the VEHICLE table, as they should be.

 

[Pat Hartman]

I'm not sure what you are trying to show to your students but the modified database is a better example since the schema is normalized.

I created two forms. One for vehicles and one for owners. The owner form has a listbox that lists the cars he owns. You would modify the vehicles table to assign a vhicle to an owner. then if you open the owners form, you'll see the additional vehicle.

I modified the schema to be a correct 1-many relationship but in real life I would implement this as many-many with a junction table. That would allow me to have a subform on the owners table where I could assign cars to them. I think that solves the problem you were talking about.

 

[Pat Hartman]

The only reason to have an autonumber in a table is as a primary key which is why I changed all the tables to use the autonumber as the PK. I didn't notice that you had made the other columns the primary keys. It looked like the typical rookie mistake of joining on text field to text field and bypassing the PK. If you want to make look up tables with text values, that will work but having the autonumber confuses the issue.

 

[Simon_MT]

Firstly, Lookup are bad in Tables, they are only needed for Data Entry.

Vehicle with Make and Color is superfluous. It would be btter to have a table for each Vehicle and be able to add the Make Model and Color

You need to create a Owner Table with the Name Address and other details
Then another table with the actual vehicle including plate / registration details.

Personally using the actual Color or Make is preferrable because it is descriptive rather than the ColourID or MakeID.

Simon

 

[yakjockey]

Thanks for your comments. I put it together very quickly, in front of the class, and wanted to emphasize some other issues which it did. But I would never use the type of structure in a real life situation. It was generous of you to modify it in a much better fashion without beating me up too much! I guess I deserved it a little for not taking more time to do it better. Thanks again.

 

[yakjockey]

Simon,

Thank you, too, for your participation in this discussion. I agree with much of what you said. If you review my comments to Pat, you'll understand why it was set up as poorly as it was. I have since corrected everything, using Pat's revised database as a starting point.

If nothing else, I think I'll avoid quickly putting together these types of things in front of a class. Despite my experience, Murphy's Law always prevails. I should have known better. Shame on me!