Combo box to fill text boxes..

[George]

A form has one combo box and 3 text boxes. This form has the Record
Source set to a table containing employee scoring information. The
combo box contains employee ID numbers. Based on the selection from the
combo box the three text boxes will display the employee name, employee
area, and an evaluation score.

Currently the combo box has a Row Source:
SELECT ScoresTbl.EmpNum, ScoresTbl.EmpName, ScoresTbl.EmpArea,
ScoresTbl.OverAllEval FROM ScoresTbl ORDER BY ScoresTbl.EmpNum;

Once the EmpNum is selected or an AfterUpdate event on the combo box,
then the appropriate text boxes are filled with the matching
information. Unfortunately, this somehow modifies the ScoresTbl.

How does the ScoresTbl get modified?

Is there a way to perform this task by using a query? I tried setting
the Record Source to a query that contained the four needed fields from
the ScoreTbl, but was unsure how to get the information back to the text
boxes in the form.

Any help is greatly appreciated.

Thank you,
George

 

[Daryl S]

George -

If you are using the combo box to select a value, make sure it is not bound
to the recordsource of the form. If it is bound to the form, then you will
over-write the current form record with what you select. Also, if you are
populating the text boxes from the combo box, then these values will also
update the record on the form.

If you are using a combo box to select a record to display, then you don't
want the combo box to be bound. The AfterUpdate event should then add a
filter to the current form to restrict which records are being shown, rather
than updating the data in the field.

 

[George]

George -

If you are using the combo box to select a value, make sure it is not bound
to the recordsource of the form. If it is bound to the form, then you will
over-write the current form record with what you select. Also, if you are
populating the text boxes from the combo box, then these values will also
update the record on the form.

If you are using a combo box to select a record to display, then you don't
want the combo box to be bound. The AfterUpdate event should then add a
filter to the current form to restrict which records are being shown, rather
than updating the data in the field.

I just finished the "Access 97 Bible", what other material would be
recommended for more advanced reference?

Thenks

 

[John W. Vinson]

I just finished the "Access 97 Bible", what other material would be
recommended for more advanced reference?

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[KenSheridan via AccessMonster.com]

George:

There are two ways you can handle this:

1 Using a bound form.

1.1 Using a bound form as you are at the moment make the combo box unbound
by deleting its ControlSource property, which is presumably EmpNum at present,
which is why the data in the underlying table is changing.

1.2 Change the Name property of the combo box if necessary so its not the
same name as the field, e.g. to cboEmployee

1.3 The ControlSource properties of the three text boxes should be EmpName,
EmpArea and OverAllEval respectively.

1.4 In the AfterUpdate event procedure of the combo box put the following
code to navigate to the selected employee:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "EmpNum = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

This assumes EmpNum is a number data type, If it’s a text data type amend
the code to:

.FindFirst "EmpNum = """ & ctrl & """"

If you are unfamiliar with entering code in event procedures you do this by
selecting the control in form design view and opening its properties sheet if
its not already open. Then select the After Update event property in the
properties sheet. Click on the 'build' button; that's the one on the right
with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines already
in place. Enter the lines of code between these two existing lines.

1.5 In the Form's AfterUpdate event procedure put:

Me.cboEmployee.Requery
Me.cboEmployee = Me.EmpNum

This will update the combo box's list if you change any of the data in the
text boxes or add a new record via the form.

1.6 An added refinement is to put the following in the form's Current event
procedure:

Me.cboEmployee = Me.EmpNum

This will keep the combo box in sync with the form's current record if you
navigate to another record via the navigation bar etc.


2 Using an unbound form

2.1 Delete the form's RecordSource property so its unbound.

2.2 Keep the combo box exactly as above.

2.3 Change the ControlSource properties of the three text boxes to:

=cboEmployee.Column(1)
=cboEmployee.Column(2)
=cboEmployee.Column(3)

The Column property is zero-based, so Column(1) is the second column of the
combo box's RowSource and so on.

The big difference between these two approaches is that in the case of the
first, using a bound form you can edit the data in the text boxes or even add
a new record, and therefore update the data in the underlying table, whereas
in the second you can only view the data and not edit it or add a new record.

Ken Sheridan
Stafford, England