Filling multiple text boxes from a 3 column combobox

[Guest]

I am looking for a way that I can fill 3 bound textboxes from the same combo
box. The project is a Bill Of Materials that has an independant table. The
combo box has 3 columns in it; MaterialID, MaterialDescription,
MaterialTaxRate and MaterialCost. What I am trying to do is allow the
Material Description populate the fields in bound textbos to save on typing.
Example: If I have already used a 3/4" widget $3.40 at %6 in another project
on the Bill Of Material, I want to be able to populate the combobox from the
Materials Table and then select it into another Bill for a new project. Once
I have selected the Description, the the [TaxRate] and [Cost] cost fields are
filled in from the other columns of the combobox. In addition, if the
Material is not listed in the table(not in the cbo), it can be added and then
appear in the combo box for the next project...I feel as though I have done a
lousy job describing this?

 

[Douglas J. Steele]

To get the other text, you need to put code in the combobox's AfterUpdate
event.

You can refer to the various fields of the currently selected row in the
combobox as Me.MyCombo.Column(0), Me.MyCombo.Column(1), etc.

To be able to add new items that aren't in the list, you need to set the
combobox's Limit To List property to True, then put code in its NotInList
event. There's a sample at http://www.mvps.org/access/forms/frm0015.htm at
"The Access Web"

 

[Guest]

DJS,
Your advise made sense to me so I added the following code to the
AfterUpdate event:

Private Sub cboMaterialAddSelect_AfterUpdate()
Dim cboDescription As String
Dim cboPrice As Currency
Dim cboTax As Currency

'Loads the 3 cbo- variables from cboMaterialAddSelect column values.

cboDescription = Me.cboMaterialAddSelect.Column(1)
cboPrice = Me.cboMaterialAddSelect.Column(2)
cboTax = Me.cboMaterialAddSelect.Column(3)

'Moves Focus to each Textbox and places cbo- variables in the values.
Me!MaterialDescription.SetFocus
Me!MaterialDescription.Value = cboDescription
Me!MaterialUnitPrice.SetFocus
Me!MaterialUnitPrice.Value = cboPrice
Me!MaterialSalesTax.SetFocus
Me!MaterialSalesTax.Value = cboTax


End Sub

I put a break point at the Private Sub line and before it reached the event
I got the following error message:

Visual Basic for Applications (VBA) encountered a problem while attempting
to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base
article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a
module.
Expressions can resolve a user-defined function only if the function is
declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8
update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function
properly when security is set to Medium or High. To obtain the latest version
of Microsoft Jet, go to Windows Update.

Bassically, the code I wrote never began to run...I have checked and double
check the obvious cases for theis error but...well that's why I am back here.

Jack

To get the other text, you need to put code in the combobox's AfterUpdate
event.

You can refer to the various fields of the currently selected row in the
combobox as Me.MyCombo.Column(0), Me.MyCombo.Column(1), etc.

To be able to add new items that aren't in the list, you need to set the
combobox's Limit To List property to True, then put code in its NotInList
event. There's a sample at http://www.mvps.org/access/forms/frm0015.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am looking for a way that I can fill 3 bound textboxes from the same
combo
box. The project is a Bill Of Materials that has an independant table.
The
combo box has 3 columns in it; MaterialID, MaterialDescription,
MaterialTaxRate and MaterialCost. What I am trying to do is allow the
Material Description populate the fields in bound textbos to save on
typing.
Example: If I have already used a 3/4" widget $3.40 at %6 in another
project
on the Bill Of Material, I want to be able to populate the combobox from
the
Materials Table and then select it into another Bill for a new project.
Once
I have selected the Description, the the [TaxRate] and [Cost] cost fields
are
filled in from the other columns of the combobox. In addition, if the
Material is not listed in the table(not in the cbo), it can be added and
then
appear in the combo box for the next project...I feel as though I have
done a
lousy job describing this?

 

[Douglas J. Steele]

There's no reason for the SetFocus, and no real need to store the values in
intermediary variables.

See whether this works:

Private Sub cboMaterialAddSelect_AfterUpdate()

With Me!cboMaterialAddSelect
Me!MaterialDescription = .Column(1)
Me!MaterialUnitPrice = .Column(2)
Me!MaterialSalesTax = .Column(3)
End With

End Sub

However, that doesn't solve why the code's not being executed in the first
place, does it?

Try compiling your application (when you're in the VB Editor, it's the first
choice on the Debug menu) Hopefully that will indicate what exactly is the
problem, and you'll be able to correct it.