Solved

How to move to a particular field in the next record of a datasheet form"

Posted on 2011-09-27
15
312 Views
Last Modified: 2012-05-12
I have a main form and a subform and I need to know how to move the cursor to the next line (after the user makes changes to the last field on the datasheet (subform)) and set the focus to a particular control on the subform datasheet.

Thanks,
gdunn50
0
Comment
Question by:gdunn59
  • 9
  • 3
  • 3
15 Comments
 

Author Comment

by:gdunn59
ID: 36712796
It currently is moving to the next record on the subform datasheet, but as soon as a make a change in that next record in the cboAssoc control which has the focus (which is the field that I want to have the focus), the cursor jumps back to the first record on the subform datasheet and the cboAssoc control.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 36714222
Hi,

Check for a requery somewhere in your code where you are setting the focus to the control.  The behavior that you describe seems to indicate a requery of the data.

Regards,

Bill
0
 

Author Comment

by:gdunn59
ID: 36718530
Bill,

I removed all the requery code, still having the same issue.

Any other suggestions?

Thanks,

gdunn59
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36719143
From what you are saying, *something* is moving the form back to the first record.
So, check for any filters as well.
0
 

Author Comment

by:gdunn59
ID: 36719591
I searched for the control that it comes going back to cboAssoc and don't see anywhere where it is setting the focus to that control.

I have uploaded a mock version of my database for review.  I'm obviously missing something, and any assistance would be greatly appreciated.

Thanks,

gdunn59

 Audit-Database--for-EE-.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36719695
Not sure why the tab order is odd in your form....?

My guess is that the issue is with the code on the lost focus event of the Comments field.
Why is this on this event and what it it's purpose?

If Me.Recordset.AbsolutePosition = Me.Recordset.RecordCount - 1 Then
    Forms!frmEmployee_Audits.SetFocus
    DoCmd.GoToControl "Audit_Notes"
Else
    'DoCmd.GoToRecord , , acNext
    'Me.Recordset.AbsolutePosition = 0
   
   
    'Me.cboAssoc.SetFocus
End If

0
 

Author Comment

by:gdunn59
ID: 36815869
boaq2000:

Because the user needs to be able to make changes to the 4 fields on the subform (cboAssoc, cboOpers, cboImpact and Comments), and then go back up to Audit_Notes on the main form.

The absolutePosition is checking if it is at the last record on the subform.

Also, I don't believe the code on the lost focus event of the Comments field is not the issue because I commented out all of that code and tried it again, and when I go to the second row of data on the subform and change the data in the field (cboAssoc), the cursor still moves back to the first record on the subform and sets the focus to the cboAssoc field.

Any other suggestions/clues?

Thanks,
gdunn
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Expert Comment

by:Bill Ross
ID: 36816086
Hi,

I downloaded the file but please define the work flow so I can see the issue.

Bill
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36816111
Bill,

I gotta run, I'll leave this to you...

;-)

Jeff
0
 

Author Comment

by:gdunn59
ID: 36816487
BillDenver,

I know that the error is happening after the following line of code on the subform control cboAssoc's AfterUpdate Event (I've commented everything out up to the line where the issue is happening:

    If cboAssoc = "N" Then
        Forms!frmEmployee_Audits.SetFocus
        DoCmd.GoToControl "cboAuditType"    
'        'Me.Painting = False
'        'Me.Dirty = False
        Parent.[frmQuality_Review_Subform].SetFocus -- THIS IS WHERE THE PROBLEM IS HAPPENING

'        'DoCmd.GoToControl "cboAssoc"
'        'DoCmd.GoToControl "cboOpers"
'        Audit_Score = Possible_Score
'        'cboOpers = "Y"
'    ElseIf cboAssoc = "Y" Then
'        Forms!frmEmployee_Audits.SetFocus
'        DoCmd.GoToControl "cboAuditType"
'        'Me.Painting = False
'        'Me.Dirty = False
'        Parent![frmQuality_Review_Subform].SetFocus
'        'DoCmd.GoToControl "cboAssoc"
'        'DoCmd.GoToControl "cboOpers"
'            cboAssoc = "Y"
'            cboOpers = "N"
'        Audit_Score = "0"
'    ElseIf cboAssoc = "N/A" Then
'        Audit_Score = "0"
'        Possible_Score = "0"
'    Else
'        MsgBox "Option Not Available.  Please choose another option."
    End If
0
 

Author Comment

by:gdunn59
ID: 36816538
BillDenver,

What happens is the user enters data on the main form filling out all forms up to the field "What is the Status of the Audit (Draft or Complete).  Once they answer this question from the drop down with "Completed", it shoots off an email and then the cursor moves to the Assoc (Y/N) field on the subform.  Then if the user changes the control Assoc (Y/N) they continue on to the Opers (Y/N) and Impact (Y/N) , and then fill in any Comments.  After leaving the "Comments" field, the cursor moves down to the next record in the subform and sets the focus to the Assoc (Y/N) field, which is what it should be doing.  But the problem is once a user is on the second line/record of the subform, and the user makes a selection from the drop down of the Assoc (Y/N) field, the cursor is automatically jumping back up to the first record of the subform and the focus is set on the Assoc (Y/N) field.  The cursor shouldn't be jumping back to the first record of the subform's Assoc (Y/N) field, and should stay on the current record (2nd, 3rd, etc.) that the user is on and allow them to make selections in the other fields (Assoc (Y/N), Opers (Y/N). Impact (Y/N) , and Comments.

All of this should repeat itself until it gets to the final record of the subform/recordset.

Hope this gives you a better understanding of what I am trying to accomplish.

Thanks,

gdunn59
0
 

Author Comment

by:gdunn59
ID: 36816543
BillDener,

I meant to say "filling out all fields", not "filling out all forms ".

0
 

Author Comment

by:gdunn59
ID: 36817439
I found this posting on EE that sounds exactly what my issue is, but I can't seem to get it to work with my scenario:

       08/16/06 05:29 PM, ID: 17330594

Any help would be greatly appreciated.

gdunn59

0
 

Author Comment

by:gdunn59
ID: 36817483
Here is the actual link for the posting that I found on EE that sounds exactly like the issue I am having:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21957185.html?sfQueryTermInfo=1+10+30+datasheet+field+form+move+specif


Thanks,
gdunn59
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 36817595
Hi,

Here's what wass happening.  When you move off a subform or set dirty=false the record gets saved.  Because of the form-subform binding this causes a form requery.  Note that the 'cboOpers = "Y" is actually changing the first record - not the record that it is on - in the subform.

Try the attached:
1. Set a PK on the ...info table (autonumber)
2. Unlinked the susbform and set the subform's recordsource to query parameters of the parent criteria.
3.  Uncommented everything

See if this is what you need.

Bill

Audit-Database--for-EE-Rev1.zip
0

Featured Post

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.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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

20 Experts available now in Live!

Get 1:1 Help Now