Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2013
    Posts
    8

    Unanswered: Insert the calculated field of a form in the table

    Hello All,

    I have created a table for employee information and i want to calculate the Earned salary of a employee based on the Actual Working Days.
    So i have created a Form to calculate the Earned Salary value where i will give the working days as input on the Form.

    I have calculated it as
    Earned salary= Basic pay/Total No of working Days*Actual WorkingDays.

    Now i want to pass this Earned salary value in the table field.
    Can you please suggest me how i can do this???

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Except for a few exceptions, never store a computed value into a table. You can compute it each time you need it (in a query or a form). Also don't use spaces or other non-alphanumeric characters in the names of the objects (column names, control names, etc.).

    Otherwise, you can use something like (replace the names in italic by the one in use in your database):
    Code:
    Dim strSQL As String
    strSQL = UPDATE TableName SET EarnedSalary = " & Me![Earned salary] & " WHERE EmployeeId = " & Me!EmployeeId;
    CurrentDb.Execute strSQL, dbFailOnError
    Have a nice day!

  3. #3
    Join Date
    Nov 2013
    Posts
    8
    Hello sinndho can you please tell me where i can write this code is it in After Update Event.

    Do i need to store the calculated value in any variable??

  4. #4
    Join Date
    Nov 2013
    Posts
    8
    i am getting a compile error expected end of statement,Can you please help,

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, it should be:
    Code:
    strSQL = "UPDATE TableName SET EarnedSalary = " & Me![Earned salary] & " WHERE EmployeeId = " & Me!EmployeeId;
    Have a nice day!

  6. #6
    Join Date
    Nov 2013
    Posts
    8
    I have tried writing like this but doesnot work


    Private Sub EarnedSalary_AfterUpdate()
    Dim dblEarnings As Double

    dblEarnings = Me!BaicPay / Me!TotalWorkingDays * Me!ActualWorkedDays

    Dim strSQL As String

    strSQL = " UPDATE [PR] SET EarnedSalary = " & Me![dblEarnings] & ";"

    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "but does not work" does not help in understanding what the problem could be. Can you compile the project? Is there an error message? If there is, what is it (number + description)? On which line?

    Moreover, without a WHERE clause in the SQL statement, you'll update the whole table, not just the current row of the Form.
    Have a nice day!

  8. #8
    Join Date
    Nov 2013
    Posts
    8
    There is no any error msg when i save the code every thing is ok.But when i run the form i cant see the values in table.On the form its showing the value.but its not being populated in the table.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you want to create a new row in the table and store the computed value into it?
    Have a nice day!

  10. #10
    Join Date
    Nov 2013
    Posts
    8
    @ sinndho:I want to store the value in the field which already exists in the table.Is it Event correct where i am writing my Code.And do i need to write a Update Statement for the bound fields on the form also.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To uptate the value of a column of a row in a table, the SQL statement is
    a) if the value is numeric:
    Code:
    UPDATE [TableName] SET [ColumnName] = Value WHERE [RowID] = UniqueValue;
    b) if the value is a text:
    Code:
    UPDATE [TableName] SET [ColumnName] = 'Value' WHERE [RowID] = UniqueValue;
    where:
    a) [TableName] is the name of the table,
    b) [ColumnName] is the name of the column,
    c) [RowID] is the name of a colomn that contain a unique value for each row (Identity column). It can be the primary key of the table or a column with a Unique index. You can also use several columns to uniquely identify a row,
    d) UniqueValue is the value in the column [RowId] that uniquely identify the row to be updated.

    Note: If you do not specify a WHERE clause all rows in the table will be updated.

    To execute a SQL statement from a VBA procedure, you can use:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE [TableName] SET [ColumnName] = Value WHERE [RowID] = UniqueValue;"
    CurrentDb.Execute strSQL, dbFailOnError
    If this does not work and no error is raised, chek the value of the variable strSQL before executing the SQL statement:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE [TableName] SET [ColumnName] = Value WHERE [RowID] = UniqueValue;"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    Have a nice day!

Posting Permissions

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