Combo Box Updates One of Four Fields

[Kenny G]

Access 2003

I have a form and there is a combo box named cboRecordSeriesItemNumber it is
based on a query "qRecordSeriesSource" from two tables.

There are five fields CategoryID as =cboRecordSeriesItemNumber.Column(1),
CategorySection as =cboRecordSeriesItemNumber.Column(2), CategoryName as
=cboRecordSeriesItemNumber.Column(3), RecordSeriesItemNumber, and Record
Series Title as =cboRecordSeriesItemNumber.Column(4).

The combo box is tied to RecordSeriesItemNumber.

When I select a RecordSeriesItem Number the CategoryID field updates the
others don't. In fact they are blank. Any ideas?

Thanks,

 

[Douglas J. Steele]

It looks as though you've set the ControlSource for the text boxes to those
references to the combo box. If that's the case, I'd recommend removing
those ControlSource properties and putting code in the AfterUpdate event of
the combo box to pass the values to the text boxes.

 

[Kenny G]

Doug,

I have changed the controls to bound controls and as I have read I will be
now pushing data into the bound controls.

As I write little code I looked up some code in a developers book.

Private Sub cboRecordSeriesItemNumber_AfterUpdate()
Dim intI As Integer
For intI = 1 To 4
Me.Controls("CategoryID" & intI) = cboRecordSeriesItemNumber.Column(1)
Me.Controls("CategorySection" & intI) =
cboRecordSeriesItemNumber.Column(2)
Me.Controls("CategoryName" & intI) =
cboRecordSeriesItemNumber.Column(3)
Me.Controls("RecordSeriesTitle" & intI) =
cboRecordSeriesItemNumber.Column(4)
Next intI
End Sub

This didn't work. What do you think?

 

[Douglas J. Steele]

You said the names of the controls were CategoryID, CategorySection,
CategoryName and RecordSeriesTitle. Your code below is adding numbers after
those names, so Access isn't going to find the controls.

All you should need is

Private Sub cboRecordSeriesItemNumber_AfterUpdate()
Me.CategoryID = Me.cboRecordSeriesItemNumber.Column(1)
Me.CategorySection = cboRecordSeriesItemNumber.Column(2)
Me.CategoryName = cboRecordSeriesItemNumber.Column(3)
Me.RecordSeriesTitle = cboRecordSeriesItemNumber.Column(4)
End Sub

If you like, you could shorten that to

Private Sub cboRecordSeriesItemNumber_AfterUpdate()
With Me.cboRecordSeriesItemNumber
Me.CategoryID = .Column(1)
Me.CategorySection = .Column(2)
Me.CategoryName = .Column(3)
Me.RecordSeriesTitle = .Column(4)
End With
End Sub

 

[Kenny G]

Doug,

Same problem as before CategoryID shows in the boxes and not the rest of the
text box information in the last three boxes.

 

[John W. Vinson]

When I select a RecordSeriesItem Number the CategoryID field updates the
others don't. In fact they are blank. Any ideas?

One question - are you intentionally storing these fields (which can be looked
up using a Query) redundantly in a second table? If so, why?

John W. Vinson [MVP]

 

[Kenny G]

Yes, by selecting one value from the combo box five fields can be populated.

The query is a one to many relationship. The field from the many side is
selected to populate all else.

The fields from the query are in separate tables which are populated in
another set of forms.

 

[Douglas J. Steele]

Have you set the ColumnCount property correctly for the combo box? Even if
you don't want to see the values when the list is displayed, you still need
to have it set to at least 5 (since you're using upto the 5th column). To
keep the columns from displaying, you need to set their width to 0 in the
ColumnWidths property.

 

[John W. Vinson]

Yes, by selecting one value from the combo box five fields can be populated.

The query is a one to many relationship. The field from the many side is
selected to populate all else.

The fields from the query are in separate tables which are populated in
another set of forms.

It still sounds like you're storing data redundantly: any time you have the
same value (other than a foreign key value) stored in two tables, you're
running the risk of update anomalies. If one of the values in the combo box's
rowsource should need to be deleted, or edited, you now have multiple
instances of that value to track down and correct!

That's fine, if you're aware of the risk and intentionally doing so, and have
procedures in place to prevent such errors. But it's *very* nonstandard and
risky.

John W. Vinson [MVP]

 

[Kenny G]

Thank you.
--
Kenny G

It still sounds like you're storing data redundantly: any time you have the
same value (other than a foreign key value) stored in two tables, you're
running the risk of update anomalies. If one of the values in the combo box's
rowsource should need to be deleted, or edited, you now have multiple
instances of that value to track down and correct!

That's fine, if you're aware of the risk and intentionally doing so, and have
procedures in place to prevent such errors. But it's *very* nonstandard and
risky.

John W. Vinson [MVP]