Results 1 to 5 of 5
  1. #1
    PCJunky is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2

    Unable to reference subform object in form using vba code

    Hi,

    I find myself writing a simple database in access for recording customers orders, just two tables, customers and orders in a one to many relationship, its about 30 years since I last coded in access and about 15 since I last wrote any vba code so there is some dusting off of the memory and grey matter here so apologies if my terminology is off.

    I have created the db, tables, relationship and key fields and used the access wizard to build a form around the customers with a subform for the orders associated with those customers, which all works fine.

    I've managed to code a better search for customers in vba which works well, and what I would like to do now is have a toggle or an on and off button filter to show or hide completed orders in the subform/orders table.

    Looking at examples online I have tried building a filter and applying it to the subform but it fails, I have also tried directly addressing the subform in code to filter it but nothing is currently working for me, I think my problem is that all the examples I look at refer the the subform as a subform i.e. a form view of the table however looking at the way that the access form wizard has created the subform it looks more like a directly embeded table object than a form and the form object is called "Table.CusotmerOrders" and not "Subform or Form.CustomerOrders" which is what I am expecting based on the examples I have found online.

    So the main form is called CustomerOrderEntry and the embeded object with the orders table in is called Table.CustomerOrders with the actual table being CustomerOrders, to refer to this I have tried the following:

    The Field is called Completed and is a yes/no value

    Forms!CustomerOrderEntry.CustomerOrders.Table.Filt er = "[Completed]= " & "No"


    Forms!CustomerOrderEntry.CustomerOrders.Table.Filt erOn = True



    So set the filter for the form then apply it, however it returns an error and halts the code so obviously I am missing something due to my lack of experience of current access/vba.

    Could anyone point me in the right direction or point out what is probably an obvious fault with my code/approach?

    Kind regards

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    See if this helps?

    The key is the subControl name, NOT the object it contains, form or report?

    Also Yes/No fields are True or False ?

    http://access.mvps.org/access/forms/frm0031.htm
    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

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a new form bound (call it sfrmCustomerOrders) to the CustomerOrders table, set its properties to show as a datasheet only and replace the one you currently have in the main form with the new one.

    To apply the filter you would use something like this:

    Code:
    Forms!CustomerOrderEntry.sfrmCustomerOrders.Form.Filter= "[Completed]= False"
    Forms!CustomerOrderEntry.sfrmCustomerOrders.Form.FilterOn=True
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    PCJunky is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2
    Thank you!

    I realised the 'subform' as access had created it in the wizard was in fact straight up just an embedded table but thought I could work around this, but changing the embedded table to a subform was the solution I needed and it all works now.

    Thanks.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 7
    Last Post: 12-21-2014, 08:21 PM
  2. Replies: 4
    Last Post: 07-09-2014, 10:00 AM
  3. Unable to Find Object Error
    By MHernan1 in forum Access
    Replies: 1
    Last Post: 08-15-2013, 04:11 PM
  4. VBA Code to reference Form Image
    By jondavidf in forum Reports
    Replies: 1
    Last Post: 07-27-2012, 12:34 PM
  5. Replies: 4
    Last Post: 07-07-2011, 03:21 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