Help - Spreadsheet brain in a Database world

[schedulermpm]

Having been recently dubbed "Access guru" for our operation ( on the premise that I could pronounce Access , and knew it was a database program ) I am now confronted with a form problem .

I need to be able to set up a form that will allow users to input data from a packing list . The packing list has header data that contains part number , PO, etc . The detail part of the list is the serial number and quantity information for the items in the shipment.

The packing slip will only have on part number / PO / etc but may contain many serial numbers. I need the information in the packing slip to populate a table (s ? ) that identifies each serial number as a unique record.

I have designed a basic form that can handle one serial number at a time but want to be able to enter multiple serial numbers without having to re-enter the header data.

I know that this is probably an easy task and if the data was going to be used in Excel I wouldn't be floundering .

So please take pity on the novice and give me some direction.

All help is appreciated . I tell everyone I know about this forum and have gotten excellent feedback from co-workers who have visited. Thanks again for any assistance.

 

[Andrew Fergus]

Hello and welcome to MrExcel.

I liked your introduction and felt I had to answer. What you really need is a subform arrangement, but before you do that you need to get your tables in order. Apologies for the long post but I figured you might need some specific instructions as to how to do this.

Tables

First things first, use 2 tables, one for the packing list header and one for the detail.

The header table will include all of those bits of information that are usually found on the top of the form, that you have already identified as being the bits of information you are having to re-enter (e.g. packing list number, date, third party etc.). I would expect the packing list number would be the primary key for this table.

The detail table should contain the bits of information found in the body of the packing list - e.g. serial number, quantity etc. BUT you also need to include an additional field in this table called something like 'Packing_List_Link'. This field is NOT the primary key but is used to link the two tables (more on that later). I suggest you use some sort of unique number for each record as the primary key, and in this instance something like an autonumber field may suit. However, if the serial number is unique then you could use that as your primary key in the details table instead.

Relationship

Link the two tables in the Relationships screen (you get to it via menu option Tools -> Relationships). Add the tables using the menu option Relationships -> Show Table. Click and drag the primary key from the header table onto the link field in the details table - make sure you enforce referential integrity & tick cascade update. To learn more about 'normalising a database', have a read of this webpage.

So, how to use this structure in your database?

Queries

Create a query based on the details table to get the data into the sequence you want (make sure you include the key field and the link field). Do the same for the headers. Save the 2 queries. It's good practice to base forms and reports on queries, rather than the tables directly, plus it gives you options.

Forms

Create a form based on the details query - make sure it is in either a tabular or a datasheet layout (when using the new form wizard), make sure the key and link fields are on the form but set the 'Visible' property of the link field to 'No'. Save the details form. {To get into the Visible property, right click the link field in your form design screen -> Properties -> Format -> Visible -> change it to No. If you used a datasheet view then just set the column width to 0}

Create a header form (be sure to include the primary key field) but this time choose the 'columnar layout' for this form. Save the form. While in the design screen of this form, expand the detail section of the form to make room for the subform. Staying in the same screen, shrink your screen size a little so that you can see the details form (in the normal database window) behind the header form. Click and drag the details form from the database window onto the detail section of the header form (while still in design view mode). What you have just created is a form within a form (i.e. a subform).

If the subform wizard pops up then make sure you select the correct fields to link the 2 forms (based on the relationship you created earlier). If the wizard doesn't appear, click on the subform (from within the header form design screen), click View -> Properties -> Data tab -> Link Child field -> set this to the name of the linked field from the subform -> Link Master Field -> set this to the unique key you used on the main part of the form (packing list number perhaps?)

There is a useful site on how to make subform here.

You should get this to work ok and then your co-workers will definitely call you an 'Access guru'.

Tidy Up

To make you look like even more of a guru, you can re-use any data already entered by using append queries so that you don't have to re-key everything. Do the header append query first, then the detail append query - this will stop you violating the referential integrity rule you created in the relationship screen.

If you have any queries, just post a reply into this thread.

Good luck!
Andrew

 

[djl0525]

Andrew, I enjoyed reading your post.

DJ