Solved

After a recalc focus moves to first record

Posted on 2011-03-25
7
414 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
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 84
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 84
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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