Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling Multiple Fields (Access 2002)

    I would like to fill a number of fields on a form, if an "NA" is entered into the first (or in one form's case) the second field.

    Basically, on form sfrmForm1, if "NA" is entered into txtField1, I would like "NA" to be entered into txtField2, txtField3, etc., otherwise the fields are left blank. I could use "NA" as the default, but believe that it will lead to more data entry errors, not less in this case.

    I've found tons of examples in this forum that have really helped with other problems, but haven't come up with the right phrase to search for the answer to this one. To Charlotte, Hans, and the other moderators, I now have database with 150 different survey questions working correctly thanks to your responses to other's questions. Thank you very much. I still can't believe that it actually works.

    Sue

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

    Re: Filling Multiple Fields (Access 2002)

    Taking the approach you suggest, you can write a small VBA procedure that is triggered by the AfterUpdate event for txtField1 that checks the value of that control, and if it is "NA" then set the other text box values to "NA" - you might also want to lock those controls so the user cannot alter the content. That does raise the issue of what if the user leaves txtField1 as a Null. Do you also treat that as an NA, or do you give the user some sort of error message?
    Wendell

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling Multiple Fields (Access 2002)

    Hi Wendell,
    Looks like my incoming email isn't actually making it here. At least, I can still access the forum.

    My problem is programming skills or more precisely lack of them. I can copy an example someone has of a program, but get confused when trying to generate something new. Haven't successfully done it yet, but will get there a some point. Thankfully, Helen Feddema's books and website have lots of examples in them and you kind folks provide more examples.

    I don't want to lock the fields after the NA is generated. The individual who will be entering the data will be reviewing mental health charts for quality and completeness of services. He is required to go over the data with the folks he is reviewing. They often challenge him on his entries. It is quite easy to overlook something and then have to go back and change the entries.

    I suspect my boss would prefer the no-Null option. She took the first big bunch of the 24-page form and put them in a spreadsheet, because she had to figure out the fairest way to count the "Yes" and "No" fields in relation to the the "NA" field. It is right after that, when she is requested the database. It was either that or she would strangle the clinician. There are 10 sub-forms linked to two tables that are in a one-to-one relationship, the no-Null requirement wouldn't cause problems would it?

    You wouldn't have a small sample of a similiar AfterUpdate event by chance? If not, at least I know what phrase to go looking for.

    Sue

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

    Re: Filling Multiple Fields (Access 2002)

    Here is an example that you may be able to adapt for your purposes:

    Open sfrmForm1 in design view.
    Select the txtField1 text box.
    Activate the Event tab of the Properties window.
    Click in the After Update box.
    Select [Event Procedure] from the dropdown list.
    Click the ... to the right of the dropdown arrow.
    You'll be taken to the Visual Basic Editor, and the first and last line of the event procedure will already have been created for you.
    Make it look like this (you can copy text from this post and paste it into the Visual Basic Editor.)
    <code>
    Private Sub txtField1_AfterUpdate()
    Dim i As Integer
    If Me.txtField1 = "NA" Then
    For i = 2 To 12
    Me.Controls("txtField" & i) = "NA"
    Next i
    End If
    End Sub
    </code>
    I have assumed that the text boxes are really named txtField1, txtField2 etc. This code will fill txtField2 through txtField12; change the occurrence of 12 to the highest number on your form.

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

    Re: Filling Multiple Fields (Access 2002)

    Programming is what really makes Access different - with Word or Excel you can build fairly complex documents, but with Access you build systems or applications. If you haven't run across it yet, see if you can find a copy of "Beginning Access 2002 VBA" by Smith & Sussman. (There isn't a great difference between the 2002 and 2000 books, so either would be useful.) It leads a novice programmer through the process fairly painlessly. Helen's book is more focus on the intermediate user in many respects, and has only a few chapters on programming.

    In the mean while, Hans has shown you the code I had in mind - it should do the trick.
    Wendell

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Location
    Chewelah, Washington, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling Multiple Fields (Access 2002)

    Wendell and Hans,
    Thank you for the help. I do have Smith and Sussman's book and have been planning on going through it again, since I understand a lot more about Access then during my first reading. I'm grasping more each time and can only say that this current project has really improved my understanding of some of the aspects that have confused me for quite a while. A number of Hans' examples or answers were key to figuring out the one-to-one relationship. The actual example of a database with a simple one-to-one relationship that Hans' posted a while back made a hugh difference for me. I could look at the relationships and properties of the various levels of the form and fields and figure out what was going on.

    I very much appreciate the fact that folks with your skills and experience are so willing to help others get solutions to Access problems. Making a database that does what you want is a lot of fun. Thank you for making it a bit easier for me.

    Sue

Posting Permissions

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