Help with some vba mystery.

[binbin]

Hi there, I have an existing thread Here. Unfortunately, I have not gone beyond my last post, and my problem seemed to have spilled over to some vba scripting which I'm totally at a lost.

To make it short, I have a mainform and a subform. I have a DLookup function in my subform.combobox that is based on the mainform.combobox text value.

What I would like to do is similar to the northwind purchase order form. Which basically has a "feature" that if a user has chosen a Supplier and have chosen items to be ordered, then suddenly the user tries to change the Supplier, a warning will pop-up,



Basically telling the user that if you continue, all the items that has been ordered for that particular purchase order will be deleted unless the user presses the "No" option, thus cancelling the combobox update.

I found the before update event procedure for the SupplierID combobox here:

Public Function GetPurchaseDetailsSubform() As [Form_Purchases Subform for Purchase Order Details]
    Set GetPurchaseDetailsSubform = Me.sbfPurchaseDetails.Form
End Function


Private Function PurchaseContainsLineItems() As Boolean
    PurchaseContainsLineItems = Me.GetPurchaseDetailsSubform.RecordsetClone.RecordCount > 0
End Function


Private Sub Supplier_ID_AfterUpdate()
    Me.GetPurchaseDetailsSubform.Product_ID.Requery
    Me.[Purchase Details_Page].Enabled = True
End Sub


Private Sub Supplier_ID_BeforeUpdate(Cancel As Integer)
    'Changing Suppliers with defined line items requires some decisions
    If PurchaseContainsLineItems() Then
        If Not MsgBoxYesNo(ChangeSupplierWarning) Then
            Cancel = True
        ElseIf PurchaseOrder_ContainsPostedInventory() Then
            MsgBoxOKOnly CannotRemovePostedItems
            Cancel = True
        ElseIf Not FRemovePurchaseLineItems() Then
            MsgBoxOKOnly ErrorRemovingPurchaseLineItems
            Cancel = True
        End If
    End If
End Sub

I tried to figure it out, but I do not know how to even start, like where do I find the ChangeSupplierWarning indicated in the MsgBoxYesNo(), usually it is the text that is displayed on the message box, but I can't find it. If there is someone here who could shed some light to this, probably if it is not too much to ask, explain what is happening on each part of the procedure, it would greatly help me understand.

Thanks!

 

[binbin]

I'm smiling right now.

The code above is too advanced for me to understand and would probably take me more time than I would like to spend to incorporate it in my form.

After spending a lot of time finding a way to do the same code on my database form, I finally did it! Well, not exactly, I was only able to imitate it and went in a different direction to accomplish my intended purpose.

Here is what I put in the vba script instead.

Private Sub cmbStoreID_BeforeUpdate(Cancel As Integer)

If DCount("*", "CountQueryName") > 0 Then

    intAnswer = MsgBox("Orders will be deleted if you change store, would you like to proceed?", vbYesNo, "Delete Orders.")

        If intAnswer = vbYes Then
                
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "DeleteQueryName"
            DoCmd.SetWarnings True
            Me.SubformName.Requery
         
        Else
            
            Cancel = True
            Me.Undo

        End If
End If

Basically, I made two queries. A delete query (DeleteQueryName) that has a criteria based on the mainform InvoiceID and a query for counting (CountQueryName) that lists all orders in the invoice detail that has the same criteria.

I then used the DCount to count the records in "CountQueryName." I then made an "If" statement that if it returned a value greater than zero then a "yes or "no" message box will pop up.

It works for now, it may not be as delicious as the northwind code, but remarkably does the work well. I hope this will help anyone out there who was lost as I was.

If anyone sees a potential problem with the above code, please let me know.

Thanks!