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):
-
Dim total As Single
-
Private Sub Form_Load()
-
Dim i As Integer
-
For i = 0 To lstOrder.ListCount - 1
-
lstOrder.RemoveItem (0)
-
Next i
-
total = 0
-
End Sub
-
-
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 INSERTTABLENAMEHERE WHERE Item = '" & removeApostrophes(cmbItem.Value) & "';")
-
If Not rs.EOF Then total = total + CSng(rs!Cost)
-
lstOrder.AddItem (txtQuantity.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
-
PLEASE NOTE: This code is UNTESTED, but if it doesn't work it should at least give you a rough idea.
17
