On Close assign a value
in [Forms]
|
Prev: Custom Record Selector Controls that don't work like the built-in
Next: Combo Box Auto expand List Width
From: Lori LeRoy on 24 May 2010 13:30 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!
From: PieterLinden via AccessMonster.com on 24 May 2010 14:30 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/Forums.aspx/access-formscoding/201005/1
From: Daryl S on 24 May 2010 14:41 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!
From: Lori LeRoy on 24 May 2010 16:04
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/Forums.aspx/access-formscoding/201005/1 > > . > |