Requery control on main form without requerying subform 1

[djayam]

Hi guys,

I'm sure this has been answered in here already but with the search facility being down...

On my main form I have a control ctl_InvoiceTotal. This is a DSum calculation on tbl_Invoices and works fine.

When Subform frm_deliveries is updated (button clicked) it adds values into tbl_Invoices. What I want to do, is when subform frm_Deliveries is updated I want to recalculate ctl_InvoiceTotal on the main form. I am currently using this code in the OnClick event of my updating button (in the subform):

    'refresh Invoice Total control
    Dim vInvoiceTotal As Control
    Set vInvoiceTotal = Forms!frm_NewOrder!ctl_InvoiceTotal

    vInvoiceTotal.Requery

This works fine, updating the control OK. However, the current record in the subform jumps back to the beginning!! Any ideas?

Thanks,

Jason

 

[TheAceMan1]

How are ya djayam . . .

From the subform try:

[blue]   Me.Parent.Requery[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[djayam]

Thanks for replying AceMan.

I tried this before and this sets the record on the Main Form back to the top, not just the sub form. I want this particular control to recalculate but the Main Form and Subform records to remain as they are.

Cheers,

Jason

 

[PHV]

what about this ?
Me.Parent.Recalc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[djayam]

Thanks PHV - still throws the subform record back to the first record though.

Here is all the code for the button OnClick event. I don't know if it'll make any sense to you guys but maybe I'm throwing the focus around too much..?

Private Sub btn_AddToInvoice_Click()
    
    Dim rs As DAO.Recordset
    Dim vMessage As String
    Dim vTitle As String
    
    If IsNull(Me.InvoiceID) = False Then GoTo AlreadyMatched
    If IsNull(Me.ID) = True Then GoTo NoDelivery
    
    [COLOR=green]'add new invoice record if existing not shown[/color]
    If IsNull(Forms!frm_NewOrder!frm_Invoices!ID) = False Then GoTo AddToInvoice:
        [COLOR=green]'get last sequence number[/color]
        Dim vSeqNumber As Long
        vSeqNumber = Nz(DMax("InvoiceSeqNumber", "tbl_Invoices"), 1000) + 1
        
    DoCmd.RunSQL "INSERT INTO tbl_Invoices (InvoiceAmount, InvoiceSeqNumber, OrderID, InvoiceLogDate, InvoiceLogUser) VALUES (0," & vSeqNumber & ", " & Me.OrderID & ", " & Date & ", 'USER')"
    
    [COLOR=green]'requery invoices subform[/color]
    Dim vfrmInvoice As Control
    Set vfrmInvoice = Forms!frm_NewOrder!frm_Invoices
    
    vfrmInvoice.SetFocus
    vfrmInvoice.Requery
    DoCmd.GoToRecord , , acLast
          
AddToInvoice:
    [COLOR=green]'set focus to deliveries form so record updates properly[/color]
    Dim vfrmDeliveries As Control
    Set vfrmDeliveries = Forms!frm_NewOrder!frm_Deliveries
    
    vfrmDeliveries.SetFocus
    
    [COLOR=green]'add to an invoice[/color]
    InvoiceID = Forms!frm_NewOrder!frm_Invoices!ID

    
    [COLOR=green]'add value for units to invoice amount[/color]
    Dim vInvoiceID As Integer
    Dim vUnitPrice As Integer
    Dim vInvoiceAmount As Long
    Dim vfrmInvoiceAmount As Control
                    
    Set vfrmInvoiceAmount = Forms!frm_NewOrder!frm_Invoices!InvoiceAmount
    vInvoiceAmount = Nz(Forms!frm_NewOrder!frm_Invoices!InvoiceAmount, 0)
    
    vInvoiceID = Me.InvoiceID
    vUnitPrice = DLookup("UnitPrice", "tbl_OrderPart", "ID = " & Me.OrderPartID)
    vInvoiceAmount = vInvoiceAmount + (Nz(Me.Quantity, 0) * vUnitPrice)
    
    DoCmd.RunSQL "UPDATE tbl_Invoices SET InvoiceAmount = " & vInvoiceAmount & " WHERE ID = " & vInvoiceID
    vfrmInvoiceAmount.Requery
    
    [COLOR=green]'make remove button visible[/color]
    Me.btn_RemoveFromInvoice.Visible = True
        
    [COLOR=green]'save updates to Deliveries table[/color]
    DoCmd.RunCommand acCmdSaveRecord
    'Me.ctl_Invoiced.Requery
       
    
'    [COLOR=green]'refresh Invoice Total control[/color]
    Me.Parent.Recalc
       
    Exit Sub
    
AlreadyMatched:
    
    Set rs = CurrentDb.OpenRecordset("tbl_Messages")
       rs.Move (20)
    vMessage = rs.Fields("Description")
    vTitle = rs.Fields("Title")
    If MsgBox(vMessage, vbOKOnly, vTitle) = vbOK Then GoTo DontAdd
    
NoDelivery:
    
    Set rs = CurrentDb.OpenRecordset("tbl_Messages")
       rs.Move (24)
    vMessage = rs.Fields("Description")
    vTitle = rs.Fields("Title")
    If MsgBox(vMessage, vbOKOnly, vTitle) = vbOK Then GoTo DontAdd
    
DontAdd:

End Sub

 

[TheAceMan1]

djayam . . .

Sorry about the requery (I meant ReCalc!).

[blue]still throws the subform record [purple]back to the first record[/purple] though.[/blue]

This is because of the requery in your code. What you need to do is hold the InvoiceID and goto that record after requery (additions in [purple]purple[/purple]):

[blue]    'add value for units to invoice amount
    Dim vInvoiceID As Integer
    Dim vUnitPrice As Integer
    Dim vInvoiceAmount As Long
    Dim vfrmInvoiceAmount As Control
                    
    Set vfrmInvoiceAmount = Forms!frm_NewOrder!frm_Invoices!InvoiceAmount
    vInvoiceAmount = Nz(Forms!frm_NewOrder!frm_Invoices!InvoiceAmount, 0)
    
    vInvoiceID = Me.InvoiceID
    vUnitPrice = DLookup("UnitPrice", "tbl_OrderPart", "ID = " & Me.OrderPartID)
    vInvoiceAmount = vInvoiceAmount + (Nz(Me.Quantity, 0) * vUnitPrice)
    
    DoCmd.RunSQL "UPDATE tbl_Invoices SET InvoiceAmount = " & vInvoiceAmount & " WHERE ID = " & vInvoiceID
    vfrmInvoiceAmount.Requery
    [purple][b]Me.Recordset.FindFirst "[InvoiceID] = " & vInvoice[/b][/purple]
    
    'make remove button visible
    Me.btn_RemoveFromInvoice.Visible = True
        
    'save updates to Deliveries table
    DoCmd.RunCommand acCmdSaveRecord
    'Me.ctl_Invoiced.Requery[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[djayam]

Hi AceMan,

'fraid that didn't work. It is definitely the Me.Parent.recalc line in the code that is the guilty party. I have tried this:

    Me.Parent!ctl_InvoiceTotal.Requery
    
    [COLOR=green]'find correct delivery record again[/color]
    vfrmDeliveries.SetFocus
        
    Dim rs2 As Object

    Set rs2 = Me.Recordset.Clone
    rs2.FindFirst "[ID] = 12"
    If Not rs2.EOF Then Me.Bookmark = rs2.Bookmark

To try and force it to ID=12. Doesn't work! If I comment out the Requery on the parent control it's fine.

Driving me nuts!!!

 

[TheAceMan1]

djayam . . .

For the recalc try:

[blue]Forms!frm_NewOrder.ReCalc[/blue]

I have other information for ya but want to get this working 1st . . .

BTW what version access? . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[djayam]

Hiya AceMan - thanks for the response. Sorry for the delay in replying - time differences in the UK!

Forms!frm_NewOrder.ReCalc worked a charm. However, there are 2 other subforms nested in frm_NewOrder and these now seem to requery and jump to the beginning!

How do I repeat (and alter) the following code to get them to find the last record they were displaying?

    [COLOR=green]'set delivery ID for finding record later[/color]
    Dim vDeliveryID As Integer
    vDeliveryID = Me.ID    

    Dim rs2 As Object

    Set rs2 = Me.Recordset.Clone
    rs2.FindFirst "[ID] = " & vDeliveryID
    If Not rs2.EOF Then Me.Bookmark = rs2.Bookmark

Thanks again mate,

Jason

 

[djayam]

PS - Access 2003

 

[TheAceMan1]

[blue]When Subform frm_deliveries is updated (button clicked) it adds values into tbl_Invoices. I want to [purple]recalculate ctl_InvoiceTotal on the main form. [/purple][/blue]

This has been accomplished.

[blue]However, the current record in the subform jumps back to the beginning!![/blue]

I suspect your code structure is causing the wrong section to run. For what you've presented only a form requery would cause this to happen and doesn't occur in [blue]AddToInvoice:[/blue]. It does however occur here:

[blue]    [green]'requery invoices subform[/green]
    Dim vfrmInvoice As Control
    Set vfrmInvoice = Forms!frm_NewOrder!frm_Invoices
    
    vfrmInvoice.SetFocus
    vfrmInvoice.[purple][b]Requery[/b][/purple]
    DoCmd.GoToRecord , , acLast[/blue]

Worst yet in the [blue]AddToInvoice:[/blue] section [blue]you never requery frm_Invoices[/blue] to show the updated record!

For now remove the colon at the end of the following line to restore proper function to your code:

[blue]If IsNull(Forms!frm_NewOrder!frm_Invoices!ID) = False Then GoTo AddToInvoice[COLOR=black yellow]:[/color][/blue]

Let me know the results.

The following is an example of setting the subform frm_Invoices to its previous current record after the form is required:

[blue]   Dim frm As Form, hldID As Long
   
   Set frm = Forms!frm_NewOrder!frm_Invoices.Form
   
   hldID = frm![purple][b][i]PrimaryKeyName[/i][/b][/purple]
   frm.Requery
   frm.Recordset.FindFirst "[[purple][b][i]PrimaryKeyName[/i][/b][/purple]] = " & hldID

   Set frm = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[djayam]

Hiya Aceman,

Thanks for your perserverence with this mate - much appreciated.

Removing the colon didn't seem to have any effect at all, although I know it shouldn't have been there so thank you.

The code you suggested for setting the subforms back to their previous records worked beautifully. I set the variables at the start and actioned the FindFirst right at the end.

So all accomplished. Thank you so much once again - I'll see if I can add 2 stars for the 2 solutions you have given me.

Take care,

Jason