On Close assign a value

[Lori LeRoy]

I would like to assign a value to a field when closing a form. I have three
employees (buyers) that we manually assign records for them to work. I would
like to do this automatically based on the ID# of the record. I have the
idea of what to do, but not sure how to program it.

In my table - tblreqs - I would like to automatically assign a buyer (# 10
or #26 or #29) based on the ReqHeaderID.
On close, if ReqHeader ID ends in 1,4,7, or 0, Buyer = 26; If ReqHeaderID
ends in 2,5,8 Buyer = 29; If ReqHeaderID ends in 3, 6, 9 Buyer - 10.

Can someone tell me how to write this code? Thanks!

 

[Daryl S]

Lori -

You can do this in code - I would suggest in the BeforeUpdate event of the
form. Here is some sample code - you will need to use the right fields for
your case.

Dim ReqHeadIDEnd As String

'if ReqHeadID is a string, then use this:
ReqHeadIDEnd = left(Me.[ReqHeaderID],1)
'if ReqHeadID is numeric, then use this:
ReqHeadIDEnd = Str(modMe.[ReqHeaderID] mod 10)

IF isnull(Me.Buyer) Then 'This will only update the Buyer if it is null, so
it won't over-write any existing buyers.
SELECT Case left(Me.[ReqHeaderID],1)
CASE "1", "4", "7", "0"
Me.Buyer = 26
CASE "2","5","8"
Me.Buyer = 29
CASE "3","6","9"
Me.Buyer = 10
END SELECT
End If

You could also build a "BuyerAssignment" table with the key value to drive
this, and use a query to update the Buyer based on the values in this table
and the right-most integer in the ReqHeaderID.

 

[Lori LeRoy]

Thank you both - it works!!!

dim strRightChar as string
strRightChar = RIGHT$(ReqHeaderID, 1)
Select Case strRightChar
Case 0,1,4,7
Me.Buyer = 26
Case 2,5,8
Me.Buyer = 29
Case 3,6,9
Me.Buyer = 10
End Select

--
Message posted via AccessMonster.com


.