Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2017
    Posts
    1,673

    The reference to control in parent form doesn't work!

    Hi!



    My app has an unbound Main form. On it is a continuous form fProducts, and an unbound control txtCurrProd (in subform container sfProducts). The value of txtCurrProduct is set equal to Product Code of active row in fProducts by Current event of fProduct.

    On main form is also a subform sfProdComponents with fProdComponents as source, which is linked with unbound control txtCurrProduct on Main form (Link Master Fields = "txtCurrProd", Link Child Fields = "ProdArtNo". This subform displays a list of components for active product in Products form.

    In header of fProdComponents I have an unbound text box with formula to display a text, depending on active product on Products form, and on existence of componets for this product. I got the formula working when product has components, but all variants I tried so long either return nothing when the product has no components, or causes the text box to return an error.

    The example of formula which I tried as last and which works when there are components
    Code:
    =IIf(Nz(Forms!fMain!txtCurrProd;"")="";"No components registered";"Components of product " & Nz([txtProdArtNo];""))
    Has someone an idea how to make it to work?
    Thanks in advance!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    It's a bit problematic! BE is a SQL Server DB, and a FE doesn't work without it! The only way I see to post it is to build a dummy DB from scratch! (And current FE is ~14MB, I don't think It will upload even when zipped! So simply making a copy and replace links with tables is not enough - I have to remove all other features to reduce it!)

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by ArviLaanemets View Post
    It's a bit problematic! BE is a SQL Server DB, and a FE doesn't work without it! The only way I see to post it is to build a dummy DB from scratch! (And current FE is ~14MB, I don't think It will upload even when zipped! So simply making a copy and replace links with tables is not enough - I have to remove all other features to reduce it!)
    All we would require would be the problematic form(s) and the table/query used with the form
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Check the recordcount of the components form?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    An example is here.

    The example doesn't work exactly in same way as actual app - probably because example tables are Access tables instead of linked SQL Database tables. When product doesn't have components, then the example's subform displays empty controls and the unbbound control in header displays the text meant for case where there are components for active product (minus product code). In actual app, when this is the case then in subform only headers are visible, and the unbound control displays nothing.

    To Welshagsman: I hope there is a way to get it using only a formula. To check recordcount I need to crate an event. When this is the only way, I better leave it as it is - the app has enough of coding without it
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, I suspect you could just use =MyFunction() just as you have with the above formulae?

    Version is too late for me, so I will bow out.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    [Forms]![fMain]![txtCurrProd] is always populated, so it is never null except when selecting a new line

    And when there are no components, txtProdArtNo is nothing, not null

    try

    =IIf(Nz([Forms]![fMain]![txtCurrProd],"")="","No components registered","Components of product " & Nz([Forms]![fMain]![txtCurrProd],""))

    or



    However the No components registered message is misleading - it will only appear if a product has not been selected. A more appropriate message might be 'No product selected'

    As Welshman suggests, perhaps you need code around the form recordcount

    perhaps a function along these lines

    Code:
    Private Function rCount() As Long
    
    
        rCount = Me.RecordsetClone.RecordCount
    
    
    End Function
    and your controlsource

    =IIf(rcount()=0,"No components registered","Components of product " & Nz([parent].[txtCurrProd],""))

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

Similar Threads

  1. Replies: 5
    Last Post: 01-21-2018, 04:06 AM
  2. Replies: 1
    Last Post: 10-06-2015, 06:50 AM
  3. Replies: 6
    Last Post: 12-13-2013, 12:38 PM
  4. Reference to a query from a form does not work
    By giladweil in forum Access
    Replies: 3
    Last Post: 02-28-2011, 06:38 AM
  5. Active X - Web Browser control doesn't work
    By forstatd in forum Reports
    Replies: 1
    Last Post: 06-02-2010, 10:56 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