Software Catalog:
Another Look at Forms
|
Raise your hand if your ever operated a DOS accounting system and had to keep hard copy lists of your General Ledger, Customer, Vendor, etc., etc. account numbers for data entry in related tables in other modules of the system even though it was an " integrated accounting system." (I guess that is why yours truly abandoned DOS applications so quickly after I discovered the power of Windows applications.) I wrote my own general ledger system in Access as soon as I could figure out how to do it. Now we will do some surgery to our Software Table to make it user-friendlier and save some extra work for the developer as well. Recall that the Software table has four fields listing the related Manufacturer ID, Vendor ID, Program ID, and System ID. Incorrect data in any of these fields would make it impossible to pull a complete report for the record with the invalid data. So we will make these four fields lookup fields. Then the data entry person selects from a list of ID Codes (by the name and not the code) in the appropriate table. You can have an empty field in a record unless you designate the field as required, but you will not have an IDCode that does not exist in the linked table. Here's how we'll accomplish the task: Open the Software table in Design view. Step 1: Select the MfgID field so that the arrow appears in the left margin beside the fieldname. Select the Lookup tab in the lower section of the window. Click the down arrow beside the text box to open the drop down list and select Combo box. Leave the default Table/Query in the window that appears. Go to the Row Source field and select the " …" command button to build a new query. The Show Table dialog will open. Step 2: Select Manufacturers. Click the Add button then Close. In the Query Design window that appears drag MfgID to the Field row of the first column, and drag MfgName to the next column, same row. Click the close button of the query window. A dialog will ask if you want to save your changes. Click yes and save the new query as MfgNameQuery the name will be entered as the Row Source. Step 3: Leave the Bound Column as 1. (This is what is stored in the Software table.) Make the column count 2. Make the Column Widths 0" ;1.5" . That is 0" for the first column and 1.5" for the second so that the ID column will not be visible. It is vital for the success of your combo box that the first column be 0" . If both columns are visible both will be entered into the table field and Microsoft Access will not accept this and the user will be blocked from moving on. Repeat steps 1, 2, & 3 for the VenID and SysID fields making appropriate changes to the table names and name fields so that the same kind of linkage will occur. You'll use VenID & VenName for the Vendors table, and SysID and SysName for the Systems table. We will perform some magic of a different kind to deal with the ProgID field. |
|
We will begin by creating a two-part data entry form for the Programs and Software tables. This will tie the data entry for the Programs table to the data entry for the Software table. Whenever you enter a new program you also need to make an entry in the Software table for each copy of the program purchased. In the database window click the Forms tab. Copy the Program Entry / Edit form and name it Program Master in the Paste As dialog. This creates the master-form to hold the new sub-form we will soon create. Open Program Master in design view. Enlarge the window by dragging the lower edge down then drag the border labeled Form Footer down a couple of inches. There will be no need for a form footer, so if there is any form below this border drag the bottom of the form up to this border. Save the changes and minimize the form to get it out of the way for the moment. We will create a form for the Software table next. Press the New button on the Forms page of the database window. Select the Form Wizard in the New Form dialog and select the Software table in the drop down list box. Click OK. In the next dialog of the Form Wizard click the command button displaying the double greater than signs ">>" to select all of the fields. Click Next. Select the radio button for Datasheet. Click Next. The style selection in the next window is irrelevant for a data sheet, so leave the default. Click Next. Name the form Software Sub. Take a look at your new form. It looks just like the data sheet that results when you run a query. Click on the MfgID field and notice that a down arrow appears at the right. There's a combo box built in automatically. This is the result of changing the field in the table to a lookup field. This gives the user a window into the Manufacturers table at the MfgName field. All the user has to do is scroll down to find the correct name and select it. No hard copy lists needed here! Test the VenID and SysID fields. The same will be true for them if there is any data in the underlying tables. Although the ID is all the database system needs to link table data, and it requires much less storage space, the manufacturer name makes much more sense to your human users than the ID code. Enter the code in Listing 6 at the end of this article into the Global module. |
` ************************************************************************
` Checks the ProgID numbers in the appropriate table to calculate a unique trio of digits.
'*************************************************************************
' Declare all variables before using them
|
Dim ThisDB As DATABASE Dim tblRead As Recordset Dim tempIDStr As String Dim tempNumStr As String Dim SQLstr As String Dim RecNum As Integer Dim Num1 As Integer Dim Num2 As Integer Dim RecCount As Integer |
' Database, assumed open ' to hold recordset ' hold test value while searching ' hold the SoftID numbers ' hold SQL statement ' Record Counter for FOR Loop ' position of ";" substring ' & hold value of tempNumStr ' position of " /" substring ' & hold value of tempNumStr ' Record number & total records |
` The SQL statement below extracts the ProgID and SoftID fields from the Software table
' WHERE the ProgID field value matches the value passed to the function from the
' calling form, Software Sub.
' The values are placed in a temporary table, tblRead.
SQLstr = " SELECT DISTINCTROW Software.ProgID," & " Software.SoftID FROM _
Software " & " WHERE (((Software.ProgID)='" & IDStr & " `));"
| Set ThisDB = CurrentDb | ' Keep track of record pointer in recordset |
| If tblRead.BOF Then | ` If there are no records then this is the first |
|
tblRead.Close CalcSoftID = " 0001" Exit Function |
` Close the empty table ` So this will be number 0001. ` No more work to do. |
| If IsNull(tblRead.Fields(" SoftID" )) Then |
| tempNumStr = Format(Num1) |
| Else |
| tempNumStr = tblRead.Fields(" SoftID" ) |
|
End If Num2 = Val(tempNumStr) If Num2 > Num1 Then Num1 = Num2 If RecNum < RecCount Then tblRead.MoveNext |
|
tblRead.Close |
` release memory used by tblRead |
| tempNumStr = " 0" & tempNumStr |
End Listing 6
|
In design view of the detail section of Software Sub select the text box for SoftID field. Click the Data tab of the Property sheet. (If it is not visible double click on the text box.) Type =CalcSoftID([ProgID]) in the space beside Default Value. (See Listing 6 for the CalcSoftID code.) What we will do next is connect the Software Sub form to the Program Master form. If Program Master is still loaded maximize it. If not load the form in design view. Arrange things so that there is about an inch of empty form below the last controls on the form, and move the window to a position that allows access to the Forms page of the database window. Select Software Sub and drag it to the left side of the empty space on your master form. Then use the right middle handle of the resulting object to stretch the object to the right to near the right margin of the form. Select the lower center handle and stretch the form down so that it is about ¾ to 1 inch tall—tall enough for 3 or 4 rows plus a row of top labels. Change to form view using the form icon in the tool bar. See how it looks. Go back to design view and make any needed adjustments. Toggle the form and design views to make adjustments. Save it when you're happy with the appearance. Open Program Master in the database window and enter a program and several copies. ProgID and SoftID should automatically be entered for you in the appropriate fields. Make up some entries just to test the form. Don't forget to double click on the ID code fields of your forms that have the Calculated ID field working for you. Remember that the Systems table is to record software locations. If you have uninstalled software on the shelf add a system named " closet shelf" or whatever so you can catalog this software too. We have named the Program Master and Software Sub so that their relationship would be obvious. You no longer need the Program Entry / Edit, so you can delete it unless you want to keep it for some reason. This is a good time to give your application some exercise to see how it works so far as data entry is concerned. You can use the tips and hints offered here to make the application more user friendly. Look for opportunities and use them. This will help hone your development skills. For example, look for fields related to other tables and make some more lookup fields. When this done at the table level any subsequent forms created will automatically have combo boxes to facilitate the lookups instead of text boxes. |
|
Return to Beginning Continue Previous |
|
This document authored by Pat Tyler Copyright July 1997 |