Hi all, I am using a continuous form to make a quote builder, quotes are built up of different items, the user selects an item from a combo box and then enters the quantity of that item. Another combo box then appears and the process is repeated until all items have been entered. The information is saved into a table. In the combo box I have included a blank option so that in the case the user accidently selects one item too many, they can re-choose the blank option so the record can be deleted using an action query that simply looks for record that have null values and deletes them. The SQL statement I am using for the row source is:
SELECT [ItemID], [ProductName], [Description], [SaleCost] FROM Stock UNION SELECT Null AS [ItemID], Null AS [ProductName], Null AS [Description], Null AS [SaleCost] FROM Stock
ORDER BY [ProductName];
Usually when a normal option is selected an input box appears asking for a quantity, cost is entered automatically. Description is just for reference and is not saved. When the blank record is selected what I want to happen is for no input box to appear (this works) then for the record to be deleted using the query or otherwise, then for the form to be refreshed so that the combo box is removed from the form. What happens currently is that I select the blank option, no input box appears but the record is not deleted, if I then select the blank option a second time, the record is deleted but the form is not refreshed, if I then hit the refresh button, the combo box disappears, clearly this is semi-working. The code I am using to do this is:
Private Sub cmbItems_AfterUpdate()
If [cmbItems].Value > 0 Then
[Price] = [cmbItems].Column(3)
[Qty] = InputBox("Enter in Quantity", "Quantity", 1)
DoCmd.Save
Else
DoCmd.Save
DoCmd.OpenQuery "qryDeleteNull"
Me.Refresh
Exit Sub
End If
End Sub
Can anybody suggest an idea that would complete this code to make the process fully functional?
Many thanks
Dan
Start Free Trial
