Continuous form update

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
Dan89Asked:
Who is Participating?
 
Dan89Connect With a Mentor Author Commented:
Solves the first bit of the problem (deletes the record as it should), just still not refreshing and removing the combobox.
Thanks
Dan
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Instead of

DoCmd.Save

Try

Me.Dirty = False

mx
0
 
Dan89Author Commented:
Got it, thanks mx for solving the main issue, i needed to to a requery rather than a refresh, code:

Private Sub cmbItems_AfterUpdate()
If [cmbItems].Value > 0 Then
    [Price] = [cmbItems].Column(3)
    [Qty] = InputBox("Enter in Quantity", "Quantity", 1)
    DoCmd.Save
Else
    Me.Dirty = False
    DoCmd.OpenQuery "qryDeleteNull"
    Me.Requery
    Exit Sub
End If
End Sub

Thanks
Dan

0
All Courses

From novice to tech pro — start learning today.