?
Solved

After a recalc focus moves to first record

Posted on 2011-03-25
7
Medium Priority
?
425 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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