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

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
LVL 1
gdunn59Asked:
Who is Participating?
 
Bill RossCommented:
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
 
gdunn59Author Commented:
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
 
Bill RossCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gdunn59Author Commented:
Bill,

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

Any other suggestions?

Thanks,

gdunn59
0
 
Jeffrey CoachmanMIS LiasonCommented:
From what you are saying, *something* is moving the form back to the first record.
So, check for any filters as well.
0
 
gdunn59Author Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
gdunn59Author Commented:
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
 
Bill RossCommented:
Hi,

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

Bill
0
 
Jeffrey CoachmanMIS LiasonCommented:
Bill,

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

;-)

Jeff
0
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
BillDener,

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

0
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.