OnExit Event Procedure for appending to a text field

Posted on 2004-12-01
Last Modified: 2012-08-14
I have a form with 9 fields.
   1           2            3                  4             5             6             7        8           9              10
Item ID  Descr1    Field3            Field4      Field 5     Field6        Qty    Rate    ExtPrice      ProdCat

The OnExit for the Item Id performs several DLookups to fill-in the Desc1, Rate, and Product Category fields.

I need an OnExit Event Procedure to override the value in Descr1 when the value of the Product Category field equals "2".
The value to be written into the field needs to be the values of Fields 3 thru 6 seperated with by a dash.

1       (IU-1")        (Plate 1/4")   (1/2" AS)      ( )       (Plate 1/4")    1        50.00    50.00             2
1G     (IU-1" Gas)  (Plate 1/4")   (1/2" AS)   (Argon)   (Plate 1/4")   1        50.00    50.00             2

The OnExit EP should override the (IU-1") and (IU-1" Gas) with
                                                                                          (Plate 1/4" - 1/2" AS - Plate 1/4") and
                                                                               (Plate 1/4" - 1/2" AS - Argon - Plate 1/4")
Notice the first replacement does not place a dash if Field 5 is null. That should be true for all fields 3 thru 6.

More urgent than difficult. I can write a macro to perform this function, but would rather have the code.

Question by:bigwoodyg
    LVL 16

    Expert Comment


    CurrentDb.Execute "Update YourTable Set Descr1 = Field3 & '/' & Field4 & '/' & Field5 & '/' & Field6 Where ProdCat = '2'"

    Please, backup before trying it.
    LVL 46

    Expert Comment

    Hi bigwoodyg

    If ProdCat = 2 then
     Descr1 = (Field3) & IIf(Not (IsNull(Field4)), "-") & Field4 & IIf(Not (IsNull(Field5)), "-") & Field5 & IIf(Not (IsNull(Field6)), "-") & Field6

    End if

    Good Luck!


    Author Comment


    three issues with your solution, I can't get around.  
    First, my Table Name is made up by two names =  "PO Detail".
    I believe the bracket fix this issue.

    Second, after replacing the Field1 with actual field names, and attempted to enter a record I receive the following error:
    Too few parameters. Expected 2.

    CurrentDb.Execute "Update [PO Detail] Set Descr1 = Mono1Type & '/' & Mono1Size & '/' & Mono1Color & '/' & Mono2Type & '/' & Mono2Size & '/' & Mono2Color Where ProdCat = '2'"

    Third, I'm not sure if your solution handles the null value criteria.

    Author Comment


    I can't compile your solution. Receiving this error.

    Arguement not optional.

    Cursor rests at the first IIf.

    Here's the actual code.

    Private Sub Lite2_ColorCoat_Exit(Cancel As Integer)

    If Category = 2 Then
     Descr1 = (Mono1Type) & IIf(Not (IsNull(Mono1Size)), "-") & Mono1Size & IIf(Not (IsNull(Mono1Color)), "-") & Mono1Color & IIf(Not (IsNull(Mono2Type)), "-") & Mono2Type

    End Sub
    LVL 46

    Accepted Solution


    Private Sub Lite2_ColorCoat_Exit(Cancel As Integer)

    If Category = 2 Then
     Descr1 = (Mono1Type) & IIf(Not (IsNull(Mono1Size)), "-","") & Mono1Size & IIf(Not (IsNull(Mono1Color)), "-","") & Mono1Color & IIf(Not (IsNull(Mono2Type)), "-","") & Mono2Type
    End if

    End Sub


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now