Results 1 to 3 of 3
  1. #1
    Sienna is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    19

    Showing Subform conditionally--problem with VBA code?

    I am trying to show a certain subform based on whether or not a field is blank or null. This database is not owned by me--it's what we use to run queries and reports on the data on a centralized server for the library. It's not even in the same city--so changing the tables is not an option for me.



    So, I have a subform (subform1) that is linked to the main form based on 2 fields (BibID and Item_Enum). The trouble is that the second field (Item_Enum) is sometimes blank, so the subform will not display. So I created another subform (subform2), which is a duplicate of the first one, but this time only linked on the first field (BibID). So, if that second field (Item_Enum) is blank, I want subform2 to display and subform1 to be hidden. If the second field (Item_Enum) is not blank, I want subform1 to display, and subform2 to be hidden.

    Sorry if that's confusing!

    I've tried putting the VBA code on different events for the form, but I can't get it to work consistently.

    There are two problems, one of which I think I have fixed--but I would prefer a better solution. The VBA code doesn't run unless the form is newly loaded. I've tried placing the VBA code on different form events, but nothing works. I want the code to activate whenever a new record is displayed. The work around is that the form IS newly loaded every time the user scans a barcode for a new book. That works now, but if I need to change the way the user looks up new records, this might not work anymore.

    The second problem is that the VBA code doesn't display subform1 when Item_Enum is blank. It doesn't display subform2 either, but that's the desired result.

    Private Sub Form_Load()
    If Me.ITEM_ENUM = "*" Then
    Me.subform2.Visible = False
    Me.subform1.Visible = True
    Else
    Me.subform2.Visible = True
    Me.subform1.Visible = False
    End If


    End Sub

    I've tried changing the condition to Me.Item_Enum = null, and changing the false/true results accordingly, but that didn't work either. In that case, only subform2 is visible, whether it should be or not.

    What am I doing wrong?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Did you try putting the code in the On_Current event? That is the most obvious one.

    If Me.ITEM_ENUM = "*" does not test for a blank - it tests for a value of an asterisk (*).

    To test for Null in item_enum, use If isnull(Me.ITEM_ENUM)

  3. #3
    Sienna is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    19
    I tried On_Current, but I changed it after it wasn't working--it could have been a different problem, though.

    So I've tried this:

    Private Sub Form_Current()
    If IsNull(Me.ITEM_ENUM) Then
    Me.Subform2.Visible = False
    Me.Subform1.Visible = True
    Else
    Me.Subform2.Visible = True
    Me.Subform1.Visible = False
    End If
    End Sub

    It didn't work at first because I had switched around the Falses and Trues... Oops.

    Thanks for your help. I'm glad to have a form that works!

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

Similar Threads

  1. some problem in showing chart legend
    By afshin in forum Queries
    Replies: 5
    Last Post: 09-16-2012, 10:03 AM
  2. Subform Not showing new records created
    By apetho17 in forum Forms
    Replies: 11
    Last Post: 04-03-2012, 12:23 PM
  3. Replies: 2
    Last Post: 04-20-2011, 01:50 PM
  4. Subform showing white
    By Rick West in forum Forms
    Replies: 2
    Last Post: 06-24-2010, 08:07 AM
  5. Subform not showing correctly
    By ricardo9211 in forum Forms
    Replies: 1
    Last Post: 08-27-2009, 07:49 AM

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