Hi Kevin
As Nicolaas has said, you can fill other fields by using a multi-column
combo-box to supply the values.
The Column value of a combo-box is "Zero-based" (i.e. 0,1,2,3...) with 0
being the leftmost column.
I also think that you'll want to use the AfterUpdate event, rather than the
OnChange event
I just so happen to have a relevant code sample from my parts inventory app
to illustrate: :-)
(This code is from a subform named "sbfTransactions" which is on a main form
called "frmVehicleRepairs")
I use this combo-box to select a partnumber, record the transaction, as well
as offer the user a choice of pricing methods...
i.e.
Cost + x%
or
Retail - x%
------------------------------------
**************************************
Private Sub cboPartNumber_AfterUpdate()
Dim ctl As Control
Set ctl = Me.cboPartNumber
With ctl
' Column(0) contains "PartID" ... which we don't need here.
Me![PartNumber] = .Column(1)
Me![Line] = .Column(2)
Me![Description] = .Column(3)
Me![PartRetail] = Nz(.Column(4), 0)
Me![PartCost] = Nz(.Column(5), 0)
Me![PartCore] = Nz(.Column(6), 0)
Me![SupplierID] = .Column(7)
End With
If IsLoaded("frmVehicleRepairs") Then 'I use this subform elsewhere. This
code is only used with "frmVehicleRepairs" to record a parts sale.
Dim MyPrice
Dim PlusOrMinus
Dim MyPcnt
Dim BasePrice
MyPrice = Parent![cboSheet] 'A value-list combo-box contain the values
"Cost" and "Retail"
PlusOrMinus = Parent![PlusMinus] & "1" 'A value-list combo-box contain
the values "+" and "-" ...
' this variable returns either "+1" or "-1" for the purposes of
calculation.
MyPcnt = Parent![Pcnt] ' a textbox that the user enters a percentage
value into... includes a simple method to correct invalid entries... see
below
BasePrice = CCur(Me.Controls("Part" & MyPrice)) ' ie "PartCost" or
"PartRetail"
Me![SellPrice] = BasePrice + (BasePrice * (PlusOrMinus * MyPcnt))
Me.TransType = "Parts Sale"
End If
Me.TransQty.SetFocus
Set ctl = Nothing
End Sub
**************************************
The code below is on the main form ("frmVehicleRepairs")
Private Sub Pcnt_AfterUpdate()
' corrects entries from "7" to "0.07" IF they are entered that way.
If Me!Pcnt > 1 Then
Me!Pcnt = Me!Pcnt / 100
End If
End Sub
**************************************
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
hello
i am pretty new at using access and having a little trouble
i have a combobox containing a list of parts.
the user slects a part from the list, i then want the part price to
show in a text box(done this), i also want the part number to appear
in anoter text box(cant do this)
the code below works great for one text box but i cant get it to
display the second value.
Any ideas where i going wrong?
Private Sub Component_Change()
If Not IsNull(Component.Column(1)) Then
[Piece Price] = Component.Column(1)
End Sub
thanks in advance
kevin
