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!

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.


--
Daryl S


"Lori LeRoy" wrote:

> 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!

Thank you both - it works!!!

"PieterLinden via AccessMonster.com" wrote:

> Lori LeRoy wrote:
> >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!
>
> 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
> http://www.accessmonster.com/Uwe/For...oding/201005/1
>
> .
>