Results 1 to 8 of 8
  1. #1
    shadowbh2 is offline Novice
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    4

    Exclamation Limit number of entries into nested subform?

    I am currently trying to figure out how to limit the entries into a nested sub form to just one.

    for example im making wine collection database, I have a PURCHASE table, a WINE table containing the FK of the PURCHASE PK, and an INVENTORY contain the FK of the WINE PK.

    a purchase can contain many wines, but one wine can only be in one inventory location.



    I am trying to add all this information with one form. I fill out the purchase details, enter the info of X bottles of wine were in the purchase, and then with one of those wines selected in their subform, i enter the inventory details for that wine. I go select the next wine in the first subform and the inventory nested subform doesnt allow another entry.

    I have this code loaded into the On Current event for the main form:

    Code:
    Private Sub Form_Current()
    With Me![INVENTORY Subform].Form
      If .Recordset.RecordCount = 0 Then
        .AllowAdditions = True
      Else
        .AllowAdditions = False
        .AllowEdits = True
      End If
    End With
    
    
    End Sub
    anyone got any ideas?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not following the described form structure and behavior. Want to provide db for analysis? Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shadowbh2 is offline Novice
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    4
    sure thing

    test2.accdb

    thanks for your time =]

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not clear about data relationships.

    WINE table looks like a record for each bottle.

    Isn't ShelfLife an attribute of the bottle, not the location?

    Surely bottles get used and the space is vacated and available for another bottle? This means each location can be associated with multiple bottles. So question is - do you care about bottle history for each location?

    Consider a field in WINE table for location instead of field in INVENTORY for wine.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    shadowbh2 is offline Novice
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    4
    Bottle location history means nothing to me. Any historical data I would want would be from WINE and PURCHASE.

    Ill look into moving ShelfLife to WINE, I just need to figure out how to limit that subform of INVENTORY to allow one location per WineID.

    Thanks for looking at my DB

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What I am saying is don't think the INVENTORY subform is needed if the location is saved into WINE table. Use a combobox in WINE form to select the location. The combobox RowSource can be limited to locations not already assigned to a bottle.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    shadowbh2 is offline Novice
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    4
    Ok, I see what you're saying. So basically just get rid of the INVENTORY table then? or just source from it.

    Im obviously fairly new to access im not sure how i would go about these things.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Instead of INVENTORY, maybe call it LOCATION. The table can still be useful as a source for the location details and controlling the combobox for selecting location.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. limit number of records in a subform
    By PJT in forum Forms
    Replies: 4
    Last Post: 04-13-2014, 11:29 AM
  2. Limit entries on a report
    By Derrick T. Davidson in forum Reports
    Replies: 3
    Last Post: 03-17-2013, 09:33 PM
  3. Replies: 2
    Last Post: 06-10-2012, 10:25 PM
  4. Limit number of records in report
    By aksnell in forum Reports
    Replies: 3
    Last Post: 12-05-2011, 02:31 PM
  5. Replies: 1
    Last Post: 10-22-2010, 01:29 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