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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Instead of

DoCmd.Save

Try

Me.Dirty = False

mx
0
Dan89Author 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.