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

How to create a Form that populates data from a table

P: 2
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.
Feb 14 '19 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,430
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.

Feb 14 '19 #2

Expert Mod 2.5K+
P: 3,284
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.

Feb 14 '19 #3

P: 2
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.
Attached Images
File Type: jpg Table View of Main.jpg (57.4 KB, 9 views)
File Type: png Tables listed.png (8.4 KB, 14 views)
File Type: jpg Report View.jpg (109.5 KB, 13 views)
File Type: jpg Form.jpg (50.2 KB, 11 views)
Feb 15 '19 #4

Expert 100+
P: 1,430
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 TblManufacturers 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
Expand|Select|Wrap|Line Numbers
  1. TblManufacturers
  2.     ManufacturerID      Autoumber    Primary Key
  3.     ManufacturerName    Text         Indexed No Duplicates)
  6. TblModels
  7.     ModelID             Autoumber    Primary Key
  8.     ModelName           Text         Indexed (No Duplicates)
  9.     ManufacturerID      Long         Foreign Key to link with 
  10.                                      TblManufacturers
  12. TblLocations
  13.     LocationID          Autoumber    Primary Key
  14.     Location            Text         Indexed (No Duplicates)
Now in your TblBusses, we get a similar set up to the TblModels
Expand|Select|Wrap|Line Numbers
  1. TblBusses
  2.     BusID              AutoNumber    Primary Key
  3.     BusNo              Number or Text    Indexed (No Duplicates)
  4.     ManufacturerID     Long         Foreign Key to link with 
  5.                                     TblManufacturers
  6.     ModelID            Long    Foreign Key to link with 
  7.                                TblModels
  8.     ModelYear          Single
  9.     BusStatusID        Long    Foreign Key to link with 
  10.                                TblBusStatus
  11.     LocationID         Long    Foreign Key to link with 
  12.                                TblLocations
  13.     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.

Feb 15 '19 #5

Expert Mod 2.5K+
P: 3,284
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.
Feb 16 '19 #6

Post your reply

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