autolookup fields on a form

[Tommy2326]

I'm creating a database in access 2000 to monitor downtime levels. I've
created a form, called 'downtime form', for all the information to be entered
into, this includes the fields 'Fault Code', 'Fault Message' and 'Action'.
The data from the form is to be stored in the table 'Downtime Data'. I have
another table with all the fault information, called 'Fault Codes', the
fields in this are 'Fault Code', 'Fault Message' and 'Action'. I'm trying to
set the form up so that i can enter the fault code and then the fault message
and action will be displayed and stored automatically in the 'downtime form'
and 'downtime data' table.

 

[Al Campagna]

Tommy,
Create a combobox (ex. cboFaultCode) bound to FaultCode to enter the
FaultCode, and include the columns FaultMessage and Action.
Using the AfterUpdate event of cboFaultCode, you can set the values for
FaultMessage and Action.
Private Sub cboFaultCode_AfterUpdate()
Me.FaultMessage = cboFaultCode.Column(1)
Me.Action = cboFaultCode.Column(2)
End Sub
Every time you select a FaultCode, both fields will be updated with the
associated value. (Combo columns are numbered 0, 1, 2, 3, etc..)

But...
You really shouldn't be "saving" the Message and Action values. (As long
as those values for a particular FaultCode don't change)
Since you've captured the FaultCode, the Associated FaultMessage and
Action can always be re-associated to the FaultCode "on the fly" in any
subsequent form, query, or report.
In that case, you need only "display" the associated value to assist the
user.

If txtFaultMessage was unbound, and had a ControlSource of...
=cboFaultCode.Column(1)
it would always "display" the FaultCode's associated FaultMessage.

If txtAction was unbound, and had a ControlSource of...
=cboFaultCode.Column(2)
it would always "display" the FaultCode's associated Action.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

 

[Tommy2326]

Thanks for that, worked a treat

Tommy,
Create a combobox (ex. cboFaultCode) bound to FaultCode to enter the
FaultCode, and include the columns FaultMessage and Action.
Using the AfterUpdate event of cboFaultCode, you can set the values for
FaultMessage and Action.
Private Sub cboFaultCode_AfterUpdate()
Me.FaultMessage = cboFaultCode.Column(1)
Me.Action = cboFaultCode.Column(2)
End Sub
Every time you select a FaultCode, both fields will be updated with the
associated value. (Combo columns are numbered 0, 1, 2, 3, etc..)

But...
You really shouldn't be "saving" the Message and Action values. (As long
as those values for a particular FaultCode don't change)
Since you've captured the FaultCode, the Associated FaultMessage and
Action can always be re-associated to the FaultCode "on the fly" in any
subsequent form, query, or report.
In that case, you need only "display" the associated value to assist the
user.

If txtFaultMessage was unbound, and had a ControlSource of...
=cboFaultCode.Column(1)
it would always "display" the FaultCode's associated FaultMessage.

If txtAction was unbound, and had a ControlSource of...
=cboFaultCode.Column(2)
it would always "display" the FaultCode's associated Action.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."