By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Decimal problem when adding data by vba

P: 18
Hi, I couldn't get around a problem about decimal rounding up when adding a record to a table from a form by programming a button.

I have an invoice form where the grand total is a calculated text field. The command button "cmdProcess" is supposed to add some fields (customer no, invoice date, grand total (as receivable), currency etc) to a table which keeps track of customer transactions. However, the decimals of grand total field is rounded up to integer (Funny, this does not happen with the command button that opens a report and prints the invoice).

The txtGrandTotal field on the form is standard with decimals set to 2, so is the receivable field in the table.

Here's the code (changed names to English for better understanding):
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdProcess_Click()
  2.     Dim db As Database
  3.     Dim rstTransaction As DAO.Recordset
  4.     Dim intAns As Integer
  5.  
  6.     Set db = CurrentDb
  7.     Set rstTransaction = db.OpenRecordset("tbl_AccountTransaction")
  8.  
  9.     rstTransaction.Index = "InvoiceNo"
  10.     rstTransaction.Seek "=", Me.InvoiceNo
  11.  
  12.     If rstTransaction.NoMatch = False Then
  13.         intAns = MsgBox("Invoice is already processed. Update?", vbYesNo)
  14.             If intAns = vbYes Then
  15.                 rstTransaction.Edit
  16.                 rstTransaction("trsCustNo").Value = Me.CustNo
  17.                 rstTransaction("trsDate").Value = Me.date
  18.                 rstTransaction("InvoiceNo").Value = Me.InvoiceNo
  19.                 rstTransaction("receivable").Value = Me.txtGrandTotal
  20.                 rstTransaction("cur").Value = Me.cur
  21.                 rstTransaction.Update
  22.                 MsgBox "Customer receivable updated"
  23.             Else
  24.                 Exit Sub
  25.             End If
  26.     Else
  27.         rstTransaction.AddNew
  28.                 rstTransaction("trsCustNo").Value = Me.CustNo
  29.                 rstTransaction("trsDate").Value = Me.date
  30.                 rstTransaction("InvoiceNo").Value = Me.InvoiceNo
  31.                 rstTransaction("recievable").Value = Me.txtGrandTotal
  32.                 rstTransaction("cur").Value = Me.cur
  33.                 rstTransaction.Update
  34.         rstTransaction.Update
  35.  
  36.         MsgBox "Receivable added to customer account"
  37.     End If
  38.     rstTransaction.Close
  39.     Set rstTransaction = Nothing
  40.     Set db = Nothing
  41.  
  42. End Sub
  43.  
thanks in advance for any ideas
Nov 4 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Celal.

Are you sure tbl_AccountTransaction.[receivable] field has an appropriate data type?

Regards,
Fish
Nov 4 '08 #2

P: 18
Fish,

Thanks a lot for your warning.. I realized the field was long integer in stead of single. I have overlooked and took your time (also spent my time) for such a simple mistake. Sorry about that.

Celal
Nov 6 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Hello, Celal.

You don't need to apologise.
Glad you've found solution.

Best regards,
Fish
Nov 6 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.