• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

After a recalc focus moves to first record

After I do a recalc the focus moves to first record.  I tried to use a bookmark.  Im not really sure how that code workes , I found it on the internet.  I got an invalid use of bookmark...where the code said control source, I just replaced it with a control source on the form ??

Thanks
Private Sub Amount_AfterUpdate()
If Commission = True Then
    Comm = Me.Amount * Me.Parent.Percent
 Else
    Comm = 0
End If

Me.Requery
     
     'Calculate the Subtotals
     
        'Calculate Premium
     strWhere = "(TransType <> 'PAY') And [Commission] = True"
     Me.Parent![Prem] = DSum("Amount", "InvoiceDataQ", strWhere)
       
     'to update subtotals
       Me.Parent.Recalc

'I replaced the sbove Me.Recalc with the Code Below.  I dont understand why the Dim BM was Remmed . I tried it both ways


  ' Dim BM as Variant
  Dim rst as Recordset
  set rst = Me.RecordsetClone
  If rst.RecordCount >0 then    
    Me.Recalc
    Me.Bookmark = BM
    Me.(next Control).SetFocus 
  Else
    Me.Recalc
    Me.(next Control).SetFocus
  End If 

End Sub

Open in new window

0
ml-sd
Asked:
ml-sd
  • 3
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you want to go back to the current record after a recalc/requery?

If so, then you'll have to do this:

Dim lnID As Long

lnID = Me.YourIDField

<recalc or requery here>

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "YourIDField=" & lnID
If Not rst.NoMatch Then  Me.Bookmark = rst.Bookmark

Note that if YourIDField is a Text value, you'll have to do this:

Dim sID As String

<other stuff>

rst.FindFirst "YourIDField='" & sID & "'"
0
 
ml-sdAuthor Commented:
Still going back to the first record of the parent form...
There is a master form , subform then a subsub form.  The event is on the subsub form.  below is how I used your code
Private Sub Amount_AfterUpdate()
If Commission = True Then
    Comm = Me.Amount * Me.Parent.Percent
Else
    Comm = 0
End If

Me.Requery
     
     'Calculate the Subtotals
     
        'Calculate Premium
     strWhere = "(TransType <> 'PAY') And [Commission] = True"
     Me.Parent![Prem] = DSum("Amount", "InvoiceDataQ", strWhere)
     
     'to update subtotals
     ' Me.Parent.Recalc
      
     
Dim lnID As Long

lnID = Me.InvoiceDataID      'Here the value for inid was 22
Me.Parent.Recalc

'Code from LSM Consulting 3/25/11
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "InvoiceDataID=" & lnID    ' This is the Id from the subform.  Here the value for inid was 22

If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark

MsgBox inid  ' This value was empty
'<other stuff>
'I didnt need to do anything else after the recalc

rst.FindFirst "InvoiceDataID='" & inid & "'"    ' This was sID, I changed this to inid . still didnt work 
      
End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then you need to work with the RecordSetClone of the Parent. If this is a Sub-Subform, then you'll have to do this:

Set rst = Me.Parent.Form.Parent.RecordsetClone

And also do this when setting the Bookmark:

Me.Parent.Form.Parent.Bookmark = rst.Bookmark

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ml-sdAuthor Commented:
Almost there,  I think

Getting error
run time error 3070  The microsoft jet database engine does nort recognize "InvoiceNumberID" as a valid field name or expression

Heres the line of code
rst.FindFirst "InvoiceNumberID=" & lnID


I changed the ID Field to the parent.
0
 
clarkscottCommented:
dim vid as long
vid = me.YourPrimaryKey '--- field on your form.
me.requery
me.yourprimarykey.setfocus
docmd.findrecord vid
me.AnyFieldYouWant.setfocus

Scott C
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The error you're getting is fairly obvious: You're not properly referring to the field where you should be searching.

If InvoiceNumberID as Field on your PARENT form? If not, you'll have to change that to refer to the correct Field on your PARENT form.
0
 
ml-sdAuthor Commented:
Thanks So Much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now