Solved

MS Access form tab to next page on exit from last control

Posted on 2007-12-06
19
1,736 Views
Last Modified: 2013-12-25
Can someone please provide a code example for tabbing to the next page of a form from the last control? I know it has something to do with the On Exit command but have no idea how to code it. Thanks.
0
Comment
Question by:devabarry
  • 9
  • 6
  • 4
19 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
No code, in Form Design, do a right-click:Properties on the form, Other tab, and set the Cycle property to All Records.

This way, when you tab off the control with the largest TabOrder value, the form will move to the next record.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
otherwise...

docmd.GoToRecord acDataForm, ,acNext
0
 

Author Comment

by:devabarry
Comment Utility
thanks. I didn't see a Cycle property on the Other tab with either focus on form or on specific control. As for the code, I get the following error msg: This action requires an Object name argument. I presume this means the page name? Where would this go?
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>I didn't see a Cycle property on the Other tab with either focus on form or on specific control.
Make sure the upper left caption of the Properties dialog reads 'Form'

Which version of Access are you using?
0
 

Author Comment

by:devabarry
Comment Utility
2003. I see now this is a little more involved. When right-clicking on the form, I get the tab list of controls that span all the pages, i.e. ID and date controls and the Add, Save, Undo, Delete and Find controls. But the tab order for individual fields on each page is accessed by highlighting a field and right-clicking. The way it is currently set up is to begin on the ID control then tab up to the page tab, after which it cycles through the individual fields, but then after the last field returns to ID. What I want it to do is to cycle to the first control on the next page after each of the last fields, and ideally return to the Add, Save, etc controls after the last page (i.e. save the record after all fields entered). So, between the two tab sections, how do I make them do what I want them to do? Is this too complicated? Perhaps it's set up in the wrong way from a design perspective? Thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
devabarry,

First, re- read jimhorn's post and make *sure* you are in the right spot with regard to the Properties box.



I think you statement:
<tabbing to the next page of a form from the last control? >
... is a little confusing

A form can have separate "Pages" (for the same record) separated by a page break.
So pressuming your tab order is set correctly, you *should* move to the next Page automatically, when you tab out of the last control on the current page.

You can also have a Tabbed control *ON* a form that has *pages*.

Or do you have something different altogether?

Please explain in detail
Thanks!
:)

JeffCoachman
0
 

Author Comment

by:devabarry
Comment Utility
Yes I think I am in the right spot in the properties box.  by <tabbing to the next page of a form from the last control? > I meant the last control from the previous page. I have multiple pages (page tabs) and I want the first control on the next page to become active after the On Exit from the last tab of previous page. My dilemma is that I essentially have two sets of tab orderings: the tabs that are for the controls that appear across all pages (ID, date, Add, Save, Undo, etc), and the tab order for the individual fields on pages. As it is now set up, the focus is first set on the ID, then date, then the top of the page (all part of the 'all pages' tab order), then the tab skips into the individual page fields. On Exit from the last field of an individual page sends the focus back to the ID and date fields, and then back through the individual page fields, until on the second go-round it goes to the Add, Delete, etc controls, after which I get a 'Record Saved' popup. I don't want the record to save until all pages have been cycled through, i.e. I want the On Exit from the last control of the last page to cycle down to the Add control, but now before. Hope all this makes sense!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
devabarry,

So again, Can I presume you are using a "Tabbed Control" on a from?
Is this correct?

If you have a Tabbed control on a form, you do indeed have two tab orders.
One for the Form itself.
Another for the Fields in the tabbed control.

The <controls that appear across all pages (ID, date, Add, Save, Undo, etc)>
...should be on the form itself.
Correct?

I presume also that your current Tab order is the way you want it.
But you just don't want the "Record Saved" popup to appear untill All the filelds on the tabbed control are cycled through.
Correct?

You need to figure out what triggers this "Record Saved" Popup, and what it actually does.
AFAICT, this is not a message that is native to Access.

I think this code is causing your issue.
Can you post all the code on the Form here, so we can take a look?

Thanks

JeffCoachman

0
 

Author Comment

by:devabarry
Comment Utility
Yes, I guess I am using a tabbed control. I didn't know that. Yes, ID, date, etc are on form itself. Correct on no save until all controls cycled through. Here's the code:

Option Compare Database

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click


    DoCmd.GoToRecord , , acNewRec
    Participant_ID.SetFocus
   
Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
   
End Sub



Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Click:
    Exit Sub

Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click
   
End Sub
Private Sub Command213_Click()
On Error GoTo Err_Command213_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command213_Click:
    Exit Sub

Err_Command213_Click:
    MsgBox Err.Description
    Resume Exit_Command213_Click
   
End Sub
Private Sub Command214_Click()
On Error GoTo Err_Command214_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    MsgBox "Record Saved"
Exit_Command214_Click:
    Exit Sub

Err_Command214_Click:
    MsgBox Err.Description
    Resume Exit_Command214_Click
   
End Sub
Private Sub Combo215_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "Participant_ID = " & str(Nz(Me![Combo215], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub Add_Record_Click()

End Sub

Private Sub Currently_in_house_Exit(Cancel As Integer)

End Sub

Private Sub Form_AfterUpdate()
      MsgBox "Record Saved"
End Sub

Private Sub Form_Current()
    Combo246 = Participant_ID
End Sub
Private Sub Command217_Click()
On Error GoTo Err_Command217_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command217_Click:
    Exit Sub

Err_Command217_Click:
    MsgBox Err.Description
    Resume Exit_Command217_Click
   
End Sub
Private Sub Command249_Click()
On Error GoTo Err_Command249_Click


    DoCmd.GoToRecord , , acNewRec
    Participant_ID.SetFocus
Exit_Command249_Click:
    Exit Sub

Err_Command249_Click:
    MsgBox Err.Description
    Resume Exit_Command249_Click
   
End Sub
Private Sub Command250_Click()
On Error GoTo Err_Command250_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_Command250_Click:
    Exit Sub

Err_Command250_Click:
    MsgBox Err.Description
    Resume Exit_Command250_Click
   
End Sub
Private Sub Command251_Click()
On Error GoTo Err_Command251_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command251_Click:
    Exit Sub

Err_Command251_Click:
    MsgBox Err.Description
    Resume Exit_Command251_Click
   
End Sub
Private Sub Command252_Click()
On Error GoTo Err_Command252_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command252_Click:
    Exit Sub

Err_Command252_Click:
    MsgBox Err.Description
    Resume Exit_Command252_Click
   
End Sub
Private Sub Combo246_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "Participant_ID = " & str(Nz(Me![Combo246], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command248_Click()
On Error GoTo Err_Command248_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command248_Click:
    Exit Sub

Err_Command248_Click:
    MsgBox Err.Description
    Resume Exit_Command248_Click
   
End Sub
Private Sub Command254_Click()
On Error GoTo Err_Command254_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command254_Click:
    Exit Sub

Err_Command254_Click:
    MsgBox Err.Description
    Resume Exit_Command254_Click
   
End Sub

Private Sub Text352_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Text508_Dirty(Cancel As Integer)

End Sub

Private Sub RMR_Date_Exit(Cancel As Integer)

End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
devabarry,

OK, it's a little bit clearer now.

I had already written my code to move to the next sheet tab when I tested.
So , yes you are correct, tabbing you would go to the form , then the first tab, then the record will cahnge and you will be back on the form.

So you should put code on the "LostFocus" event of each of the last fields on each of the tabs.

The code will look something like this:
Sub YourLastField_LostFocus()
    Me.YourTabbedControlName.Pages("YourNextTab").setfocus
End sub

Do not put this code on the Last Tab, because obviously, there are no more tabs to go to.
This should get you to tab through all the fields on all the tabs.


Now about that "Save" Button.

The "Record Saved" message code is on the Form AfterUpdate event.

Whey you cycle through all the fields and make at least one change, this event fires.
There is nothing you can do to stop it.
Just remember, Access will save the record on the After Update event all the time anyway, so there is no real need to have a "Save" button.

There are instances where you would need a Save button. Like if you had a lot of fields and you wanted to save every once in a while.
Or if you had an unbound form and collected all the Field data on the form and then "Saved" the record to a table (Somewhat like the "submit" button on some websites)

Anyway...you can now tab through all the fields on all the tabs, then Save, if you like.

Give this a try and post back here if you have any issues.

JeffCoachman
0
 

Author Comment

by:devabarry
Comment Utility
Thanks Jeff. I'm getting the error msg: "Compile Error: Argument not optional" with the following code:

Private Sub Currently_in_house_LostFocus()
Me.DefaultControl("Household").SetFocus

End Sub

when this error is thrown the "DefaultControl" is highlighted on the VB Editor page. I'm pretty sure DefaultControl is the name of my tabbed control, but how do I know for sure? Household is the name of the next tab page I want to set focus to. Thanks.
0
 

Author Comment

by:devabarry
Comment Utility
Correction, the code example is:

Private Sub Currently_in_house_LostFocus()
Me.DefaultControl.Pages("Household").setFocus

End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
devabarry,

The code I gave you was:
Sub YourLastField_LostFocus()
    Me.YourTabbedControlName.Pages("YourNextTab").setfocus
End sub

But yours somehow has: Me.DefaultControl?
:O
    Private Sub Currently_in_house_LostFocus()
        Me.DefaultControl.Pages("Household").setFocus
    End Sub

Correct the code and see if it works.
;)



In any event, just to be sure:
A Sheet tab has both a Name Property and a Caption Property.
The name in quotes should be the *name* of the sheet tab, (not the caption)

For example,
The Caption is:  Household
But the Name is:  Page2

So it would be something like this:
Private Sub Currently_in_house_LostFocus()
    Me.YourTabbedControlName.Pages("Page2").setfocus
End Sub

Hope this clears things up!
:)

JeffCoachman
0
 

Author Comment

by:devabarry
Comment Utility
Hi Jeff, I had thought that DefaultControl was the name of my tab control. It comes up in the dropdown list and I don't know what other way to determine the name. I don't think you mean to say that "YourTabbedControlName" is a system variable or some such thing? In any case I tried it and it is unrecognized. I know this is very simple but I'm stuck on figuring out how to identify the tab control name because I didn't create the names and don't know how they are identified (I don't see it in the tab control properties). Thanks.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
devabarry,

No problem.
;)

Here is a screen-shot:
https://filedb.experts-exchange.com/incoming/ee-stuff/6106-Control-Name-in-properties-box.zip

So in my screenshot, the tabbed control's name is:  TabCtl0

Inserted into my code, this would be:
Sub YourLastField_LostFocus()
    Me.TabCtl0.Pages("YourNextTab").setfocus
End sub

JeffCoachman

0
 

Author Comment

by:devabarry
Comment Utility
Thanks Jeff!!!! That's it. I'm sorry you had to spell it out in such kindergarten fashion, it's just that I have so many tabs on my tab control that I didn't even realize I had some blank space to click on (until I scrolled to the end). A thick moment on my part but nonetheless I appreciate your patience and guidance. I have learned a lot.

Cheers,
Barry
0
 

Author Closing Comment

by:devabarry
Comment Utility
thanks again Jeff :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
devabarry,

No problem!
:)

Tabbed controls are tricky for that reason... If you right-click on the wrong spot, you might get the "Page", not the Tabbed control itself.

As far as patience goes...
Years ago someone once took the time to help me... so...

Happy Holidays!

JeffCoachman
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>As far as patience goes...  Years ago someone once took the time to help me... so...
I vaguely remember you sent them a case of beer for it too.   That would seem appropriate here as well.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

17 Experts available now in Live!

Get 1:1 Help Now