1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 form calculated fields

Discussion in 'Business Applications' started by dltoney2, Jan 15, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    Form has several calculated fields. The first part of this code works fine, the second part that is supposed to calculate the Reservations Amount Owed does not work - the field returns blank. I also have another calculated field that isn't working: Reservations Balance Due which would be Reservations Amount Owed minus Reservations Amount Paid. What am I doing wrong? Thanks in advance for your help.

    Private Sub Combo68_AfterUpdate()
    If Me.Special_Ticket = "Corporate_Table" Then
    Me!Ticket_Price = "312.50"
    Else
    If Me.Special_Ticket = "Sweetheart_Ticket" Then
    Me!Ticket_Price = "800"
    Else
    If Me.Special_Ticket = "Patron_Ticket" Then
    Me!Ticket_Price = "350"
    End If
    End If
    End If
    End Sub

    Private Sub Reservations_Amount_Owed_AfterUpdate()
    Me.[Reservations_Amount_Owed] = Me.[Reservations_Number_of_Tickets] * Me.[Reservations_Ticket_Price]
    End Sub
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    dltoney2, welcome to the Forum.
    You say "the second part that is supposed to calculate the Reservations Amount Owed does not work - the field returns blank"
    This can be caused by one field being a Null value, (Not zero but blank), or by not having the correct field name.
    I notice that your code uses
    Me!Ticket_Price
    and
    Me.[Reservations_Ticket_Price]
    Does the Me!Ticket_Price field update the Me.[Reservations_Ticket_Price] field?
     
  3. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    That is strange why the first part actually worked when the name of the field really is Reservations Ticket Price - I have changed the code to the correct name. Yes this code updates the Reservations Ticket Price.

    However, the second part still does not work.

    Private Sub Combo68_AfterUpdate()
    If Me.Special_Ticket = "Corporate_Table" Then
    Me!Reservations_Ticket_Price = "312.50"
    Else
    If Me.Special_Ticket = "Sweetheart_Ticket" Then
    Me!Reservations_Ticket_Price = "800"
    Else
    If Me.Special_Ticket = "Patron_Ticket" Then
    Me!Reservations_Ticket_Price = "350"
    End If
    End If
    End If
    End Sub
    Private Sub Owed_AfterUpdate()
    Me.Owed = Me.Reservations_Number_of_Tickets * Me.Reservations_Ticket_Price
    End Sub
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    OK, as far as I can see the VBA code is Ok, but in the wrong place.
    It is in the Private Sub Owed_AfterUpdate() event procedure, which only gets updated AFTER you put something in it.
    So if you enter a value in the Owed field does it then work OK?
     
  5. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    Apparently I spoke too soon about the first part working. I get an error message "Method or Data Member not found"
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    It sounds like you need to check the form's field names versus the Table field names.
    You should be using whatever the VBA editor offers you when you type in
    me.
     
  7. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    The Special Ticket field is a combo box (3 drop down options) so I changed the code to reflect Combo68. I am not getting the error message previously mentioned, however, the Reservations Ticket Price field is auto filling in 150 no matter what is selected in the Special Ticket drop down box. 150 should be the default Reservations Ticket Price unless one of the three is selected in the Special Ticket field.

    Private Sub Combo68_AfterUpdate()
    If Me.Combo68 = "Corporate_Table" Then
    Me.Reservations_Ticket_Price = "312.50"
    Else
    If Me.Combo68 = "Sweetheart_Ticket" Then
    Me.Reservations_Ticket_Price = "800"
    Else
    If Me.Combo68 = "Patron_Ticket" Then
    Me.Reservations_Ticket_Price = "350"
    Else
    Me.Reservations_Ticket_Price = "150"
    End If
    End If
    End If
    End Sub
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Combo boxes normally use the Key field as the first column, but hidden. So you need to check that combo68 actually gives you "Corporate_Table" when it is selected and not the key field Value.
    add aline of code before the rest to test it
    msgbox Me.Combo68
     
  9. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    You are so very right - saved in the table is a number not the words.
    Not sure what you mean by add a line of code before the rest...Where exactly should I add msgbox Me.Combo68 in the code?
    Thanks for your patience.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    after the line
    Private Sub Combo68_AfterUpdate()
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    You can use the table ID values for your code or use
    Combo68.column(1)
    to use the text.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Got to go now.
     
  13. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    Thanks anyway for trying. I am not experienced with writing code - I can cut and paste like crazy :)

    Nothing I have tried will populate the Reservations Ticket Price field from the Special Ticket Combo field...I give up.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Well just let me know if you want to learn how to do it.
    I can teach you the correct way, one thing that I have noticed is that your VBA code use "312.50", whereas if your field is a Currency field, as it should be, you should use 312.50, ie not text but a Value.
     
  15. dltoney2

    dltoney2 Thread Starter

    Joined:
    Jan 15, 2012
    Messages:
    8
    I do want to know how to learn how to do it. I don't know how to write code. I would appreciate the help - very frustrated yesterday - better today. Can we try again?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1036362

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice