Subform Navigation buttons (Access 97 SR-2)

Topic

New Reply

Hello all,

I have a main form with several subforms. The subforms are placed on different pages on a tab control. I would like to create navigation buttons for the subform but reside them on the main form. (The benefit being that I only have to create the buttons once–instead of five time for five different subforms.) However, I’m having a hard time figuring out what to code in order to make it happen. I’m thinking something along these lines (pseudo-code):

Sub Command_Click()
Find out which tab as been selected
Use a method to find out the name of the subform on that page
Set the focus to the subform
DoCmd.GoTo Record,,acFirst
End Sub

Now I can think of many different ways to accomplish this, but I’m hoping for a more elegant solution like the above or better. Thanks in advance for your help.

Reply | Quote

Replies

Unless I’m missing something, wouldn’t just clicking on the appropriate tab take you where you want to go?

Reply | Quote

Hopefully, this post will clarify. The subforms are on the tabbed pages. I want to use navigation buttons to scroll through the RECORDS of the subform, not the tabs. I would like to create the navigation buttons on the main form to make navigation a little more user-friendly. (Some subforms will have numerous records, so when a user updates a field toward the bottom of the record set, the user may decide to go to the beginning. )Sorry if I confused you. And thanks!

Reply | Quote

I have found a solution that may work, but please feel free to give your opinion.

Sub cmdFirstRecord_Click()
Dim strSubName as String

strSubName=Screen.PreviousControl.Name

DoCmd.GoToControl strSubName
DoCmd.GoToRecord,,acFirst
End sub

I’m assuming the other navigation buttons will work as well. While I know that the previous control had to be a subform, this has not posed a probelm in the form’s design. The main form’s controls are locked and not enabled. So when the tab page is clicked, the focus is on the subform. Please respond if you have a better solution or comments. Thanks.

Reply | Quote

Here’s another approach: put your buttons on a subform and drop the subform onto the form when you need those buttons. I’ve attached a 97 sample database that demonstrates this approach.

Reply | Quote

thanks, Charlotte. I’ll take a look.

Reply | Quote

Thanks, Mark. I’ll examine both techniques. You guys are such a big help!

Reply | Quote

I like both ideas. Charlotte, thanks for the demo. Very Clever way of not having to recreate common command buttons over and over again. Mark, wonderful idea of using the subform name + number as an easy way to not hard code the names themselves. Brillant. Right now, I think I will try out Mark’s idea and maybe both of your ideas into my own database. Thanks, again.

Reply | Quote

I like both ideas. Charlotte, thanks for the demo. Very Clever way of not having to recreate common command buttons over and over again. Mark, wonderful idea of using the subform name + number as an easy way to not hard code the names themselves. Brillant. Right now, I think I will try out Mark’s idea and maybe both of your ideas into my own database. Thanks, again.

Reply | Quote

Thanks, Mark. I’ll examine both techniques. You guys are such a big help!

Reply | Quote

thanks, Charlotte. I’ll take a look.

Reply | Quote

Here’s another approach: put your buttons on a subform and drop the subform onto the form when you need those buttons. I’ve attached a 97 sample database that demonstrates this approach.

Reply | Quote

(Edited by MarkD on 07-Sep-04 23:14. Replaced attachment, fixed minor bug)

I don’t know that I’d want to rely on PreviousControl. If interested, see attached demo database (originally A2K format, converted to A97 format) that shows another approach. The main form (SubformDemo) has a tab control with four pages, each with subform (datasheet). (The tables used for subforms are all linked Northwind tables, on my system Northwind.mdb is located in C:Program FilesMicrosoft OfficeOfficeSamples folder, reset links using Lined table Manager if necessary.) There are navigation buttons for both tab control (smaller buttons on left) and for the currently displayed subform. Code used for record navigation:

Private Sub GoToRecordSubfrm(lngRecType As AcRecord)
On Error GoTo Err_Handler

Dim strMsg As String
Dim intPage As Integer

With Me
intPage = .TabCtl1.value
.Controls("Subform" & (intPage + 1)).SetFocus
DoCmd.GoToRecord , , lngRecType
End With

Exit_Sub:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2105 ' You can't go to specified record
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GO TO RECORD ERROR MSG"
Resume Exit_Sub
End Select

End Sub

Note that you only need one sub, the lngRecType parameter specifies whether to go to next record, previous record, etc (see acRecord Enum for the constant values). The command buttons call sub like this:

Private Sub cmdNewRec_Click()
GoToRecordSubfrm acNewRec
End Sub

There is also code to disable/enable the record & tab navigation buttons in same way the built-in navigation buttons are. Each subform simply calls a generic sub from main form like this:

Private Sub Form_Current()
Me.Parent.SubformOnCurrentEvent
End Sub

See demo for full details. One more note, to simplify things, I named the four subforms “Subform1”, “Subform2”, etc. You can still use meaningful names for the subforms (the form name) – just name the subform CONTROL as “Subform1”, etc. Otherwise you’ll have to be hard-coding the control names (there’s no law that says the subform itself and a subform control have to have the same name). I had to convert demo to A97 format, AFAIK should work OK in A97.

HTH

Reply | Quote

Hi:
This is a greet program to use the same navigation buttons for a form with different tab controls.
This works like a charm. Now, my concern is that “Is there a way to use similar buttons to “Save,
Undo, or delete records from different tabs on a form” – like the navigation buttons you’ve in your attachment?

Thanks in advance.
Satish

Reply | Quote

I never had much luck trying to do this with Save, Undo, or Delete Record buttons. It doesn’t make much sense to have “Save” button for subform records on main form because as soon as focus is moved from subform to main form, the subform record is automatically saved by Access. This behavior is by design since subforms are intended for working with records that are related by one or more fields with record on main form; problems can occur in some cases if related record not saved. Do not know of good way to prevent this. Likewise a generic “Undo” button won’t do much good, once you’ve moved focus to main form (by clicking button) the subform record has been already saved and the standard Undo command will have no effect – only the “Undo Saved Record” command (Edit menu) would reverse change. I would not recommend trying to defeat built-in behavior (unless you enjoy doing a lot of extra work) – for subforms that are updatable by user, it’s simpler to create custom toolbar or shortcut menu that includes only those commands from Edit menu, Records menu, & Form View toolbar necessary for user to add, edit, or delete records, along with event procedures, validation as necessary to prevent user from doing something stupid.

There are other issues when editing related records on both main form & subform. For example see MSKB 208833:

ACC2000: Unable to Undo or Rollback Main Form and Subform Changes

Also for explanation of why Access automatically saves record when moving from main form to subform (or vice versa) see MSKB 132033:

ACC: Main Form Record Edits Saved When Subform Gets Focus

Brief explanation: “If Microsoft Access did not save the main form record, it would be possible to create detail records that do not have a corresponding parent (main form) record. Creating detail records without a corresponding main form record would violate referential integrity rules.” See article for more info.

HTH

Reply | Quote

I never had much luck trying to do this with Save, Undo, or Delete Record buttons. It doesn’t make much sense to have “Save” button for subform records on main form because as soon as focus is moved from subform to main form, the subform record is automatically saved by Access. This behavior is by design since subforms are intended for working with records that are related by one or more fields with record on main form; problems can occur in some cases if related record not saved. Do not know of good way to prevent this. Likewise a generic “Undo” button won’t do much good, once you’ve moved focus to main form (by clicking button) the subform record has been already saved and the standard Undo command will have no effect – only the “Undo Saved Record” command (Edit menu) would reverse change. I would not recommend trying to defeat built-in behavior (unless you enjoy doing a lot of extra work) – for subforms that are updatable by user, it’s simpler to create custom toolbar or shortcut menu that includes only those commands from Edit menu, Records menu, & Form View toolbar necessary for user to add, edit, or delete records, along with event procedures, validation as necessary to prevent user from doing something stupid.

There are other issues when editing related records on both main form & subform. For example see MSKB 208833:

ACC2000: Unable to Undo or Rollback Main Form and Subform Changes

Also for explanation of why Access automatically saves record when moving from main form to subform (or vice versa) see MSKB 132033:

ACC: Main Form Record Edits Saved When Subform Gets Focus

Brief explanation: “If Microsoft Access did not save the main form record, it would be possible to create detail records that do not have a corresponding parent (main form) record. Creating detail records without a corresponding main form record would violate referential integrity rules.” See article for more info.

HTH

Reply | Quote

Hi:
This is a greet program to use the same navigation buttons for a form with different tab controls.
This works like a charm. Now, my concern is that “Is there a way to use similar buttons to “Save,
Undo, or delete records from different tabs on a form” – like the navigation buttons you’ve in your attachment?

Thanks in advance.
Satish

Reply | Quote

(Edited by MarkD on 07-Sep-04 23:14. Replaced attachment, fixed minor bug)

I don’t know that I’d want to rely on PreviousControl. If interested, see attached demo database (originally A2K format, converted to A97 format) that shows another approach. The main form (SubformDemo) has a tab control with four pages, each with subform (datasheet). (The tables used for subforms are all linked Northwind tables, on my system Northwind.mdb is located in C:Program FilesMicrosoft OfficeOfficeSamples folder, reset links using Lined table Manager if necessary.) There are navigation buttons for both tab control (smaller buttons on left) and for the currently displayed subform. Code used for record navigation:

Private Sub GoToRecordSubfrm(lngRecType As AcRecord)
On Error GoTo Err_Handler

Dim strMsg As String
Dim intPage As Integer

With Me
intPage = .TabCtl1.value
.Controls("Subform" & (intPage + 1)).SetFocus
DoCmd.GoToRecord , , lngRecType
End With

Exit_Sub:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2105 ' You can't go to specified record
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GO TO RECORD ERROR MSG"
Resume Exit_Sub
End Select

End Sub

Note that you only need one sub, the lngRecType parameter specifies whether to go to next record, previous record, etc (see acRecord Enum for the constant values). The command buttons call sub like this:

Private Sub cmdNewRec_Click()
GoToRecordSubfrm acNewRec
End Sub

There is also code to disable/enable the record & tab navigation buttons in same way the built-in navigation buttons are. Each subform simply calls a generic sub from main form like this:

Private Sub Form_Current()
Me.Parent.SubformOnCurrentEvent
End Sub

See demo for full details. One more note, to simplify things, I named the four subforms “Subform1”, “Subform2”, etc. You can still use meaningful names for the subforms (the form name) – just name the subform CONTROL as “Subform1”, etc. Otherwise you’ll have to be hard-coding the control names (there’s no law that says the subform itself and a subform control have to have the same name). I had to convert demo to A97 format, AFAIK should work OK in A97.

HTH

Reply | Quote

I have found a solution that may work, but please feel free to give your opinion.

Sub cmdFirstRecord_Click()
Dim strSubName as String

strSubName=Screen.PreviousControl.Name

DoCmd.GoToControl strSubName
DoCmd.GoToRecord,,acFirst
End sub

I’m assuming the other navigation buttons will work as well. While I know that the previous control had to be a subform, this has not posed a probelm in the form’s design. The main form’s controls are locked and not enabled. So when the tab page is clicked, the focus is on the subform. Please respond if you have a better solution or comments. Thanks.

Reply | Quote

Hopefully, this post will clarify. The subforms are on the tabbed pages. I want to use navigation buttons to scroll through the RECORDS of the subform, not the tabs. I would like to create the navigation buttons on the main form to make navigation a little more user-friendly. (Some subforms will have numerous records, so when a user updates a field toward the bottom of the record set, the user may decide to go to the beginning. )Sorry if I confused you. And thanks!

Reply | Quote

Unless I’m missing something, wouldn’t just clicking on the appropriate tab take you where you want to go?

Reply | Quote