Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Unanswered: Limit number of records entered

    I have two tables with a one to many relationship.

    The first table is the Visit Authorizations with several fields, with one called "# Visits" where the user enters a number.

    The second table is the Visits table that contains records with the visit dates and names of nurses.

    I want to prevent the user from entering too many records in the Visits table. If the "# Visits" in the Visit Authorizations table is 4, I want the user to only be able to enter up to 4 records in the Visits Table.

    I'm not sure what event to use or how to write the code. Could anyone help me with this?

    Using Access 2002
    Last edited by Zenaida; 04-13-06 at 13:07.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This could be easy or it can be impossible ... Depends on how you want default add record action to work. The safest way to do this is to have a form where you control the adding of records. So, don't have the Add Records property turned on. You won't be able to stop someone from adding past the allowed number.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    If I can't prevent someone from going past the allowed number, can I at least have a message box pop open?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Zenaida
    If I can't prevent someone from going past the allowed number, can I at least have a message box pop open?
    I don't see why not ... Again: You can prevent the overrun if you design it properly ... You have to provide a mechanism to add the records. Then you can block the excess ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I did not mention I'm a newbie so preventing the overrun sounds too complex for me to dive into right now. I only have one user using this database so the message window will work fine for us.

    But if you could help on the message window . . . . maybe the Before Insert event would be the place to do this?

    I'm fairly new with writing code so I don't know how to reference the information I need the code to evaluate.

    Right now I have a code to prevent the user from entering a child record (visit) without a corresponding parent record (auth) in the BeforeInsert event on my form.

    What do I need to add to the code to have it compare the value in the #Visits field in the parent table to the number of child records the user is trying to add in the Visits table?

    Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent.NewRecord Then
    Cancel = True
    MsgBox "A new Completed Visits record can not be added without an Authorization record."
    End If
    End Sub

    If you have a better suggestion, please let me know. Thanks so much for your time.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Tip: What I was intimating ...

    Add a command button.
    In your form turn off adding records.
    In the OnClick event of the command button put code like:[CODE]Dim SQLString as String

    SQLString="INSERT INTO TableNameHere (Column1, Column2, ... ColumnN) VALUES (0,0,0,0, ... ,0);"
    Docmd.RunSQL SQLString[\CODE]

    And obviously, provide the tablename, columns names, and appropriate initial values ...

    Then requery the form/subform ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I hope I'm not getting you frustrated, but I don't understand what you're suggesting, it's a little over my head.

    I have a main form and two subforms. Subform one contains a field called "Visits" that has a number in it. I want to prevent the user from entering more records in subform two than the number that is in the "Visits" field in subform one.

    I have subform one and subform two displayed in a tab control (on the same tab) inside the main form.

    This is the code I found that I've been trying to get to work, I know it has major issues but am at a loss as to how to fix it for my situation:

    Private Sub Form_Current()
    Dim rst As Object

    Set rst = Me.RecordsetClone

    Me!VISITS.Value = rst.RecordCount

    If rst.RecordCount >= Me!VISITS.Value Then
    Me.AllowAdditions = False
    End If

    End Sub

    I get the following error message: Run-time error '2465': Microsoft Access can't find the field 'VISITS' referred to in your expression.

    I don't know if I'm entering the correct field name or not. Do I enter the field name from subform one that contains the number? Do I mention it in both places where I have it in the code? I'm really lost. Any help is greatly appreciated.


    Using Microsoft Access 2002 DAO

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sadlywith Access /JET there is no way of implenting this at databse storage level. so as M Owen says you need to control it at the form level. its not as bad as it sounds - but it doesn mean that you have to make sure you users never have access to the tables (which of course you dont do --- do you!)

    one way is to put some code in the forms before insert (or after insert/ before update) which does a quick check that the required paramter hasn';t been exceeded

    A Dcount may be appropriate, as much as I hate domain functions they do ocassionally have their uses.

    dcount is essentially a select statement returning the number of a specified calclaution. if you can express you problem in SQL dcount should return the correct value, which you can then decide on waht action to take place. I don't understand your problem (sorry too lazy to read in depth - if thats a problem get to planks of wood and crucify me - it is easter after all) so I can't give a defintive answer as to where you place your code.

    the before insert is triggerred is before a (blank) record is inserted - providing you can work out if your business rule is going to be violated with no other data capture than attempting to create a new record then thats the place for it.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks for taking the time to explain that to me. I'll take the time and try what you and M Owen suggested. Thanks again and have a great weekend!

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In the On_Current event:

    Dim iC As Integer

    iC = DCount the number of visit records where the key matches the parent record

    If iC > 3 Then Me.AllowAdditions = False Else Me.AllowAdditions = True

    The DCount function is explained in the help file.

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Here's some actual code from an app I wrote a while back:[code]Option Compare Database
    Option Explicit

    Private Sub Form_AfterInsert()

    Call CheckCount
    End Sub

    Private Sub Form_Current()

    Call CheckCount
    End Sub

    Sub CheckCount()
    ' The "New Generation" of PSVF's has a parameter limit of 22 due to the additional
    ' space needed for the validated parameter ranges.
    ' 4/5/2004: increased to 24 due to customer requirements.
    ' 5/14/2004: redesign of PSVF includes parameters spanning 2 pages - max 18 per page. Therefore, the max is now 36.
    ' 6/2/2004: the never ending story of PSVF's - the design has changed again; now we're at 30

    Dim blSwing As Boolean

    If IsNull(Me.ProcessKey) Then
    blSwing = False
    Else: If DCount("[ParameterKey]", "tblParameters", "[PSVFormKey] = " & Me.PSVFormKey) > 29 Then blSwing = True Else blSwing = False
    End If

    Me.AllowAdditions = IIf(blSwing, False, True)
    Me.lblLimit.Visible = blSwing ' this is a label indicating the max has been reached

    End Sub[/cpde]
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Here's some actual code from an app I wrote a while back:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_AfterInsert()
    
        Call CheckCount
    End Sub
    
    Private Sub Form_Current()
    
        Call CheckCount
    End Sub
    
    Sub CheckCount()
    ' The "New Generation" of PSVF's has a parameter limit of 22 due to the additional
    ' space needed for the validated parameter ranges.
    ' 4/5/2004: increased to 24 due to customer requirements.
    ' 5/14/2004: redesign of PSVF includes parameters spanning 2 pages - max 18 per page.  Therefore, the max is now 36.
    ' 6/2/2004: the never ending story of PSVF's - the design has changed again; now we're at 30
    
    Dim blSwing As Boolean
    
    If IsNull(Me.ProcessKey) Then
        blSwing = False
        Else: If DCount("[ParameterKey]", "tblParameters", "[PSVFormKey] = " & Me.PSVFormKey) > 29 Then blSwing = True Else blSwing = False
    End If
    
    Me.AllowAdditions = IIf(blSwing, False, True)
    Me.lblLimit.Visible = blSwing ' this is a label indicating the max has been reached
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •