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

How to add prices together.

P: 17
I am kind of new to Access but I am trying very hard. I have a pretty good grasp of making tables, forms, and queries, but I am having a very difficult time with getting a certian thing done. I am trying to create a form from a simple table that I have created to pull multiple items out of the table and total the costs together to get a total costs of the items selected. Basically I have a table with 2 fields titled Item and Cost. What I want is to have one control in the form for "item" that I can pick multiple selections from. I also want to be able to choose a quantity for each of those items and then get a grand total cost at the end. Any help you can give will be much appreciated.
Sep 7 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 126
For a beginner, this is a pretty big task to take one, but all the better for your learning curve :)
I had two ideas for this, add the values to a temporary table which is cleared first, or add them to a listbox.
I decided on the second one, because then you can see your order, but I recommend you try to understand the code I give you
rather than just using it. Here goes:

First, create an unbound combobox, when the wizard comes up, click cancel (as with all the other controls). Call it cmbItem
Go to the properties of cmbItem (right click on it -> properties) and ensure the Row/Source Type is set to "Table/Query"
Then, in the Row Source, enter the following SQL: "SELECT item FROM ENTERYOURTABLENAMEHERE ORDER BY item;"
Next, add an unbound text box next to it for the quantity, call this txtQuantity.
Next, add a listbox at the bottom, called lstOrder.
Next, add a command button somewhere called cmdTotal and set the caption to "Total".
Finally, add a command button at the side of txtQuantity called cmdAdd. Change its caption to "Add", and then double click on it
so that the On Event window comes up. Next to "On Click", click the "..." at the right hand side, click Code Builder, and
click OK.

Delete all the code that comes up, and paste all of the following (removing line numbers):
Expand|Select|Wrap|Line Numbers
  1. Dim total As Single
  2. Private Sub Form_Load()
  3.     Dim i As Integer
  4.     For i = 0 To lstOrder.ListCount - 1
  5.         lstOrder.RemoveItem (0)
  6.     Next i
  7.     total = 0
  8. End Sub
  9.  
  10. Private Sub cmdAdd_Click()
  11.     If Nz(txtQuantity.Value, "") = "" Then txtQuantity.Value = "1"
  12.     If Nz(cmbItem.Value, "") <> "" Then
  13.         Dim rs As DAO.Recordset
  14.         Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM INSERTTABLENAMEHERE WHERE Item = '" & removeApostrophes(cmbItem.Value) & "';")
  15.         If Not rs.EOF Then total = total + CSng(rs!Cost)
  16.         lstOrder.AddItem (txtQuantity.Value & "x " & cmbItem.Value)
  17.     End If
  18. End Sub
  19.  
  20. Private Sub cmdTotal_Click()
  21.     MsgBox "Your total so far is: " & Round(total, 2), vbInformation, "Your order so far:"
  22. End Sub
  23.  
  24. Public Function removeApostrophes(ByVal s As String) As String
  25.     removeApostrophes = s 'Please note, this function will cause a stack overflow if passed a String with more than 2878 apostrophes in it.
  26.     If LenB(Nz(s, "")) <> 0 Then
  27.         Dim pos As Integer: pos = InStr(s, "'")
  28.         If pos <> 0 Then removeApostrophes = left(s, pos) & "'" & removeApostrophes(Mid(s, pos + 1))
  29.     End If
  30. End Function
  31.  
PLEASE NOTE: This code is UNTESTED, but if it doesn't work it should at least give you a rough idea.
Sep 7 '07 #2

P: 17
Thank you for the reply. I have done everything as insructed by in line 11 of the code supplied is kicking back an error stating object required. I can't figure out where this is coming from. Can you help further.

Private Sub cmdAdd_Click()
If Nz(txtQuantity.Value, "") = "" Then txtQuantity.Value = "1"
If Nz(CmbItem.Value, "") <> "" Then
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(CmbItem.Value) & "';")
If Not rs.EOF Then total = total + CSng(rs!Cost)
lstOrder.AddItem (txtQuantity.Value & "x " & CmbItem.Val
Sep 8 '07 #3

P: 17
Thank you for the reply. I have done everything as insructed by in line 11 (If Nz(txtQuantity.Value, "") = "" Then) of the code supplied is kicking back an error stating object required. I can't figure out where this is coming from. Can you help further.

Thanks

Private Sub cmdAdd_Click()
If Nz(txtQuantity.Value, "") = "" Then txtQuantity.Value = "1"
If Nz(CmbItem.Value, "") <> "" Then
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(CmbItem.Value) & "';")
If Not rs.EOF Then total = total + CSng(rs!Cost)
lstOrder.AddItem (txtQuantity.Value & "x " & CmbItem.Val
Sep 8 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
For line 11 try

Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.txtQuantity.Value) Then Me.txtQuantity.Value = "1"
Welcome to TheScripts!

Linq ;0)>
Sep 8 '07 #5

P: 17
The form is working now my mistake on spelling. It took a little while to figure it out. I have run into another problem. The form is working as it should by listing the info, but it will not multiply the quantity by the cost of the item. Any suggestions.

Thanks,
You guys are great.


Private Sub cmdAdd_Click()
If Nz(TextQuantity.Value, "") = "" Then TextQuantity.Value = "1"
If Nz(CmbItem.Value, "") <> "" Then
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(CmbItem.Value) & "';")
If Not rs.EOF Then total = total + CSng(rs!Cost)
lstOrder.AddItem (TextQuantity.Value & "x " & CmbItem.Value)
End If
End Sub

Private Sub cmdTotal_Click()
MsgBox "Your total so far is: $" & Round(total, 2), vbInformation, "Your order so far:"
End Sub

Public Function removeApostrophes(ByVal s As String) As String
removeApostrophes = s 'Please note, this function will cause a stack overflow if passed a String with more than 2878 apostrophes in it.
If LenB(Nz(s, "")) <> 0 Then
Dim pos As Integer: pos = InStr(s, "'")
If pos <> 0 Then removeApostrophes = Left(s, pos) & "'" & removeApostrophes(Mid(s, pos + 1))
End If
End Function
Sep 8 '07 #6

Expert 100+
P: 126
Sorry, try this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2. If Nz(TextQuantity.Value, "") = "" Then TextQuantity.Value = "1"
  3. If Nz(CmbItem.Value, "") <> "" Then
  4. Dim rs As DAO.Recordset
  5. Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(CmbItem.Value) & "';")
  6. If Not rs.EOF Then total = total + (CSng(rs!Cost) x CInt(TextQuantity.Value))
  7. lstOrder.AddItem (TextQuantity.Value & "x " & CmbItem.Value)
  8. End If
  9. End Sub
  10.  
Sep 10 '07 #7

P: 17
Thanks so much. Works perfectly!!!
Sep 11 '07 #8

Post your reply

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