Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform Navigation buttons (Access 97 SR-2)

    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.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    Unless I'm missing something, wouldn't just clicking on the appropriate tab take you where you want to go?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    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!

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform Navigation buttons (Access 97 SR-2)

    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.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    <P ID="edit" class=small>(Edited by MarkD on 07-Sep-04 23:14. Replaced attachment, fixed minor bug)</P>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:

    <code>Private Sub GoToRecordSubfrm(lngRecType As AcRecord)</code>
    <code> On Error GoTo Err_Handler</code>

    <code> Dim strMsg As String</code>
    <code> Dim intPage As Integer</code>

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

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

    <code>End Sub</code>

    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:

    <code>Private Sub cmdNewRec_Click()</code>
    <code> GoToRecordSubfrm acNewRec</code>
    <code>End Sub</code>

    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:

    <code>Private Sub Form_Current()</code>
    <code> Me.Parent.SubformOnCurrentEvent</code>
    <code>End Sub</code>

    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

  7. #7
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    thanks, Charlotte. I'll take a look. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  8. #8
    New Lounger
    Join Date
    Sep 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    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

  9. #9
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

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

  10. #10
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    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.

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Subform Navigation buttons (Access 97 SR-2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •