Solved

After a recalc focus moves to first record

Posted on 2011-03-25
7
407 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
  • 3
  • 3
7 Comments
 
LVL 84
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:ml-sd
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
Comment Utility
Thanks So Much.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now