Results 1 to 15 of 15
  1. #1
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28

    Field Issue


    I am having a mental block. I need to use the results in a form field to change the value in another field on the same form when a certain value is set for the first field. Example: in a filed labeled "For Billing" (combo box), if "No" is selected I need the value in the "Rate" field to automatically reset to a currency number "$0.00". If the "For Billing" is set to "Yes" then the "Rate" field should reflect the default value again or have the option of inputting a different value. Rate is automatically filled from value on another form (as a default value only).
    Thanks for the help.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You didn't give the table, field, form names or examples but you could try:

    Code:
    Private Sub For_Billing_AfterUpdate()
       Dim r As DAO.Recordset
    
       If Me.Combo4 = "yes" Then
          Set r = CurrentDb.OpenRecordset("select top 1 [MyField] from [MyTable]")
          '      MsgBox r.Fields("MyField").DefaultValue
          Me.Rate = r.Fields("MyField").DefaultValue
          r.Close
          Set r = Nothing
       Else
          Me.Rate = 0
       End If
    
    End Sub

    For the combobox, I used a value list of "Yes";"No" .

    Change "MyField" and "MyTable" to your field and table names.

  3. #3
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    Sorry. Table is DailyLog. Form is TimeCard. "Rate" and "For Billing" are fields on the form. "Rate" comes from a form called "Projects" and is a default value based on input into the rate in Projects. "For Billing" is a combo box with "Yes/No" and limited to list.

  4. #4
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    Example: I open the Projects form and enter my data. I click on "Time Cards" and it opens the TimeCard form. Data from projects form prefills the pertinent fields in the Time Cards form, such as rate, for billing, etc. When I select "For Billing" I get a "Yes/No" option. It is defaulted to "Yes". If I select "No" I want the rate value to change to "$0.00".

  5. #5
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    If I later need to change it to yes I need to be able to reset the rate value.

  6. #6
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    ""Rate" and "For Billing" are fields on the form" ...

    The fields are labeled the same way in the underlying table called DailyLog

  7. #7
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    SSANFU,

    I copied the code and changed the NyTable and MyField to the proper tables and field names. I put the code in the AfterUpdate property of the "For Billing" field. However, it did not work. I keep getting a debugging error that takes me back to the code.

    The field "For Billing" is a compbo box with "Yes/No" as the only options. It is on a form called Time Card. The field "Rate" is also on th eform called Time Card. Both are linked to a table called Daily Log. If I select "No" on "For Billing" I want the value in the "Rate" field to change to "$0.00". If I slelect "Yes" I need the information in the "Rate" field to stay the same.

  8. #8
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    actually this is a control issue more than a field issue - anyone able to help?

  9. #9
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    OK. This is what I did and it works, just don't know why.

    On the AfterUpdate property of the control called "For Billing" I entered the following code:

    Private Sub For_Billing_AfterUpdate()
    If For_Billing = "No" Then
    Rate = "0"
    Else
    Rate = DefaultValue
    End If
    End Sub

    Now when I select "No" from the combo box the value in the Rate control changes to $0.00. The only other thing I would like it to do is change to the value located at [Forms]![Projects]![Hourly Rate] if the combo box in Time Cards is set to "Yes". But I can live with what I have so I guess I solved my own problem for now.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hi recon2011,

    Just so we are on the same page:
    ""Rate" and "For Billing" are fields on the form" ...
    Tables have fields
    Forms have controls that, if bound, are bound to fields

    Along with that, you should never use spaces nor special characters in object names. (Objects: field, table, query, table or module names) It will cause you headaches and extra code...


    OK,

    I copied the code and changed the NyTable and MyField to the proper tables and field names. I put the code in the AfterUpdate property of the "For Billing" field. However, it did not work. I keep getting a debugging error that takes me back to the code.
    What is the error message? Did you step through the code to see where/what the error was?


    Is the control "Rate" on the form "Projects" bound to the same table/field as the "Rate" control on the form "TimeCard"?

    You have a combo box control names "For Billing". Internally, Access doesn't handle spaces in names, so it changes it to an underscore. In your code, you are refering to a control names For_Billing. I have never seen this work before, since there is not a control named For_Billing (with the underscore).

    When you use the unqualified name "Rate", you could be referring to:

    [Forms]![Projects]!Rate
    [Forms]![TimeCard]!Rate
    a field "Rate" in a table

    You can use "Me.Rate" where "Me." is shorthand for "[Forms]![FormName]" (as in [Forms]![Projects]), if the code is behind a form. The "Me." reference will not work in a standard module.


    "DefaultValue" is a property - if this returns a value, I would be leery that it is the correct value. You didn't specify where (the field or control) the default value was to come from.


    The only other thing I would like it to do is change to the value located at [Forms]![Projects]![Hourly Rate] if the combo box in Time Cards is set to "Yes".
    I would think you would want to change the value of [Forms]![Projects]![Hourly Rate]to 0 if the combo box selection was "No".
    If the form is open, all you would need to do is have a line like this in the sub:
    [Forms]![Projects]![Hourly Rate] = 0

  11. #11
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    SSANFU,

    What I have is a form called projects that is used to make a record of projects that one or more people may be working on. This form has a couple of subforms that track things such as associated property and time cards of the people assigned to the project. There is a link (command button) that opens the time card forms in edit mode where users can input their daily activities.

    The projects form has a control called "Hourly Rate" used for the project. Each employee would be working under that hourly rate so when they select "Time Card" the rate is prefilled for them to avoid errors. However, sometime a rate may vary from person to person or even by activity so the time card must reflect the rate for that period of activity by that person. On many occasions a person may do something under that project for which time is not billed. This is where I need the "Rate" to set itself to "$0.00" if "No" is selected under the "For Billing" control. Both controls are on the "Time Card" form.

    Currently I have the "Rate" on the Time Card form to default to the Rate set in the Projects form, but it is only a default and can be changed on Time Card form without affecting the Rate on the Projects form.

    As far as the error on the code you gave me I don't know what it was. After I copied the code into the AfterUpdate property I tried to run it but was kicked back to the code builder screen with the top line highlighted. I changed som ethings and then was taken back to the code screen with the DAO.recordset line highlighted.

    DefaultValue didn't make much of a difference because nothing changed in the time card forms. The rates entered previously remained the same and any new record carried the Rate over from Projects as intended. Now when I select no the rate changes to "$0.00" but if I change it to yes it remains at "$0.00" and I have to manually change it back.

    I hope this makes sense.

  12. #12
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    The defaultvalue I mentioned is the property for the control called Rate. In the properties section under DefaultValue I defaulted the value to be equal to the value of the control called Rate on the Projects form.

    [Forms]![Projects]!Rate

    is different from

    [Forms]![Time_Card]!Rate

    They are each connected to their respective Tables: tblProjects and tblDailyLog

    The reason for the two Rates is that there is a need to set the hourly rate for the project but have the ability to modify the hourly rate for any particular activity on a time card.

    For example: the hourly rate established for a project called HR Consulting may be set at $150.00/hour. The location of the job mnay be two hours away. Johnny drives to the location and bills for two hours of drive time at $75.00/hour. So his time card for the drive there will show $75.00 under the Rate while the Project would show the $150.00. Bill comes along and performs three hours of work under that project. Bill's time card shows three hours at $150.00. These amounts are totalled up in a running sum. That is why there is a "For_Billing" control set up as a combo box. If the time is not being billed for whatever reason I need the rate on th etime card to show ) so it does not get added to the running sum. I hope this clears it up.

  13. #13
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    whatever reason I need the rate on th etime card to show ) so it does not get added to the running sum. I hope this clears it up.

    I meant to say show "$0.00"

  14. #14
    recon2011 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    28
    Would this work in the AfterUpdate of the control called For_Billing:

    Private Sub For_Billing_AfterUpdate()
    If Me.For_Billing = "No" Then
    Me.Rate = "0"
    Else
    Me.Rate = [Forms]![Projects]![Rate]
    End If
    End Sub


    The Projects form remains open when the Time Card form is opened and remains under the Time Card form until Time Card is closed.

    I haven't tried this yet and will wait unti lI hear back so I don't mess up what appears to be working (mostly) right now.

    Thanks for your help!

  15. #15
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One way to try code is to comment out the current code (do not delete it) and ass the new code. If the new code doesn't do what you want/expect then delete the new code and un-comment the old code. The best of both worlds.... save the old (working code) and test the new code.

    And Yes, it looks like the new code whould work.

    I have A2K7 at home. If you want to remove sensitive info from your mdb, do a compact & repair, zip it then upload it, I will look at it this weekend.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated field with NOT Equal issue
    By kbassnac in forum Queries
    Replies: 8
    Last Post: 04-20-2011, 07:11 AM
  2. Report Data Field Issue
    By SilverSN95 in forum Access
    Replies: 3
    Last Post: 07-23-2010, 07:53 AM
  3. Replies: 0
    Last Post: 07-13-2010, 07:45 AM
  4. Urgent issue! Create field for intervals
    By Bjorn in forum Queries
    Replies: 3
    Last Post: 02-10-2010, 10:26 AM
  5. Issue summing two field values
    By w2vijay in forum Reports
    Replies: 4
    Last Post: 02-10-2010, 01:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums