I need to create something in my form where I can select a
technician's tech id (a unique number) and the form will display the
techs name in a non-editable field. This would allow me to avoid user
entry error since we have techs with duplicate first/last names.
You can click on the text boxes with the names, open the Properties
sheet, click the Data tab, and set the Locked property to Yes. That
will disable editing of the names on your form.
Or, you could also do the following, which is a long-winded version of
what Mr. Vinson recommended:
You can add a combo box to your form to select the technician's ID and
at the same time automatically fill in the names with the Combo Box's
Row Source Query. All it takes is some unbound text boxes and just a
little bit of VBA. First, if you do not already have such a combo
box, use the toolbox and place an unbound combo box on the form. In
the properties sheet name it cboTechID. Use a SQL statement or Query
as the Row Source and make sure that it displays the names you want as
well as the ID. It could be as simple as this:
SELECT TMyTechID, FirstName, LastName
FROM Mytablename
Of course, you will use the field names you already have in your table
whick may differ from my example.
Next, use the toolbox again to add two unbound text boxes. Click on
the first textbox and then open the Properties sheet. In the
Properties sheet click the Other tab and enter txtFirstName for the
Name. Do the same for the other textbox and name it txtLastName.
Click on the Combo Box and open the Properties sheet. Select the
Format Tab. For Column Count enter 3, Column Widths could be
something like 0.7";1";1" or make it simple and just make it 1";1";1"
Next, set the list Rows to 20 (this is optional)
Then (and this is not optional) for List Width enter 3".
Next, click the Event tab in the Properties sheet. Double click to
the right of the line that says After Update and then select Code
Builder. You should see the following:
Private Sub cboTechID_AfterUpdate()
End Sub
Between these two lines add the following code which will use the
combo box's query or SQL Statement to fill the two text boxes with the
names.
Add:
Me.txtFirstName = Me.cboTechID.Column(1)
Me.txtLastName = Me.cboTechID.Column(2)
Your finished VBA Sub should look like this:
Private Sub cboTechID_AfterUpdate()
Me.txtFirstName = Me.cboTechID.Column(1)
Me.txtLastName = Me.cboTechID.Column(2)
End Sub
Next look at the top of the VBA window, find cboTechID and click on
the arrow. Select Form from the list. In the box to the right of
that one click on the arrow and select Current.
You should then see this in the VBA window:
Private Sub Form_Current()
End Sub
Between these two lines add the following:
Me.cboTechID.Requery
Me.txtFirstName = Me.cboTechID.Column(1) (you can copy and paste
these)
Me.txtLastName = Me.cboTechID.Column(2)
Your finished sub should look like this:'
Private Sub Form_Current()
Me.cboTechID.Requery
Me.txtFirstName = Me.cboTechID.Column(1)
Me.txtLastName = Me.cboTechID.Column(2)
End Sub
Just one more thing to do. That is to make the text boxes unable to
be edited. Just click on one of your text boxes, open the Properties
sheet, and select the Data Tab. At the bottom where it reads Locked
change the setting from No to Yes. Do this also with the other text
box. Now no one can edit the names on the form.
Now when you select the ID in your combo box you should also see the
names appear in the text boxes.
I hope this helps.
Hunter57
http://churchmanagementsoftware.googlepages.com/home