Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65

    #Error when Subform blank

    Hi All,



    I have a field in a form that updates based on a subform. When I click to enter a new record having a blank form, this field shows #error which is corrected as soon as the subform updates then works fine. I understand why the error happens since there is nothing to update but I do not want the end user to see this thinking there may be a problem with the form. Are there any work arounds here maybe using the Before Update function? I've tried some if statements using NZ but they do not seem to work.

    Thanks in advance,

    Eric

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You could try to use a check in the Current event of the form to look for records in the subform:
    Code:
    if Me.sfrmYourSubform.Recordset.RecordCount=0 then
       Me.txtYourField=Null
    Else
       Me.txtYourField=me.sfrmYourSubform.Form.Controls("txtControlOnTheSubform")
    end if
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Paul, worked for my numeric field.

    Hi Vlad, I have another field that is not numeric and tried your code and got a "Method or data member not found" stuck on Recordset, first row. Any ideas? I figured it was a misspelling of the subform but it is correct.

    Thanks,

    Eric

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would just change the test to:

    If Len(testvalue & vbNullString) = 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If Paul's suggestion doesn't work please try:
    if Me.sfrmYourSubform.Form.Recordset.RecordCount=0 then
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Paul,

    Are you suggesting creating a new code routine or adapting the one you previously sent? The code you sent before does work well for the number fields so definitely would like to keep that. I am a novice at coding as you can tell....

    Thanks,

    Eric

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My thought was to test it adapting the first, alternatively trying it as a second function. I haven't tested, so it's a shot in the dark.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad,

    I updated based on your last comment and got this error: "You can't assign a value to the object" on the second row where Me.txtYourField=Null. Hovering over shows Null=Null. Any thoughts?

    Thanks
    Eric



    if Me.sfrmYourSubform.Recordset.RecordCount=0 then
    Me.txtYourField=Null
    Else
    Me.txtYourField=me.sfrmYourSubform.Form.Controls(" txtControlOnTheSubform")
    end if

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Of course because it is bound to an expression, sorry about that, please change that line like this:
    Code:
    if Me.sfrmYourSubform.Recordset.RecordCount=0 then
          Me.txtYourField.ControlSource="" 'makes the control unbound
    Else
          Me.txtYourField=me.sfrmYourSubform.Form.Controls(" txtControlOnTheSubform") 'or you can try to set the .ControlSource property again but without the Me. part - use what you currently have
    end if
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad...

    We are getting close! No error when opening the form but it doesn't update when there is data in the subform so the issue is after my Else... I wasn't sure if the control on the subform had to be in quotes so I tried it both ways and still didn't work. This is what I show after Else:

    Me.BusUnit = Qry_CCAforCCAAdjSubform.Form.ControlSource = "BusinessUnit" and

    Me.BusUnit = me.Qry_CCAforCCAAdjSubform.Form.Control("BusinessU nit")

    Appreciate the help,

    Eric

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Eric,

    Would you please try this:

    Code:
    Me.BusUnit = Me.Qry_CCAforCCAAdjSubform.Form.Controls("BusinessUnit")
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad,

    Still not working. I am assuming that the last piece in " " is the field name from the subform correct?

    Thanks,

    Eric

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Eric,
    Just tested this and it works for me:
    Code:
    If Me.Qry_CCAforCCAAdjSubform.Form.Recordset.RecordCount = 0 Then
       Me.BusUnit.ControlSource = ""
    Else
        Me.BusUnit.ControlSource = "=[Qry_CCAforCCAAdjSubform]![BusinessUnit]"
    End If
    Please let me know!

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad,

    I hate to report it still does not work for me... The currency code from Paul is working perfectly but for some reason text is not updating. If you are out of ideas, I will just move on. It's nothing more than just annoying. I appreciate you trying to help.

    Eric

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2019, 10:20 AM
  2. allow blank record in subform
    By smg in forum Forms
    Replies: 3
    Last Post: 05-17-2019, 11:50 AM
  3. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  4. Subform printing out blank?
    By tonygg in forum Access
    Replies: 6
    Last Post: 04-27-2015, 03:09 AM
  5. load blank subform
    By slimjen in forum Forms
    Replies: 18
    Last Post: 07-02-2014, 09:51 PM

Posting Permissions

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