Results 1 to 5 of 5
  1. #1
    athanas-jifunze is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    8

    "Auto Changing Field Values when another field value has Changed"

    Hi team. I have five tables relating. The first table is tblParticipant(ParticipantID - not auto number, PartipantName, ParticipantSex) the second table is tblTraining(TrainingID - auto number, ParticipantID, TrainingTypeID, TrainingTopicID, AppearanceID, TrainingStartDate, TrainingEndDate, TrainingVenue) the third table is tblTrainingType(TrainingTypeID, TrainingType), table four tblTrainingTopic(TrainingTopicID, TrainingTopic) and table five is tblAppearance(AppearanceID, Appearance).


    The relationships: These table are related as per colors above, the files with same color in two tables are related.

    My request. When Doing data entry in the form to populate data in the tblTraining I want Appearance field in the tblAppearance auto change the values from First, Second, Third and so forth.



    Explanation: I am developing a Training database which will store a list of teachers who will be trained for three years, each teacher will attend many training. So when she/he comes for the first training when I Choose the Training Type, the table generate the new TrainingTypeID and when I choose a training topic generate the new TrainingID.

    Request: I want the field Appearance to auto change the field from blank to First when I enter the training type for the first time, and when the participant attend the second training the Appearance field also auto update to Second, and so forth. What is the appropriate code or way to perform this task

    Will appreciate much your help

  2. #2
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Could you open a recordset and count the number of occurrences of the TrainingTypes for each ParticipantName? Then you could use a case statement to fill the tblAppearance.

  3. #3
    athanas-jifunze is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    8
    Thank you for your quick reply. However I am junior to BVA what kind or sample of case statement to use in filling the tblAppearance.

    Thank you Agailn

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Dim strCount as Integer
    strCount = dcount("FieldtoCount","YourTable","YourCriteria")


    Select Case strCount
    Case 1
    Me.Appearance = "First"
    Case 2
    Me.Appearance = "Second"
    Case 3
    Me.Appearance = "Third"
    Case 4
    Me.Appearance = "Forth"
    End Select

    If you are using a form, something like the above may work.

  5. #5
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Your table explanation is not clear. First - your Participant table should have as its primary key field an auto number field. ALL TABLES should always have a primary key field that is an auto number field.

    Then one needs to understand what I'll describe as List tables versus Data & Transaction tables. A list table is primarily static - the most classic is the list of USA States but also Product Lists is common. Sometimes the List table itself is more easily built by relying on Look Up fields of other sub List tables. This is particularly true in a Product table where this table includes Look Up fields from a Category List, Color List, Size List - - because each of these are discrete sets to select from.

    A data table is your Participant table. Generally we don't call it a list table because it has updates & additions much more frequently. Training courses is a List table - it gets updated perhaps once per semester or whatever period is appropriate. Then you have Transaction tables that are the intersection of those. So you have a ParticipantTraining table - where you are going to look up & select a Customer and then look up and select a Training course.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-29-2016, 11:42 AM
  2. Replies: 5
    Last Post: 11-23-2014, 03:54 PM
  3. Replies: 4
    Last Post: 06-06-2014, 08:50 AM
  4. Replies: 7
    Last Post: 01-29-2012, 07:44 AM
  5. Replies: 8
    Last Post: 11-12-2010, 10:55 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