Solved

After a recalc focus moves to first record

Posted on 2011-03-25
7
419 Views
Last Modified: 2012-05-11
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
Comment
Question by:ml-sd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 85
ID: 35213919
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
 

Author Comment

by:ml-sd
ID: 35214112
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
 
LVL 85
ID: 35214206
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ml-sd
ID: 35214443
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
 
LVL 20

Accepted Solution

by:
clarkscott earned 250 total points
ID: 35215068
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
 
LVL 85
ID: 35215452
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
 

Author Closing Comment

by:ml-sd
ID: 35220250
Thanks So Much.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question