Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that is based on a table called "Data"

In this form I have a lookup column that has 3 choices: Return To
Inventory, Return To Customer and Scrap/Rework that is based on a table
called "Item"

I want when either Return To Inventory or Return To Customer is selected to
autofill the next field called "ValueOfRework" to 0 but if Scrap/Rework is
selected I want it to fill in the ValueOfRework with the result of 3 previous
entered fields on the form.
FreightCosts+(QuantityReturned*UnitCost)

The result of either selections must show on the form but also be recorded
into the underlying table in the ValueOfRework field.

I know there may be other solutions, but I have the whole thing done and
really don't want to start writing more queries etc. This was just an after
thought that will make entering information into form faster and easier.
 
Create an event procedure for the combo box's AfterUpdate event; say the
"what to do with it" field is names Status. The event procedure should look
something like this:

Private Sub Status_AfterUpdate()

If Me![Status] = "Return To Inventory" Or Me![Status] = "Return To
Customer" then
Me![ValueOfRework] = 0
Else
Me![ValueOfRework] = Me![FreightCosts] + Me![QuantityReturned] *
Me![UnitCost]
End If

End Sub

If there's the possibility that there may not be any freight costs (or any
of the others) you should use Nz(Me![FreightCosts],0) so that the Null
doesn't trash the rest of the calculation
 
I have a form that is based on a table called "Data"

In this form I have a lookup column that has 3 choices: Return To
Inventory, Return To Customer and Scrap/Rework that is based on a table
called "Item"

I want when either Return To Inventory or Return To Customer is selected to
autofill the next field called "ValueOfRework" to 0 but if Scrap/Rework is
selected I want it to fill in the ValueOfRework with the result of 3 previous
entered fields on the form.
FreightCosts+(QuantityReturned*UnitCost)

The result of either selections must show on the form but also be recorded
into the underlying table in the ValueOfRework field.

I know there may be other solutions, but I have the whole thing done and
really don't want to start writing more queries etc. This was just an after
thought that will make entering information into form faster and easier.
Click to expand...

I'm uncomfortable with the thought of storing derived data, but... it
might be legitimate in this case.

To do it, open the form in design view. Select the combo box (I don't
know what you named it, I'll assume it's named cboAction) and view its
Properties. On the Data tab click the ... icon by the After Update
event and choose the Code Builder. Edit the code to something like:

Private Sub cboAction_AfterUpdate()
Select Case Me!cboAction
Case "Return To Inventory", "Return To Customer"
Me!ValueOfRework = 0
Case "Scrap/Rework"
Me!ValueOfRework = [FreightCosts]+([QuantityReturned]*[UnitCost])
Case Else
<handle error condition>
End Select
End Sub

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top