Parse field name from "on update" event 1

[wgbrow43]

I am writing a program to tabulate responses to 54 questions. Each question is answered “Yes”, “No”, or “N/A”. I set my table up with 3 fields for each question: Qx_Y,Qx_N, Qx_NA (with ‘x’ being the question #) – each field is a “Yes/No” data type.

Each question can only have one “Yes” answer. My plan was to write a subroutine for the “on update” event for each field to make sure the other two fields for that particular question were set to “False”: example: When Q1_Y is updated, the subroutine would set Q1_N and Q1_NA to False. When Q1_N is updated, the subroutine would set Q1_Y and Q1_NA to False, etc.

I do not want to create 162 subroutines (54 questions x 3 fields). Is there a way to write a subroutine that will identify what field was updated so I can then “compute” what other fields need to be updated? I guess I looking for a way to parse the field name from the “on update” event process?

This is a brand new project, so I am open to suggestions for a better way to do this as well. I just have to have something to demo the first of September.

 

[jebry]

Hi!

It seems like it would be easier to put these into frames corresponding to each question (you may need to change them to radio buttons). Then Access will allow only one to be marked at a time without any extra programming.

If that solution doesn't appeal to you try using the Screen object. You would create a public function (not procedure) in a general module and then use:

Set cntl = Screen.ActiveControl
strQuestion = Left(cntl.Name, InStr(cntl.Name, "_") - 1)

this will pick up the Q as well. If you don't want the Q then add

strNumber = Mid(strQuestion, 2)

or

intNumber = CInt(Mid(strQuestion, 2))

Then go to the Event tab on the property sheet for each of the check boxes and put:

=YourFunctionName()

Note that the function is very incomplete as you will need to check for the Y N or NA and you will need to check the value of the control to determine if you need to clear the other check boxes.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[wgbrow43]

Thank you for your reply. The frames sound like the way to go. I'm not familiar with them but I'll research them. Thanks again!!

 

[gol4]

Jeff pointed out an excellent solution for this but I want to add that the real problem appears to be the initial design of your table. since they can only have one answer why do you have 3 fields. You will be able to do it this way but then later you will have troubles getting the data.

question# response.