Microsoft Access - Advance Training: Combo Box to Text Box - Lesson 1

This is a step by step instruction on how to create a Microsoft Access Form that allow users to choose a description from a Combo box and it will automatically populate text fields based on that selection. This can be accomplished in most version of Microsoft Access but I will demonstrate using version 2003.

I must warn that this for advance users of the software. If you're a novice, I recommend that you become familiar with the software before taking this training because I may omit basic tasks.

In order to demonstrate why a Combo box to Text field may be needed, I created a fictitious company. The ABC Corporation requested a project to create a Microsoft Access form that will populate a database with user comments on all projects. The charter indicates that the form should be user-friendly.

The first thing should be done is to break the work into smaller tasks. The tech lead created the following requirements.

Requirements

R1: Create two tables, tblMain and tblComments. The tblMain table must include all of the main fields, such as, TprNo, Pno, Desc and Pm, whereas, the tblComments table should include all of tblMain fields plus Comments, DateCreated and TimeCreated fields.

R2: Both tables need fields (MainIDNo and RecordNo) to automatically generate numbers. These should be the primary keys for each table.

R3: The Desc field should be a maximum of 255 characters in both tables.

R4: All fields should be required in both tables.

R5: The Data Type for most of the field names should be set to "Text" with the exception of MainIDNo and RecordNo; they should be set to AutoNumber. The Comments field should be set to Memo. DateCreated and TimeCreated fields' data type is "Date/Time."

R6: A query is needed of the tblMain data with the Desc field sorted in ascending order.

R7: Create a form with the RecordNo (text box), TprNo (text box), Pno (text box), Pm (text box), Desc (Combo box), Comments (text box), DateCreated (text box) and TimeCreated (text box).

R8: On the form, the user shouldn't be able to select text boxes, RecordNo, TprNo, Pno, Pm, DateCreated and TimeCreated.

R9: The DateCreated and TimeCreated boxes should not be visible for the user to see. The only purpose is to record the date and time the comment was posted to the table, tblComments.

R10: The user shouldn't be allowed to edit or delete records from the form.

R11: The form should advance to a new record once all the necessary information has been completed.

Armed with the above requirements, you are now ready to work on the tasks.

Tasks One (Tables Creation)

Step1: Launch Microsoft Access 2003 and open a New Blank Database. Save the Database called "Project_Comments."

Step2: Switch the database to Design View. This may require the table name to be saved. Name the first table, tblMain.

Step3: Add the following Field Name and Data Types in the order below:

MainIDNo / AutoNumber
TprNo / Text
Pno / Text
Desc / Text
Pm / Text

Step4: Set the MainIDNo to the primary key.

Step5: Go into the properties for each field with the exception of MainIDNo and set required to "yes." The field size should indicate "10" for the TprNo and Pno, "255" for Desc and "100" for Pm.

STEP6: Switch to Datasheet View. You will be prompted to save your table.

STEP7: Input the following data in the fields: (The "/" between the characters represent a new field)

11111 / P1111 / Car Repair Shop Project / John Doe
22222 / P2222 / Project Orange Juice / Jane Doe
33333 / P3333 / Customer Project / Sally Doe

STEP8: Save and close out the table.

STEP9: Create a new table in Design View and save it as TblComments.

Step10: Add the following Field Name and Data Types in the order below:

RecordNo / AutoNumber
TprNo / Text
Pno / Text
Desc / Text
Pm / Text
Comments /: Memo
DateCreated / Date/Time
TimeCreated / Date/Time

Step11: Set the RecordNo to the primary key.

Step12: Go into the properties for each field with the exception of MainIDNo and set required to "yes." The field size should indicate "10" for the TprNo and Pno, "255" for Desc. The PM size should be set to "100."

Step13: Close out of the table. There isn't a need to input information. This will be added using the form.

Step14: This completes requirements, R1 through R5.

You have completed Microsoft Access - Advance Training: Combo Box to Text Box - Lesson 1. In the next lesson, we will setup a query and the actual form.