Using MS Access/MS Access Vba code

Advertisement


Question
QUESTION: How do I code the following:


1. Has the man submitted all reports?  Yes ____ No ____

 a. If not, how many times has the man been delinquent?
 b. If no, then have the funds been withheld as a result of failure to submit SF-330?
 c. If applicable, has the man submitted the final SF-330? Yes___NO___

ANSWER: Is this a homework question? If so, please do your own work. Show my what you think the answers should be and I will critique them.

But those questions are impossible to answer without knowing the database structure.

---------- FOLLOW-UP ----------

QUESTION: It is not a homework question. It is something I am working on my own. This is the question on the form, and the vba code that I wrote, but it is not giving me the correct output. I only need help on the Question 1.

1. Has the grantee submitted all quarterly Federal Reports in a timely fashion? YES___ NO___ a. If not, how many times has the grantee been delinquent?

b. If no, then have the funds been withheld as a result of failure to submit SF-425?

c. If applicable, has the grantee submitted the final SF-425 for the grant? YES___ NO___

2. Has the Finance staff accountant reviewed the Payment History and are the drawdowns consistent with the grant award expenditures? YES___ NO___ a. Has the grantee drawn down excess cash? YES___ NO___

b. If YES, have arrangements been made to repay the excess cash? YES___ NO___

3. Does the grantee have any additional issues not addressed during this review? YES___ NO___ If yes, please explain:







My VBA code:




Private sub form_current ()

On error Goto Err_Current

'Hide Q1a if Q1 is null or ="Yes"

If Me.ctl_3Q01="No" Then

Me.ctl_3_Q01a.Visible=True  

Else

Me.ctl_3_Q01a.Visible=False

end If




'Hide Q1b if Q1 is null or = "No"

If Me.ctl_3_Q1 = "Yes" Then

Me.ctl_3_Q01b.Visible = True

Else

Me.ctl_3_Q01b.Visible= False

End If




'Hide Q1c if Q1 is null or ="No"

If Me.ctl_3_Q01 ="Yes" Then

Me.ctl_3_Q01c.Visible = True

Else

Me.ctl_3_Q01c = False

End If

ANSWER: First, it appears your structure is incorrect. A survey database requires a very specific structure. It looks to me like you have a table with fields for each question. The proper structure should be:

tblQuestions
QuestionID (PK Autonumber)
Question
RelatedQuestionID (FK)

tblRespondents
RespondentID (PK Autonumber)
Firstname
Lastname
other info about respondent

tblResponses
ResponseID (PK Autonumber)
ResponseDate
RespondentID (FK)

tblAnswer
AnswerID (PK Autonumber)
QuestionID (FK)
ResponseID (FK)
Answer


One of the problems in your Code is that it uses the On Current Event. The On Current event fires when a new record is loaded. Since there is no data when the question first loads, the Else option will always be chose. But since you should redo your tables. The way I would do this is with a subform. So your main form is bound to the Answers table. When the question is answered you test the answer and display a subform linked on the the Related questionID to display the applicable subquestions.





---------- FOLLOW-UP ----------

QUESTION: 1. Has the grantee submitted all quarterly Federal Reports in a timely fashion? YES___ NO___ a. If not, how many times has the grantee been delinquent?

b. If no, then have the funds been withheld as a result of failure to submit SF-425?

c. If applicable, has the grantee submitted the final SF-425 for the grant? YES___ NO___

2. Has the Finance staff accountant reviewed the Payment History and are the drawdowns consistent with the grant award expenditures? YES___ NO___ a. Has the grantee drawn down excess cash? YES___ NO___

b. If YES, have arrangements been made to repay the excess cash? YES___ NO___

3. Does the grantee have any additional issues not addressed during this review? YES___ NO___ If yes, please explain:







My VBA code:




Private sub form_current ()

On error Goto Err_Current

'Hide Q1a if Q1 is null or ="Yes"

If Me.ctl_3Q01="No" Then

Me.ctl_3_Q01a.Visible=True  

Else

Me.ctl_3_Q01a.Visible=False

end If




'Hide Q1b if Q1 is null or = "No"

If Me.ctl_3_Q1 = "Yes" Then

Me.ctl_3_Q01b.Visible = True

Else

Me.ctl_3_Q01b.Visible= False

End If




'Hide Q1c if Q1 is null or ="No"

If Me.ctl_3_Q01 ="Yes" Then

Me.ctl_3_Q01c.Visible = True

Else

Me.ctl_3_Q01c = False

End If




It is a table, and it is a subform, and The questions are fields (3_Q1,3_Q1a,3_Q1b, and 3_Q1c. I need your help in correcting the code that I have to display the correct result. Note:
This is part of other 2 tables that the codes that I had are working correctly. It is just this particular Question 1 that I am having difficulty with

Answer
"It is a table, and it is a subform, and The questions are fields (3_Q1,3_Q1a,3_Q1b, and 3_Q1c."

And that is not the right structure. What you are doing is encoding data using field names. This is not proper database design. Relational database should be tall and thin, not wide. Using fields named for the questions is not properly normalized. Each Answer should be a record in an Answers table, not a field!

Fixing your database structure will require redesign of your forms. I urge you to redo your structure. It will make your reporting and analysis easier.

But I did already give you a clue. You are using the wrong event for that code.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.