Results 1 to 11 of 11
  1. #1
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50

    Subform will not open to last record


    I have a Subform that I want to open to the last record but I keep getting a warning when the macro runs to open the main form holds the subform. The warning tells me (basically) that the subform is not open.
    I also want to run a calculation on the circled field in the subform that uses the circled field in the main form but I can't make it work.
    Both the form and the subform are designed from tables (not queries) however the tables have a common field relationship.

    Detail: a person's height does not change but their weight can. The subform's table holds data records from subsequent measurements of weight and blood pressure, etc. by date of visit.Click image for larger version. 

Name:	clinical data form and subform.png 
Views:	14 
Size:	41.9 KB 
ID:	25804




    The height field is in the main form and the subform holds a weight field. I am trying to run a macro that calculates BMI using the person's height and a variable weight.

    Any help would be greatly appreciated.

    Mark

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    Your SubForm only shows one record. Do you have the DataEntry property set to YES? If so, it will only show NEW records.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    It is set to NO. However, when I set to YES I only can see one new record. I can't scroll through other records. If I could just get it to show the latest record that would be great. It always opens to the very first record.

    thanks for your response RG

  4. #4
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    I forgot to mention that this is just a screen shot of my form. Other records will show multiple records in the subform but it still pulls up to the first record

  5. #5
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    See if this does what you want:

    In the Main Form:

    Code:
    Private Sub Form_Current()
      SubFormControlName.SetFocus
    End Sub
    
    Private Sub SubFormControlName_Enter()
      DoCmd.GoToRecord, , acLast
    End Sub

    Note that SubFormControlName may or may not be the same as the name of the Form the Subform is based on.

    Linq ;0)>
    Last edited by Missinglinq; 09-14-2016 at 03:55 PM.
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    thank you but I'm not sure where to put this code

  7. #7
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Not working for me.
    Here is my code
    Private Sub Form_Frm_ClinicalData()
    SubFrm_ClinicalFUData.SetFocus
    End Sub
    Private Sub SubFrm_ClinicalFUData_Enter()
    DoCmd.GoToRecord , , acLast
    End Sub

  8. #8
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    The first code needs to be in the Form_Current event of the Main Form, as shown...not in the Form_Frm_ClinicalData event, as you've got.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    How are you linking the form and subform? In the subform query criteria to point to the mainform key field or using LinkMaster/LinkChild properties on subform?

    Or maybe use DLast function to get that value to use in your subform

    DLast("Height", "InitialEncounterTable", "PatientID = " & me.PatientID)

  10. #10
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    I think I'm in over my head here fellas.
    I am clearly a novice user.

    I can say that my form and subform are linked using a Master field link.
    I wouldn't even know HOW to use DLast.

    I keep trying the code (in different ways) that Missinglinq suggested but I keep getting runtime errors. Here's a screen shot.Click image for larger version. 

Name:	2016-09-14_14-23-45.png 
Views:	8 
Size:	11.4 KB 
ID:	25811

  11. #11
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    What errors are you getting?

    Are you sure the Subform Control is named SubFrm_ClinicalFUData?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-17-2014, 03:31 PM
  2. Replies: 8
    Last Post: 08-09-2013, 09:52 AM
  3. Need to have subform open to the last record
    By PirateGenie in forum Access
    Replies: 1
    Last Post: 03-22-2013, 09:47 AM
  4. Open record from a subform
    By pratim09 in forum Forms
    Replies: 1
    Last Post: 04-28-2011, 12:01 AM
  5. Replies: 1
    Last Post: 11-30-2010, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums