Skip to content

Making sure you get your table relationships set up correctly….then build the data entry form

April 20, 2015

Staring MS access, a lady who knows enough to be dangerous and a dude, that’s me, who has been here before.

My customer request sounded like this “Hi James, we have some stuff we want to enter for our kits.  We have lots of things that go into the kits and want to be able to report on them from the DB.”

After a little more prodding from me, I gleaned this from my customer.

Some of the items that go in the kit could go into multiple kits and some of the items in a kit come from more than one vendor.

My data normalization skills kicked in and I spend many hrs looking at this to ensure I got it right.  The kits were already in the DB, but not the detail of the kit items or any information about vendors who supply the kit items.

I ended up creating three new tables in the DB.

kit_items

assoc_kit_items_kits

vendors

New tables and their relationships

kit_items

The assoc table would be the table to keep track of the details of the kit_item.

After a bit of dabling around with VBA and trying to handle the requirement exclusively in VBA – I punted and allowed the application to do the work that it was designed to do.  In dabbling, I did get to learn more about VBA – breakpoints – watches and how to drill down into a data structure to view a value.  BUT – it was way to much *extra work to do it this way.  WHEN access has lots of native features to handle things like editing and saving data to multiple tables on an edit.

The primary tool I used to *build an entry form for a table and its related data was a form – subform solution.  The kit_itemid would be the linking field between my main form (The item) and its child or linked sub-form, the assoc_kit_kititem.  This classic entry form allows you to enter a parent record, like a kit, while detailing lots of records about that kit, like how many items did it contain, how many vendors etc.

Setting up the form was not trivial – as the subform can be a little tricky.  But, patience and attention to detail and Stackoverflow and even the MS help, specifically on the subform edits and properties, was enough to help me build this form.

Main item data entry form with sub-form

kit_subform

The outer navigation bar is for the primary record, while the inner sub form shows details about the selected item.  You would use the outer record nav while entering new items and the inner nav while adding details about the item.  I ended up adding drop down controls to show existing kits that the item would belong to and for the vendors.

I also added a very simple data entry form for the item vendor.  Once I had tables understood and created and the entry forms working – I added a little navigation from the home form.

Navigation menu for the kit items

estec naviation

I added a new button to the main form, which opens the sub form that you see on top of the main menu.  I also added a view of the kit items being entered, not a report, but a form technically, with a listbox on it that displays a query result set.

Display Form – using listbox to show query result

estec item details

and finally, I added a form that shows the total number of times the kits and their items were booked each year.

Display Form – using listbox to show query result – again!

estec kits out

Advertisements

From → MS Access

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: