How to create a Form that populates data from a table

[CheapWine]

I'm super new, treat me like so. (In advance, thank you)

I'm creating a database for our bus information. So the form will have things like Bus #, VIN, Status (active/inactive), tire size, etc.

I want to create a form that generates with assigned blank boxes and then populates information when you enter a bus # based on the bus you chose. From there I would like to know how to lock of unlock the boxes for editing so that bus information can be updated. But say, the VIN for example would never change, so that should be locked.

I don't know how to create a decent form and at this point I need a good amount of help.

Again, thank you.

[PhilOfWalton]

Welcome to Bytes. When I saw your user name od CheapWINE and the word VIN in your question, I thought this would be a nice thread obout alcohol. Ah well...

We don't normally write databases for people, as we expect them to put some effort in, even if it is wrong, then come back to the forum and say "This is what I've done. This bit isn't working, can you help?"

What you want is reasonably simple, so let us know where you have got to. Lets have a look at your tables.

Phil

[twinnyfo]

And a look at your form might be helpful, too. But since you are creating a DB, getting the tables right first is most important.

Thanks!

[CheapWine]

Here is my progress

The main table has all the information the other tables have. However, I was starting to change the tables and build relationships so that the tables would feed information into each other. There are some hiccups there needless to say. I'm working on that.

The report generates all the data I want. It's in great shape right now.

The form, however, is the big pain.
First, I can't just select a bus.
Second, I can't select a bus and have it fill in the data based on my tables.
Third, I don't know how to allow or disallow edits.

I hope this helps and gives more insight into my project. Thank you all.

[PhilOfWalton]

Good start, so let's see what we can do to make life easier.

I know nothing about buses, so please correct me when I am wrong.

Firstly, it always helps with a database to use drop down Combo Boxes to enter data, because if they are entered by hand, Typos frequently occur and Bluebird is not the same as Blue bird. So it may pay to have a TblManufacturer s and a TblModels, and on your form both would be entered using Combo Boxes, but the models would be limited to only those models produced by that manufacturer.
You've got it spot on with your Bus Status Combo and your Location Combo. I presume you already have a TblLocations.

These tables should look like this

TblManufacturers
    ManufacturerID      Autoumber    Primary Key
    ManufacturerName    Text         Indexed No Duplicates)


TblModels
    ModelID             Autoumber    Primary Key
    ModelName           Text         Indexed (No Duplicates)
    ManufacturerID      Long         Foreign Key to link with 
                                     TblManufacturers

TblLocations
    LocationID          Autoumber    Primary Key
    Location            Text         Indexed (No Duplicates)

Now in your TblBusses, we get a similar set up to the TblModels

TblBusses
    BusID              AutoNumber    Primary Key
    BusNo              Number or Text    Indexed (No Duplicates)
    ManufacturerID     Long         Foreign Key to link with 
                                    TblManufacturers
    ModelID            Long    Foreign Key to link with 
                               TblModels
    ModelYear          Single
    BusStatusID        Long    Foreign Key to link with 
                               TblBusStatus
    LocationID         Long    Foreign Key to link with 
                               TblLocations
    Plus all the other fields.

Note that the names of all the fields are clear and you know exactly what sort information is held in them. Note also there are no spaces in the field name, and I suggest you remove spaces in all Table, Query, Form & Report Names. Note also that the "#" sign is used to indicate a date, so I avoid using them instead use "No"

Back to your form. Basically you can use a Combo to select the bus number.

Form design is a matter of personal taste. Personally, I use Arial Black font size 12 to make reading easy. 99% of the time, Labels are either to the left of the control or on top of it. I normally use Bold font for labels. Those to the left of the control are always aligned right, so you can see what label aligns with which control. Those labels above the controls are aligned left over text, right over numbers, and often centred over dates.
I like to keep the control size something like the size of the data, so your Bus Capacity box should only be about 1/2" wide.

I think, if you fall in with this scheme, please send an image of your Relationship page, with all the fields in all the tables showing, and possibly your revised form in design view.

To answer the last part of your question, there is a property called "Locked" on many types of controls. If that is set to "Yes" then the value can't be altered.

Phil

[twinnyfo]

Also, once you select the Bus, you can use that value to filter the form by that bus number. I believe this fulfills the “auto-populating” feature you are after.