Refresh and lookup data after an Append Query

[snakie]

Hi, firstly please take a note on this code:

Private Sub AddItem_Click()
On Error GoTo Err_AddItem_Click

    Dim UserConfirm As Integer
    Dim strSQL As String
    Dim OID As String

    OID = Me.ChangeOrderNumber.Value

'Check if the relevant fields have been filled in

Dim strProductNameEntered As Variant
Dim strQuantityEntered As Variant

    strProductNameEntered = Nz(Me.cboProductName.Value, "")
    strQuantityEntered = Nz(Me.Quantity.Value, "")

    If strProductNameEntered = "" Then
        MsgBox "Please select a product from the drop-down box.", _
               vbExclamation, "Product Required"
        Exit Sub
               
    ElseIf strQuantityEntered = "" Then
        MsgBox "Please enter the number of items required in the quantity box.", _
               vbExclamation, "Quantity Required"
        Exit Sub
    
    End If

'User confirmation required
     
    UserConfirm = MsgBox("This item will be added to the quotation " _
        & OID & " ." _
        & vbCrLf & vbCrLf & "Press 'Yes' to proceed; 'No' to cancel.", vbYesNo, "Confirmation Required")

'Add product to existing OrderID
    
    If UserConfirm = vbYes Then

        strSQL = "INSERT INTO tblOrderDetails (OrderID,ProductID, Quantity) VALUES " & _
             "(" & Me.OrderID & ", " & Me.ProductID & ", " & Me.Quantity & ");"

        CurrentDb.Execute strSQL, dbFailOnError
        
    Else
    
    End If
    
    DoCmd.Close acForm, frmSelectProduct
    Forms!frmQuotesGenerator.SetFocus
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 'refresh QS
    Forms!frmQuotesGenerator![Order Details Extended].UnitPrice = DLookup("UnitPrice", "tblProducts", "ProductID = " & [ProductID])
    
  
End_AddItem_Click:
    Exit Sub

Err_AddItem_Click:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
        " in " & Me.Name & ".AddItem_Click", _
        vbOKOnly & vbCritical, "Error Occured"
    Resume End_AddItem_Click

End Sub

This code is behind a button on a pop-up form, allowing an user to add an item to an existing order. I have the following problem:

(1) After an item is added to an order (via append query), how do I refresh the form frmQuotesGenerator so that the item is automatically displayed on the frmQuotesGenerator's subform, [Order Details Extended]?

(2) Order details are being added via a pop-up box, however back to the main form's subform section (continuous form) there is also a product name field and price field. My problem is the price field hasn't been updated.

I have this code in place but I know this only applies if an user adds the item directly to the subform rather than via the pop-up form: DLookUp("UnitPrice","tblProducts","ProductID = " & [ProductID])

So how do I get around to these 2 problems? Sorry it's a bit long but thought I better explain everything first.

Many thanks in advance.

Joe

 

[TIbbs]

To answer question 1. do a requery on the subform, i.e.
Forms!frmQuotesGenerator.Form.Order Details Extended.Requery


Now I am not sure what you are trying to do by your second question, but let me understand better, are you trying to synchronize the pop-up with the main form? If that is the case you need to apply a filter to the pop-up window to match the order beind amended/created.

 

[snakie]

To answer question 1. do a requery on the subform, i.e.
Forms!frmQuotesGenerator.Form.Order Details Extended.Requery


Now I am not sure what you are trying to do by your second question, but let me understand better, are you trying to synchronize the pop-up with the main form? If that is the case you need to apply a filter to the pop-up window to match the order beind amended/created.

Hello and thanks for this.

I am afraid the requery didn't work, and the main form frmQuotesGenerator jumped back to the first record. However, if I manually return to the form and refresh the data via
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
then it works though. Why is that?

With regard to the second problem, okay basically the old method is to have a main form and a subform. On the subform, an user selects an item via a drop-down list, and then an AfterUpdate would "force" the UnitPrice field (next to it) to be updated via a lookup procedure.

Now the new method that I am trying to implement is to use a pop-up box to add an item instead. Through the append query, an item is added to [Order Details Subform], and I am hoping this item would appear back onto the subform (the old form - which has already opened) through refresh/requery. As with the price field on the subform it should have been updated too.

I have attached a copy of the database. The main form is frmQuotesGenerator, and the other form in question is frmSelectProduct.

If you can have a quick look then I would be really grateful.

Thanks in advance for all your help.

Joe

 

[TIbbs]

Sorry, now I know what you are trying to achieve. But I get an error in all your main form and subform when I try to open them.

on the pop up, add to the ProductID item after update event
Forms!frmQuotesGenerator.Form.Order Details Extended.Form.ProductID.Requery

You will not go to a new record but it will requery the productID field with the new value. This applies if the pop up form has the same underlying table as the OrderDetails subform. The fields should automatically be filled in.

If that is not the case and the pop up is not from the same table, then use this code after the sql code that appends the data.

I have used a similar coding but I use a temporary table so calculations can all be made in the temporary table as I have a split database and only want the user to post orders if they are all filled in and with the required amendements.
This is my code on a sku item after update:

Private Sub Sku_AfterUpdate()
On Error Resume Next
'fill in record in the next column
Me.SkuID = Me.SKU.Column(1)

'Requery the field to show modification
Forms!frmSOPOrders.Form.frmSOPScratchPad.Form.SkuID.Requery

End Sub

 

[snakie]

Hello,

I have just opened up the database again and the forms are working though... I have re-attached the database here and see if you can open it, thanks.

Joe

PS. If you wish to add a test record on frmQuotesGenerator, the Change Order Number field needs to be filled in first, and must be in the format of CHG00000 (so the letters "CHG" plus some digits).

 

[TIbbs]

I get a run-time error 2105 same as before as it has vb code on the on open event to go to new record. Remove it and post it again.

DoCmd.GoToRecord , , acNewRec

have you tried my suggestion?

 

[TIbbs]

you will need to clean your code a bit but that is what you need to refresh the subform, put this in your button

Dim strSQL As String

    strSQL = "INSERT INTO tblOrderDetails ( OrderID, ProductID, Quantity ) " & _
            "SELECT " & Me.OrderID & "," & Me.ProductID & "," & Me.Quantity & ";"
    
    CurrentDb.Execute strSQL, dbFailOnError
    

Forms![frmQuotesGenerator]![Order Details Subform].Requery

instead of using a Dlookup, use a query that retrieves the productID item with an additional column for the unit price.
Then set the after update of the productID to set the unit price with that column value.

i.e Me.UnitPrice=Me.cboProduct.Column("value of column")

Use also a saved query for your main form, rather than the select statement that you currently have, it will make it faster.

 

[snakie]

Hi and thanks very much for this. I have cleaned the code a bit and have done as you suggested. It all works now.

Thanks very much for your help.