Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28

    Unanswered: Field in Mainform equals last record field in SubForm

    Hi Everyone,

    I was looking online and couldn't find an answer to this question.

    I have a form with a subform. I would like a field in the main form to be equal to the field of the last record in its subform, or blank if there are no records.

    Any clue on how to do this?

    Would really appreciate it!

    Cheers,

    Randa

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not specify when the control in the main form must be updated, so I'll use the Form_Current event in this example.
    Code:
    Private Sub Form_Current()
    '
    ' SF_Table1 is the name of the control of type subform/subreport.
    ' Note: The name of this control can be different from the name
    '  ----  of the form it displays.
    '
    ' Text_FromSubForm is the name of the control of type Textbox that
    ' must display the value of the first field of the last record in
    ' the subform (Fields(0)).
    ' Note: To display another field, change Fields(0) to Fields(n)
    ' ----  where n is the ordinal number of the field in the subform
    '       with Fields(0) being the first (leftmost) field.
    '
        Dim rst As DAO.Recordset
        
        Set rst = Me.SF_Table1.Form.RecordsetClone
        rst.MoveLast
        Me.Text_FromSubForm.Value = rst.Fields(0).Value
        rst.Close
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Hi Sinddho,

    Thanks for your quick reply!

    However, I am getting 0.

    I executed the code as you mentioned:

    Dim rst As DAO.Recordset

    Set rst = Me.RevisionTSubform1.Form.RecordsetClone
    rst.MoveLast
    Me.DateLC.Value = rst.Fields(1).Value
    rst.Close
    Set rst = Nothing

    but the date returned is 05.01.1900, which means a blank is returned...

    Is there a way to call to the field name (DateCreated) instead of Field(1)?

    Oh, just one more piece of info.. In design view, my subform is not in the regular table form.. It's a subform created through a form wizard.. Don't think this has any importance in our discussions but you never know.

  4. #4
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Hi Sinddho!

    Forget it, it was Fileds(3). Thanks for saving my life yet again!

    Cheers,

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Hi Sinndho,

    For some reason I keep on facing the same problem. I googled it and my post came up! I read most of it before I realized it was me .

    The issue this time is the following

    Let's say my last row of the subform looks like this: 1 14000 12 17.5 6.6 $747,016.00

    I want to record the $747,016.00 in the main form.

    I used your code but with Me.xxxx.Value = rst.Fields(5).Value. The value recorded is $1.00.

    If I change from 5 to 4, I get a $6.00. And to 3, I get a $17.00.

    So basically it's recording the int values of that column. However, for a large number like 747,016.00, it's recording 1... Do you know how I can solve this problem?

    Much much appreciated!!!

  7. #7
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Ok, figured it out!! It's the actual field number of the whole table not just the fields in the subform!

    I keep on having conversations with myself in this post.

    Cheers!

Posting Permissions

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