Easy Combobox question

[Stoss]

Hello all,

I have a quick combobox question. I am having a brain freeze right now and for the life of me can't figure this out.

I have a combobox with SupplierID info. When this box is changed to a different supplier, a subform changes to list the items for that Supplier.

Basically, what I want is that if a person changes that value (and there is a value already there) to popup a message saying that it will delete all the items in a subform. But if it is null, then let them change it without showing a message or deleting anything in the subform (which would be blank at this time anyway).

I am just stuck on which event to put it on and the exact coding.

In simple terms... if previous value is Null continue with value change, if not null, then popup a message saying "if you change this, you will delete all items below"

Here is the code that I have but not sure if it is right or which event to put it in.
Dim Response As Integer

If Me.cboSupplierID = vbNullString Then Exit Sub
Response = MsgBox("By selecting another Supplier, you will delete ALL previously made orders for this PO. Do you wish to continue?", vbYesNo, "Continue")

If Response = vbYes Then
DoCmd.OpenQuery "qryDeleteOrderDetails"
Me.Order_Details_subform.Requery
End If

Oh, and also, if the response is No, I want the combobox to go back to the original value.


Hope that makes sense...
Thanks
-Stoss

 

[vbaInet]

Basically, what I want is that if a person changes that value (and there is a value already there) to popup a message saying that it will delete all the items in a subform. But if it is null, then let them change it without showing a message or deleting anything in the subform (which would be blank at this time anyway).

In simple terms... if previous value is Null continue with value change, if not null, then popup a message saying "if you change this, you will delete all items below"

Right, the sentences above don't quite make sense. Maybe you want to shed more light? How can you have Null SupplierIDs? If a supplier ID is null that record simply shouldn't exist. If you do do have Null supplier Ids then I suspect that you either don't have a primary key in that table or are using an Autonumber pk (which in itself is meaningless for this table).

 

[Stoss]

Sorry, I will try to clarify...

Here is the process and what I was trying to get at earlier.... There is a parent form and a subform. If a user clicks, "New PO" the form is clear. After the user selects "Location"..etc. They will come to a combobox on the Parent form that is for Supplier. This will be Blank (or what I was trying to say earlier...Null). They then select a supplier. Once they do that, they will go to the subform and select different products to order and these products (combobox) are based off the Supplier. Now, lets say someone decides to change the supplier, I want a msgbox to appear and say "are you sure, you will delete all items below". Logic is that by changing the supplier, the products in the child form are no longer valid products.

I have it working to the point that by changing it, it shows the msgbox and the deletes the child form. But, what I am still missing is if someone says "No" (as in I don't want to change the supplier) then go back to the supplier that was already there. In my form and coding, if you say no it doesn't delete anything but the combobox stays on the newly selected supplier and not what was originally there. AND, if there is nothing to begin with (i.e. New PO) I don't want that msgbox to appear.

Hope that clears it up a little.
Thanks,
Stoss

 

[vbaInet]

Look into using the OldValue property of the combobox.

PS: If there was no record in the subform then it might be an idea to just carry on with the other actions ignoring the msgbox and delete command.

 

[Stoss]

Ok, I will look into that, I didn't know that was available. Thanks.

 

[Stoss]

Thanks vbaInet!

I got everything working just fine! I had never seen the Oldvalue property before, that is a pretty cool one to know about.

Thanks for all your help!
-Stoss

 

[vbaInet]

Glad you got that sorted.