locked
how to go to next form record, not subform record RRS feed

  • Question

  • I've got a couple forms with subforms, 1 to many relalationships.  The number of records in the subforms vary, sometimes greatly.  One example is a project to deploy servers.  There's 1 project, with x number of servers.  I want a button that will go to the next project, not the next server, but every time I create a button using the wizard no matter where I place the button it always only controls the subform records.  How to I get it to focus on the main form's table?
    Wednesday, March 20, 2013 2:40 PM

Answers

  • If you put the button on the main form, the wizard should do what you want.  If you really need the button in the subform, then the code might look like:

       With Parent.RecordsetClone
          .MoveNext
          If .EOF Then
             Beep
          Else
             Parent.Bookmark = .Bookmark
          End If
       Emd With

    Personally, I simply use the record navigation bar at the bottom of the main form instead of creating extra buttons for this purpose.

    • Marked as answer by Dummy yoyo Wednesday, March 27, 2013 10:29 AM
    Wednesday, March 20, 2013 10:16 PM

All replies

  • Just to make sure we are on the same page -- relationships occur between tables, so I am guessing you mean you have related tables that your subforms are based on.  Next -- it sounds like you are displaying textboxes in your subforms and the subforms are based on different tables.  So when you move to one record in one subform -- then you want the other subform to be on a related record.  This is what it sounds like.

    If this is the case -- then having subforms to display data in textboxes from different tables is kind of redundant.  If you know how to program with Visual Basic for Applications (VBA) you can programmatically display the desired data.  I am just guessing (respectfully) that you are probably not a VBA coder at this time and are trying to get by with the wizards.  That would have some limitations for your purposes as you are experiencing. 

    Ideally, you would have unbound textboxes (textboxes not linked to tables) on one form and a button that selects the desired records using a combination of DAO and Sql code.  Note: subforms are mainly used to display datasheet view data (in table form).  Here is a sample of DAO and sql code to navigate your tables:

    Here you would have textboxes named as follows txt1, txt2, txt3, txt4, txt5, ...
    This is referred to as a Control array and you would loop through this control array and the recordset fields

    Option Compare Database
    Option Explicit

    Dim RS As DAO.Recordset  '--this is a Form level declaration so that the Recordset Object stays open for the life of the form


    Private Sub Form_Load()
       Set RS = CurrentDb.OpenRecordset("tbl1")
       RS.MoveLast
       RS.MoveFirst
    End Sub

    Private Sub cmdMoveNext_Click()

       Dim i As Integer
       For i = 1 To RS.Fields.Count - 1 '--note: first field in RS would be an ID field - ordinal # 0
          Me.Controls("txt" & i) = RS(i)
       Next
      
       RS.MoveNext
      
    End Sub

    '--and you could have a back button that would move to RS.MovePrevious


    Rich P





    • Edited by Rich P123 Wednesday, March 20, 2013 4:31 PM
    Wednesday, March 20, 2013 4:00 PM
  • Carl_S_S,

    It possible you use subforms to accept/create new records but this has to be done through VBA and/or couple of Queries/SQL statements. I dont think you could accomplish this using wizard.

    One other way to can have your subform to display server information base on selected project is to alter the recordset property of the subforms through a button Click event in your main form as follows:

    Private Sub btnNextProject_Click()

    Forms!frmMainForm.frmSubForm.Form.RecordSource="Select * From tblSubFormTable Where ProjectId=" & Me.txtProjectId

    Here I assumed you have a textbox named txtProjectId. You can also use a Combo box and the above code will be in the OnChange or (preferably) LostFocus event or use List box and put it in OnClick event (just change Me.txtProjectId to combo/list box name).

    This will always refer the recordsource property of the subform to display record(s) that match your condition.


    New_Ibro

    Wednesday, March 20, 2013 4:58 PM
  • Thanks the replies.  I don't think I explained myself clearly enough. 

    I have 2 tables, tblProjects and tblBuildsheet, they're setup with a 1 to many relationship with the project number being the common link (1 project, many buildsheets).

    I have a query that filters that data and shows it in the form and subform qryBuildsheet.  I have a 2 forms fmProjectInfo and fmBuildInfo ( the subform).  fmProjectinfo displays info from tblProjects and fmBuildInfo displays info from tblBuildsheet. One project may have 2 servers to build, or may have 100.  So my form shows the project info at the top and then shows the subform with all the servers that need to be built at the bottom.  I want a Next and Previous button that will cycle thru Projects, but the wizzard only allows me to cycle thru the servers, which doesn't help. 

    I know VBScript pretty well, but am a newbie when it comes to VBA, but because of the simularities I'm catching on.

    What I know is that the wizard enters this command in VBA "DoCmd.GoToRecord , , acNext" but the focus of the next record is on tblBuildSheet, I want it to be on tblProjects.  The project numbers may not be consecutive, depending on filters.  How to I get Access to focus on the Project, rather than the build?



    • Edited by Carl_S_S Wednesday, March 20, 2013 7:09 PM
    Wednesday, March 20, 2013 6:59 PM
  • If you put the button on the main form, the wizard should do what you want.  If you really need the button in the subform, then the code might look like:

       With Parent.RecordsetClone
          .MoveNext
          If .EOF Then
             Beep
          Else
             Parent.Bookmark = .Bookmark
          End If
       Emd With

    Personally, I simply use the record navigation bar at the bottom of the main form instead of creating extra buttons for this purpose.

    • Marked as answer by Dummy yoyo Wednesday, March 27, 2013 10:29 AM
    Wednesday, March 20, 2013 10:16 PM