Results 1 to 9 of 9
  1. #1
    Graham Hefford
    Guest

    Calculation in a Form (Access 2000)

    I am currently developing an database for the company I work for and I have a problem storing a calculation.

    When the user types in the date into the database, the database calculates a formula (=([DATE]-3-[FIRST DAY OF YEAR])/7+1) to turn the date into our production week in a separate box. This calculation is created using a text box's controlsource. The problem is that I need to store this value back in the database itself, But I cant seem to work this out. Is there any one that can help

    Thanks

    Graham

  2. #2
    dave_hill
    Guest

    Re: Calculation in a Form (Access 2000)

    Have you tried using the AfterUpdate event of the data entry textbox to initiate storing the value?
    For example (txtCalcValue refers to the control where the calculation is done)

    CurrentDb.Execute "INSERT INTO table1 ( Field 1) VALUES ( ' " & txtCalcValue & " ' );"

    If txtCalcValue is calculated after the AfterUpdate event of the data entry textbox occurs, you might have to do the calculation in code and send that value over.

  3. #3
    Graham Hefford
    Guest

    Re: Calculation in a Form (Access 2000)

    Hi Dave

    Thanks for your reply, and I am very sorry for troubling you. I have tried your idea but still it does not work. I am new to this kind of work in access so I apologies if I sound a bit dense.

    I have put this line in the after update in the text box event tab, and typed the following

    CurrentDb.Execute "INSERT INTO DATA INPUT ( WEEK) VALUES ( ' " & WEEK & " ' );"

    1. DATA INPUT BEING THE TABLE NAME,
    2. WEEK BEING THE TEXTBOX NAME.
    3. WHERE DOES THE CALCULATION =([DATE]-3-[FIRST DAY OF YEAR])/7+1 GO AS IT IS IN THE CONTROLSOURCE BOX IN THE WEEK TEXTBOX OPTIONS.

    Sorry and can you help me resolve this matter.

    Thankyou very much for your help

    Graham Hefford

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation in a Form (Access 2000)

    Is the value being stored in the same table that the form is based on? You don't ordinarily store values that can be calculated at any time, but if you must, the take the foruma out of the textbox entirely and bind the textbox to the Week field (I would recommend you avoid naming fields and controls things like Week and Month and Year and Date because you will confuse the Access engine which has built-in functions by those names). Then in the afterupdate event of the textbox where the user is typing in a date, put this:

    Me!Week = (Me![DATE]-3-[FIRST DAY OF YEAR])/7+1

    That assumes that you have a textbox named Week and another named Date on your form, and that the Date textbox is the one where a date is being entered.

    When the record is saved, the value in the Week textbox will also be saved.

    This should go in the event procedure, by the way, not in the property sheet.
    Charlotte

  5. #5
    Graham Hefford
    Guest

    Re: Calculation in a Form (Access 2000)

    hi Charlotte

    Sorry for sounding a little thick but i am still having difficulties, as I am a new access developer learning th next level.

    There is a table called DATA INPUT that contains the data, when the inputer types the date the week textbox shows the production week textbox workeds out from the formula and displays the result, then i need this week to be saved in the database as week..

    I put the calculation in the date after update tab, and put the bind the week textbox to the date one using the controlsource of the week text box and assigning it to the date box.

    but i cant seem to get this to work, its all my bosses fault and is putting me off this line of work. can you help simply as i am learning.

    thanks again

    Graham

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation in a Form (Access 2000)

    <hr>I put the calculation in the date after update tab, and put the bind the week textbox to the date one using the controlsource of the week text box and assigning it to the date box.
    <hr>
    The first part (before the comma) is right, but you don't need to do anything special for the week textbox (other than probably wanting to set its locked property to true to prevent people from entering data in it directly). It should be bound to the week field in your table as would normally be the case.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation in a Form (Access 2000)

    You don't need the week textbox to hold a formula if you want to save the value. I'm not sure what you mean by this:
    <hr>I put the calculation in the date after update tab<hr>
    In the code behind the form (you know how to get there, right?), you should see something that looks kind of like this (assuming the textbox where they enter the date is also called Date):

    Private Sub Date_AfterUpdate()

    End Sub

    You put the formula in there between the beginning and ending lines, something like this:

    Private Sub Date_AfterUpdate()
    Me!Week = (Me![DATE]-3-Me![FIRST DAY OF YEAR])/7+1
    End Sub

    This assumes you also have a control on the form that's called "First Day of Year" and that there is a value in that field. Does that explain it clearly enough?
    Charlotte

  8. #8
    Graham Hefford
    Guest

    Re: Calculation in a Form (Access 2000)

    Thanks for the reply

    but the calculation is not working at all,all i get is a 0 in the week textbox ,the calculation is (=[Me]![Week]=([Me]![DATE]-3-[FIRST DAY OF YEAR])/7+1 as pasted from access). The calculation is in the after update on the date textbox where the date is entered and assigned the table value date, and the week textbox is assigned the table field week.

    I cant work this out, sorry
    Graham

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation in a Form (Access 2000)

    What do you mean, "as pasted from Access"? Does that formula work when it's in the controlsource of the textbox? If so, then it should work in code. What is "First Day of Year"? Is that a control on the form, or what? Can you describe what the formula is calculating. It appears to me that you're trying to take the value entered into the Date field, subracting 3 from it and then subtracting the value of [First Day of Year] from it, then dividing the whole thing by 7 and adding 1. What do each of those fields represent? Date should obviously be a date value, but why subtract 3 and what is [First Day of Year]? You can't actually divide a date by anything, so what are you trying to do?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •