Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: check field in subform from another subform

    hi there,

    im trying to check a field in a subform to see if it is empty or null.

    if Is null (Forms![Main_Sub_Items]![frm_Main_sub_system].Form![RecordCount]) then

    RecordCount = 1

    end if

    So im trying to check if RecordCount is empty and if it is, give it a value of 1

    sounds easy but not for me.

    thanks
    Marcus

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is RecordCount the name of the field you want to know if it's Null?
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for looking Sinndho,

    yes RecordCount is the field i need to check.

    regards
    Marcus

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Try:
    Code:
    If IsNull(Forms![Main_Sub_Items]![frm_Main_sub_system].Form![RecordCount]) Then
    
         Forms![Main_Sub_Items]![frm_Main_sub_system].Form!RecordCount = 1
    
    End If
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    hi there,

    I tried the code below but changed it slightly. The one thing i failed to mention is that txtboxRecordCount is a textbox where its control source is =Count([Line]) Line is a Number field in a table. So im just counting the number of entries.

    So when i run this code, even though txtboxRecordCount is empty it does not set intRecord_Counter to 1. It skips to the else line.

    Any ideas anyone?


    If IsNull(Forms![Main_Sub_Items]![frm_Main_sub_system].Form![txtboxRecordCount]) Then

    intRecord_Counter = 1

    else

    ' do something

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm not entirely sure if you can set the value of text box bound to an expression like that. I've certainly never tried!

    You might be better off picking up the subform's data source into a recordset and counting the records there. Alternatively, given my above thought about over-writing the value in a bound text box, try amending the formula as follows:
    Code:
    =Nz(Count([Line]), 0)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks weejas for your suggestion, but i get a run-time error 2427 "you entered an expression that has no value"

    would it be possible to show me how to pickup the subforms data source or point me in the right direction?

    thanks
    marcus

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a public property in the class module of the subform:
    Code:
    Public Property Get RowCount()
    
        Dim rst As DAO.Recordset
        
        Set rst = Me.RecordsetClone
        rst.MoveLast
        RowCount = rst.RecordCount
        rst.Close
        Set rst = Nothing
        
    End Property
    2. From the parent form (Frm_1), you can reference the RowCount property of the subform (SF_1); like this:
    Code:
    Me.Text_RowCount.Value = Me.SF_1.Form.RowCount
    Have a nice day!

  9. #9
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    To the rescue again, I don't know what I'd do without this site. Thanks to ya both
    Marcus

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

Posting Permissions

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