Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85

    3 questions - 2 about records and 1 about making field visible.

    I actually have 3 questions that I am hoping someone can help me with.

    1. On a form I want fields to be invisible until one field meets its requirements and then the rest become visible. I have the following code in but its not working.


    Private Sub Act1Cal_AfterUpdate()
    Me.CalisthenicsDone.Visible = False


    If Me.Act1Cal = "Calisthenics" Then
    Me.CalisthenicsDone.Visible = True

    End If
    End Sub

    2. I want to know if it is possible to have a table to only allow 10 records? If it is possible how to I do that.

    3. I have been playing with the database I am creating and as I work with it I am creating records that have an autonumber. Is there away to reset the autonumber back to 1?

    Any help would be much appreciated,

    Brad.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1. On a form I want fields to be invisible until one field meets its requirements and then the rest become visible. I have the following code in but its not working.


    Private Sub Act1Cal_AfterUpdate()
    Me.CalisthenicsDone.Visible = False
    If Me.Act1Cal = "Calisthenics" Then
    Me.CalisthenicsDone.Visible = True

    End If
    End Sub
    You will probably have to have similar code in the on current event of the form to handle existing records

    IF me.Act1Cal="Calisthenics" THEN
    Me.CalisthenicsDone.Visible = True
    ELSE
    Me.CalisthenicsDone.Visible = False
    END IF

    2. I want to know if it is possible to have a table to only allow 10 records? If it is possible how to I do that.
    You can do it through a form but you cannot prevent someone from going directly to the table if you make the tables available (as a general rule though, the users should not have access to the tables all of their interaction with the database should be through forms.)

    From a form, you could do the following in the on current event:
    Code:
    If Me.NewRecord Then
        MsgBox "You are not permitted to add a new record via this form"
        DoCmd.GoToRecord , , acFirst
    End If
    3. I have been playing with the database I am creating and as I work with it I am creating records that have an autonumber. Is there away to reset the autonumber back to 1?
    Assuming that the records were just for test purposes, you can delete them and then run the compact/repair utility. If the records are of value, you can create a new database with the same structure and then append the records from the old database to the new. You will probably have to link to the old database from within the new.

  3. #3
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    What do you mean on current event? That doesn't come up as one of the options in the property table

  4. #4
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    Events are on forms, not tables.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In the property sheet of the form, look for Events and then On Current. (see attached for an example)

  6. #6
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    so how do I bring up the form properties, instead of a field property?

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    When you first open a form in design view you should be able to just go to the design tab in Access 2007 and then click on the property sheet. If you work on a control on the form and want to get back to the form's properties you have to click on the small button in the upper left hand corner of the form (at least that is what you do in Access 2003; I can't remember how you would do it in Access 2007 but it should be similar--sorry I only have Access 2003 here at work).

  8. #8
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    Thank you so much jzwp11. My fields are appearing and disappearing as they should and I now have record #1 again. I have not tried to limit the amount of records yet that is next on my list.

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would have to check to see how many records currently exist and then if it is 10 prevent the user from entering a new record. The DCount() function is one way of doing it

    Code:
    If Me.NewRecord Then
        IF DCount("*","tablename") <10 THEN
          'other code such as your code to make controls visible or not
        ELSE
           MsgBox "The maximum number of records has been reached"
           DoCmd.GoToRecord , , acFirst
        END IF
    End If
    Out of curiosity, why would you want a limit of 10 records in a table?

  10. #10
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    I built a database that tracks weight, Calories intake, exercise calories expended and Activity tracker. I only want a maximum of 10 profiles created because I have to create a new table and form for each user or else the one table would have everyones information on it. I don't want your information when I am looking at my information.

  11. #11
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...create a new table and form for each user or else the one table would have everyones information on it.
    No, the rule is that like data should be in 1 table, so you would not have a separate table for each user. You as the database designer have to use the right forms to allow the user to see only their data.

  12. #12
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    So how then would I use the same form for each profile but seperate it so when you look at the previous records on the form I will only see the Brad forms and not the David forms?

  13. #13
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    They would be adding to the same table so wouldn't I see all the records?

  14. #14
    stryder09 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    85
    A better explanation of what I am trying to do.

    I have a general form/table that each person would enter their profile information in, they would only go into this if they wish to change their profile at all.

    Name
    Weight
    Height
    Age
    % they wish to lose, # of days (these would be editable).

    I then have a 3 other tables linked through relationships, Weight Tracking, Food Tracking and Activity tracking. These tables draw information from the general table above when needed.

    These 3 tables are linked to the same form. I have it set up so that when you would pick profile 1 form it would place the persons name automatically on the form and then they would enter their daily info.

    If I have all the profiles putting onto the same table how do I break it up so they only see their info from the table, though everyones is on there?

  15. #15
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would use a form that you filter before opening depending on who the person is. Of course, you will have to know who the person is, so you will have to have them log into the database. Once you know who logged in, then you can filter the form to only show their data.

    So in your table that holds the people information, you would have a username field (unless you want them to use some form of their real name such as firstname.lastname or firstinitialoffirstname.lastname) and a password field. You would have a login form and then based on the information inputted, you would check it against the usernames and passwords in the DB, if both matches open the form filtered to just that user.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making subform field visible/invisible
    By Snufflz in forum Forms
    Replies: 3
    Last Post: 01-17-2011, 05:30 AM
  2. Replies: 2
    Last Post: 01-06-2011, 04:38 AM
  3. Making objects visible in a report
    By Lockrin in forum Reports
    Replies: 3
    Last Post: 04-12-2010, 07:06 AM
  4. Tab only visible when field = x
    By ecpike in forum Forms
    Replies: 7
    Last Post: 06-08-2009, 04:38 PM
  5. Replies: 2
    Last Post: 11-17-2006, 01:07 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