Access TextBox and ComboBox Records

  • Thread starter Thread starter daksport00
  • Start date Start date
D

daksport00

I am pretty sure that this is something that can be done. I can almost
remember doing this a few years ago, but its been that long since I've
done access programming.

Here is what I am looking to do:

I have a form with a combobox. I would like to be able to select a
product model from the combobox (I have this setup already, because it
was straightforward and simple).

Once I select the model, I would like a group of labela and textboxes
to display the different properties of that model, as they appera in
the table.

I.E> Dimensions, Price, Weight, etc.

This information is in the same table as the Model Number.

On a seperate section of the form, I want to show a table (or sub-form
as that may be necessary), which will take the model number and use it
as a search string in the Drawings table, returning the Drawings that
are applicable to the given model. This I wouls imagine would be done
with a query.

Any suggestions would be greatly appreciated.

Thanks

-Dave
 
Easiest way is to include all of the fields of interests in the rowsource
for your combo box (you don't have to make them visible), and then put code
in the combo box's AfterUpdate event to transfer values to the text boxes
once you've selected a row in the combo box.

Something like:

Private Sub MyCombo_AfterUpdate()

Me.TextBox1 = Me.MyCombo.Column(1)
Me.TextBox2 = Me.MyCombo.Column(2)
Me.TextBox3 = Me.MyCombo.Column(3)

End Sub

Note that the Column collection starts numbering at 0. What I've shown above
copies the values from the 2nd, 3rd and 4th columns of the selected row.
 
For the Model detail, include the other fields in the RowSource query
statement. To display one in a textbox, set its ControlSource to
=YourComboBox.Column(x), where x is the column number, starting with 0.

To display records from the Drawings table, create a subform with the fields
you wish to display. When you open the form, this would show the entire
table of drawings. To filter it by those whose names contain the ModelNumber
from the combo box, include the following code in the CB's AfterUpdate event:

Dim strSQL As String
strSQL = "SELECT Drawings.DrawingName " & _
"FROM Drawings " & _
"WHERE Drawings.DrawingName Like '*" & Me.YourComboBox & "*'"
With Me.YourSubform
.Form.RecordSource = strSQL
.Requery
End With

Change the table, field, subform and control names as appropriate.

Hope that helps.
Sprinks
 
I am pretty sure that this is something that can be done. I can almost
remember doing this a few years ago, but its been that long since I've
done access programming.

Here is what I am looking to do:

I have a form with a combobox. I would like to be able to select a
product model from the combobox (I have this setup already, because it
was straightforward and simple).

Once I select the model, I would like a group of labela and textboxes
to display the different properties of that model, as they appera in
the table.

I.E> Dimensions, Price, Weight, etc.

This information is in the same table as the Model Number.
Click to expand...

Simply include these fields in the Query upon which the combo box is
based. You can use the ColumnWidths property of the combo to set the
display width of these fields to zero.

Then on the Form put textboxes with control sources

=comboboxname.Column(n)

where (n) is the zero based index of the field you want to see - that
is, if the price is in the third field in the query, use (2).

John W. Vinson[MVP]
 
Ok Sprinks, I did what you showed here. The textboxes work perfectly
right now, thanks for that.
However, with the subform, when I select a new model number from the
combobox, The Model Number field of the subform updates correctly.
However, there are other fields in the subform. These fields are the
same as what is in the table that this subform is tied to. Do I need
to write the same type of SQL Update for all of the fields??

-Dave
 
Back
Top