Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit Records (2000)

    Is there a way that I can limit the number of records that can go into a table?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit Records (2000)

    You can't do this at the table level, but you can do it by writing a bit of VBA code behind a form based on the table. You have to make sure that your users won't edit the table directly.

    Create a BeforeInsert event procedure for the form like this:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.RecordsetClone.RecordCount > 10 Then
    MsgBox "You can't add records any more"
    Cancel = True
    End If
    End Sub

    Replace 10 by the number you need.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Records (2000)

    Thanks for the code detail. I REALLY want to take a VBA coarse but until then, I'm clueless. This information will help a lot.

    Thanks again,
    Trese

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit Records (2000)

    I would question why you need to limit the number of records in a table. That need suggests that there is something wrong with your database design in the first place or at least with the interface design. The only tables that customarily need limits are lookup tables, and you limit those by not allowing your users to see them.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Records (2000)

    I'm setting up a ride database and there are only four balloons available. The balloon information has to be updated for the four balloons but if they enter in any additional balloons, there won't be a calendar table attached.

    The reason being, I had a terrible time trying to figuring out a way to have a calendar attached to each balloon so I put the calendar information together in Excel and then brought it over. Unfortunately, doing it this way, the number of balloons can not increase because I manually attached the calendar to each balloon and then appended the calendars together into one big make table.

    I know this is a terrible approach but I didn't know how to do it any other way.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit Records (2000)

    I presume you can have a number of rides per day per balloon, so you need to track both time and date for each balloon. Have you considered the possibility of a single table with a pointer to a balloon table, so that all you have to do to add a balloon is to create a new entry in the balloon table. In other words, your table structure would look something like:<UL><LI>lngBalloonID - a long integer with the current value of 1 to 4
    <LI>dtStartTime - a Date/Time field with the start time of a ride
    <LI>dtEndTime - a Date/Time field with the end time of a ride[/list]The Ballons table would simply be a long integer table with numbers from one to 4 for the primary key, and a second text field to describe the specific balloon.
    There may be some issues with the number of rides a balloon can do in a given day, and so on, and you may want to setup a primary key on a combination of lngBalloonID and dtStartTime, and restrict the user to entering times on an interval of 1hour, 2 hours, 4 hours, or however frequently a ballon ride can be given so you don't end up with duplicate bookings. Hope this helps.
    Wendell

  7. #7
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Records (2000)

    Thank you for the reply Wendall. I'm too new at this. I don't understand how to create a calendar that goes from 2003-2023. In excel, I can put in the first date and drag down until I'm at 2023. Can something like that be done in access.

    Ideally, I would like to have one calendar with an unlimited number of balloons. But I barely figured out what I have.
    Sure appreciate the assistance.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit Records (2000)

    Well, Access doesn't work quite the same as Excel. By doing the drag, I assume you are creating a row of cells for every day of every year for the next 20 years, and then you have columns that represent blocks of time for each day. If not, please correct me.

    Anyhow you can do the same thing in Access, but you can do it in one table because you can make a column (field) represent the balloon ID. Taking that approach, your table would look something like BalloonID, Date, TimeBlock1, TimeBlock2, TimeBlock3, TimeBlock4 ....., and that does simply the issue of avoiding duplicates. However it means you need a record for every day for the next 20 years, or something like 29,000 records to start with (for 4 balloons), and if your business is like most, many of the cells would be empty. In any event, it can be done pretty easily by importing the data into Access from Excel, and then creating a primary key consisting of the BalloonID and the Date. The approach I suggested earlier would only create a record for a given time block where a booking had been made. That suggests the other issue of tracking the booking party and whether or not they've paid, their address, the usual sorts of things one tracks for customers. Hope you get some ideas from this - database design is about 50% science, and 50% art!
    Wendell

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limit Records (2000)

    I'm not sure I understand why you are doing this, but you can effectively limit the number of records by using the validation property. For example, if you only want 4 balloons, and the BalloonID field is numeric, then just use a validation code of "Between 1 and 4".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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