Results 1 to 10 of 10
  1. #1
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6

    Limit New records on a sufrom depend of parent form text box value

    I am a First Timer
    I like to know how to control subform NEW entries

    ParentForm Name > AttendanceF
    Textbox control source on Parent Form > NumberAttended




    SubformName >AttendanceSF


    There are exisiting records on subform varies on training module
    I like to know how to control subform NEW entries depend on the Parent form field value (Numberattended)
    eg if use put 8 in NumberAttended field only 8 names could be added to the subfrom (AttendanceF).
    User is forced to add 8 records and limit to 8 related records at this stage

  2. #2
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6
    I don't want to set max number of records. I like to limit the records depend on parent form value. This value differs every time? Not sure made it clear

  3. #3
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6
    Any other ideas?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    On the subform's BeforeInsert event check if the number of saved records is equal to the main form control that contains the number. If it is equal to the number, cancel the record addition. If not, allow it. What you haven't said is where the count of the subform records has to come from. Could be a temporary table, a target table, or simply a Count() of the primary key field in the subform (so as to prevent counting blank fields in subform records). If you can figure out what to use the Count or DCount functions on, you should be able to adapt the following (which should count a PK field).

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
    
    If Count([NameOfSomeField]) > Me.textboxWithNumber Then
      MsgBox "Message Here", vbOKOnly
      Cancel = True
      End If
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6
    I have this code which I like to implement

    Dim i As Integer
    Dim N As Integer
    N = Me.Parent.NumbersAttended ' working


    MsgBox ("Attended=" & " " & N)
    i = Me.Parent.txtAddtionAllowed.Value
    MsgBox i
    i = i + 1
    Me.Parent.txtAddtionAllowed.Value = i
    MsgBox (i)
    If i = N Then
    MsgBox ("Equal")
    '(deny addition)
    Else
    MsgBox ("Not equal")
    '(allow addition)
    End If
    End Sub
    ===============
    Issue is if record is edited counter still increment
    with before insert I have this function
    =CarryOver([Form],"" ( from Allen Browne)

    I am appending records to existing subform (Datasheet) depend on Main form field [NumbersAttended] user need to enter manually

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't really understand what your issue is now. The code you posted doesn't tell me what event you're trying to use it in - you left that part out.

    What I posted is a slight variation of what I used before (which works), except that I used DCount instead of Count because I was counting the existing records in a temp table. The version I posted should count the number of records in a subform data sheet (which you didn't say you were using, but I assumed you are) as long as you supply the proper control names. There is no need for counters or creating count variables as you are doing.

  7. #7
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6
    Please may I start from the beginning
    1-Main form has an input field called [NumbersAttended] which user has to enter a number. (Eg say 8 , number of people attended to the training)
    2-Then user only ONLY able to Append 8 more names to the Subform on top of existing relevant records. ( Eg., Other people who have done the same training)
    This what I want to achieve please

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can we step back even further?
    In 4-5 lines what exactly is the "business" issue you are trying to solve/automate? That is what are you trying to do in plain English?
    With database, getting your tables and relationships designed to meet your requirements is critical. If these aren't correct, you'll end up trying to create "workarounds" for everything. You say, "I am a First Timer", so I'd like to see you get your design matching your needs--before getting into forms and subforms.

    So you do some sort of training events that people/students attend.....

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by tuffley21 View Post
    Please may I start from the beginning
    1-Main form has an input field called [NumbersAttended] which user has to enter a number. (Eg say 8 , number of people attended to the training)
    2-Then user only ONLY able to Append 8 more names to the Subform on top of existing relevant records. ( Eg., Other people who have done the same training)
    This what I want to achieve please
    Your request does not seem to have changed from the beginning and again I say what I posted will do what you want. If you don't understand how to implement it, that is a different issue. The code I gave you limits the number of records in a subform. All you have to do is adapt it to refer to your main form control that has the number you want to use as the maximum number of subform records.

    If you would like to post a copy of your db as an accde with no features that are incompatible with version 2007 (or a db with an mdb extension) with instructions on which form(s) to open I will try to make it happen for you.

  10. #10
    tuffley21 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    6
    Micron
    Appreciated being trying to HELP -Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 12-20-2015, 01:09 PM
  2. Replies: 4
    Last Post: 02-12-2015, 04:17 PM
  3. Replies: 3
    Last Post: 07-03-2013, 01:20 PM
  4. Text limit in a form Access 2010
    By ThebigCt in forum Access
    Replies: 2
    Last Post: 02-02-2012, 10:14 PM
  5. Replies: 3
    Last Post: 12-01-2011, 06:51 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