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

This is a continuation of Microsoft Access - Combo Box to Text, Lesson 1. We will complete this lesson using Microsoft Access 2003. Please note, if the description doesn't automatically populate the TprNo, Pno and Pm fields, please enable ActiveX.

Tasks Two (Query Creation)

Step1: Go to Create >>> Query Design View. In the Show Table pop-up screen, add tblMain.

Step2: Highlight all the fields in the Table box omitting the asterisk and drop in the first field box at the bottom of your screen. This will display
field names, MainIDNo, TprNo, Pno, Desc and Pm in their own columns. Check marks should appear in the Show row for each field name.

Step3: Choose Sort Ascending under the Desc column.

Step4: Switch to Datasheet View to verify that the data from tblMain appears sorted by the Desc.

Step5: Close the query and name it qryDesc.

Step6: This complete requirement R6.

Tasks Three (Form Creation)

Step1: Open a Blank Form in Design View

Step2: Update the Forms Properties to the following:
Only change the properties listed below. The rest can remain at the default or will be modified as the
form is adjusted.

Format tab:
Caption: Order Query with Criteria from Combo Boxes
Default View: Single Form
Allow Form View: Yes (Allow Datasheet, PivotTable and PivotChart Views should indicate "No")
Record Selector: No (Prevents user from selecting a particular record on the form)
Navigation Buttons: No (Omits the navigation buttons on the form)

Data tab:
Record Source: Choose tblComments from the drop down.
(This indicates the table that will store the information from the form)
Allow Additions: Yes (Users will be able to add records to table, tblComments)
Data Entry: Yes
Allow Deletions: No (Prevents user from deleting a record from the table using the form)
Allow Edits: No (Prevents user from editing data from the table from the form)
Record locks: Edited record.

Step3: Close the Forms Properties window.

Step4: Add a header and footer to the form.

Step5: Drop the Label object into the header area. Type "ABC Corporation - Comments Form." Resize the box and move it to desired location. Open the Label properties box and change the name property to lblHeader.

Step6: Add seven text box objects to the form (See picture for an example.) Label them: Record No, Tpr No, Project Number, Project Manager, Comments (make this one larger to input more data), DateCreated and TimeCreated. Leave some room at the top of the form to add the combo box for the description later.

Step7: Update the listed properties for each text box object starting with Record No.

Object Property
Record No Name (txtRecord), Control Source (RecordNo)
Tpr No Name (txtTpr), Control Source (TprNo), Locked (Yes)
Project Number Name (txtPno), Control Source (Pno), Locked (Yes)
Project Manager Name (txtPm), Control Source(Pm), Locked (Yes)
Comments Name (txtComments), Control Source (Comments)
Date Created Name (txtCreationDate), Control Source (DateCreated), Locked (Yes)
Time Created Name (txtCreationTime), Control Source (TimeCreated), Locked (Yes)

Step8: Add the combo box right under the Project Number at the top of the form. Choose the "I want the combo box to look up the values in a table or query. Click "Next" and then choose the Queries radio button. Click "Next" again when the qryDesc is highlighted. Move only the Desc field to the Selected Fields side of the window. Sort by Desc. After advancing to the next screen, it should show the information from the query. Adjust the Desc box to the desired size. Choose to store the value in Desc. Click "Next". Name the combo box, cmboDesc. Click Finish."

Step9: Bring up the properties for cmboDesc. Change the Column Count to 5, the Column Width to
0";0";0";1";0" and the Row Source to qryDesc and, set Allow Value List Edit to "No."

Step10: Stay in the cmboDesc properties tab and add Code events to the following properties:
After Update

Private Sub cmboDESC_AfterUpdate()
Me!txtTprNo = Me!cmboDesc.Column(1)
Me!txtPno = Me!cmboDesc.Column(2)
Me!txtPM = Me!cmboDesc.Column(4)
End Sub

On Exit

Private Sub cmboDESC_Exit(Cancel As Integer)
Me!cmboDESC = Me!cmboDESC.Column(3)
End Sub

Step11: Add a Button under the Comments text box. On the Command Button Wizard, choose Catergories:
(Records Operations) and Actions (Add New Record)

Step12: Click "Next" and type "Add Record" in the text box. Move to the next screen.

Step13: Name the button, "btnAddRecord" and click the "Finish" button.

Step14: Right click on the button and open the Properties window.

Step15: Click on the Event tab

Step16: Open the Code Expression Builder for the On Mouse Up property

Step17: Add the following text between Private Sub and End Sub
Me!txtCreationDate = Date
Me!txtCreationTime = Time()

Step18: Save the Form

Step19: Switch to Form View to test your form.

Step20: To test the form, choose a description. This should automatically populate the TPR No, Project No and Project Manager fields. Type text in the Comments area and hit the Add Record button. The date and time fields are added when the button is released.

You have completed Microsoft Access Combo Box to Text, Lesson 2.