Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003

    Wink Unanswered: Field Manupilation/Navigation in Access2000

    I am designing a database for my school. It's for a survey.

    I have been tasked to use Access 2000 to prepare this survey. All a
    respondee needs to do is to enter the program, enter his answers,
    through a form, and then the database will automatically capture the

    I have linked my access to Excel, so that my teacher can use the
    results of the survey to generate the results she needs, she being
    Access illiterate.

    My main table involves having the question nos listed in the first
    Column, with subsequent columns each representing one
    respondee.Therefore the responses will be recorded in a 2-D table
    format, and the results for each question can be tabulated row wise
    in Excel or pivoted for Column wise assessment.

    My problem is as follows. The database will be placed online, so that
    respondees can fill in their answers online. These results are to be
    automatically recorded into the table. -- Every time a new person
    starts the survey, his response has to be recorded in a new field.

    The "respondee" fields are labelled from R1 to R100. I have been
    trying to get Access to jump to the next empty field every time a new
    person starts the survey.

    I have attempted to use the following commands

    Private Function Toggle2_Click()

    Dim X As Integer
    Dim Count As Integer

    For Count = 1 To 100 Step 1

    If IsNull(tables![feb trial].Fields!R("&X&")) Then
    [Forms]![Feb Trial 1]![Target].[ControlSource] = R("&X&")

    Else: X = X + 1
    End If


    End Function

    I have an option box named "Target" that would allow the user to
    input his answers. The option box has to be controlled by the
    appropriate "controlsource", in this case, being the next avaliable
    empty field.

    Therefore, I was hoping that the above command would at the click of
    a mouse, check for the

    Occurence of answers in a particular field, if the field does not
    have any answers, = NULL , then the respondee can input his answers
    into that field. If that particular field has got some inputs,
    through the loop, X will be increased by 1, and the programme will
    again search the next Field, being R ... and so on till it finds an
    empty field. Having done so, it will set the option group control
    source as that particular field "R ... " and then the survey continues

    My command does not work.

    Could any of you suggest a way to solve my problem? or perhaps have
    any programs to recommend that may help me solve my survey database

    Many Thanks


  2. #2
    Join Date
    Jun 2002
    Vienna, VA

    You need a different table setup.

    Your answer table should be
    RecID (autonumber), questionID(from question table?), response ID(manual counter), response.

    Your manual counter would be a function to get the number of persons who already answered that question, then add one. That way, you're not limited to 100 respondents.

    Ask again here if you need clarification.


  3. #3
    Join Date
    Feb 2003

    Red face

    Thanks for your help

    I'm not to sure how does the manual counter, could you elaborate? And how could I go about writing this function? VBA or something?

    Is my elaboration below correct?

    RECID would be Record ID? using autonumber (primary key?)
    QUESTION ID would be the question Number linked to a question table
    RESPONSE would be the answer of the respondee

    one of the problems I face is that the questions of the survey have to be updatable also. IE the question are not fixed, so questions must be updatable, and so does the number of respondees.

    then again, is it possible to manupilate the fields in the way I tried previously?

    thanks so much


Posting Permissions

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