Question on controls

[absolutered03]

Attached is the Relationship Diagram for my database.

Basically, I want to create a form for tblCalls...i used to have the questions as fields in this table. but I learned that it is not normalized that way. so now, as you can see the questions, answers, and values for the answers are stored in different tables.

Well when I designed my form with the question as fields in tblCalls, it was quite easy to make controls that displayed the question and the pull down for the answers.

Now, though, I don't know how to go about it when the questions are on their own table with the answers in a linking table, and the values for the answers in a totally different table.

Basically, lets say

tblQuestions
QuestionID/Question/QuestionTypeID
1/Welcome/Service
2/Professional/Service

tblValues
AnswerValueID/Value
1/YES
2/NO
3/NA

The form I am creating is for a representative to review recorded calls that are taken in our CS dep.

So the form frmCalls is like a questionaire for the reviewer to 'rate' the representatives performance.

frmCalls then would then need to look as follows: (i will keep it simple for this example)

Representative: [pull down to select rep name from tblEmployees]
Reviewer: [pull down to select reviewer name from tblReviewers]
Date: [text box to enter information for tblCalls.Calldate]
Did the Representative Welcome the Caller? [Pull down populating three options from tblValues, (YES,NO,N/A). When an answer is chosen, a record should be created in tblAnswers with the current CallID 'n', for QuestionID '1' (the ID number for this question) and AnswerValueID 'n' (for the selected answer)

I hope this is clear...please let me know if I can explain this better for you.

Thank you SO much for all your help! I am in a bind because I need this working correclty by Monday...ugh. I will never accept a project like this ever again.

 

[CEH]

Sounds like you just need to build some combos... Use the wizard... it will walk you through it... For example... Form is open in design view... on the toolbox pick combo.. as soon as you click it on the form wizard will open.... Example for AnswerValueID.... I presume this field is in the query used on your form... The wizard will ask you what table or query..then you will pick the fields for your combo... from your answer table...pick AnswerValueID and Description, it will be set to hide the ID field... store value in foreign key...AnswerValueID. Hope that gets you started.

 

[absolutered03]

Thanks for posting back. Your help is very much appreciated.

I have gotten as far as creating the comboboxes with the correct responses in the pull down.

The problem is that I can't relate the answers back to a new record in tblAnswers.

See, because tblAnswers holds the three foreign keys:

tblanswers
AnswerID - PK, autonumber
CallID - FK from tblCalls (this is just an autonumber for new call records)
QuestionID - FK from tblQuestions (this is just an autonumber for the question being asked)
AnswerValueID - FK from tblValues (autonumber of the response chosen)

So my form control combobox pulls the correct options from tblValues, but I don't know how to make the control create a new record in tblAnswers with the corresponding CallID, and QuestionID.

I'm guessing this would have to some coding:
Example for callID 1, Question 1:
-Pull down the combobox to choose value for certain question(lets say YES, or AnswerValueID 1)
-When a value has been picked, Create a new record in tblAnswers with following data:

tblanswers
AnswerID: Next Available autonumber
CallID: 1
QuestionID: 1
AnswerValueID: 1

Any ideas, even shots in the dark would be appreciated!!!
Thanks again!

 

[stopher]

I think you need to use the form/subform method (the usual method for entering many records relating to one record). The form will show a call. The subform will list all the answers for that call. The form and subform are internally linked (master/child relationship) so that when you add a new record to the subform it knows to link it to the main form (by the FK).

See my example attached and take a look at frmCalls.

hth
Chris

 

[absolutered03]

Chris,

Thanks for the response! I basically have it setup in that fashion currently. My only issue is that there is ~40 questions per call, so I would like the questions to auto-populate on the subform for each new call record, so that they dont have to pull down each question first.

Not as important would be i think to have the questions show up in text boxes instead of combo pull downs.

Any ideas?

Anyhow, I again appreciate your help and look forward to any other advice!

Thanks!

 

[absolutered03]

I found some code on another thread that had a similar issue; it is an sql statement. I've modified it for my application, but where would I store it so it would do it for every next/new record?:

INSERT INTO [subfrmAnswers] (QuestionID, Call_ID, AnswerValueID) " & _
"SELECT QuestionID, " & Me.Call_ID & " FROM tblQuestions

 

[absolutered03]

ugh. Im a Noob!

I wrote an append query that writes all the questions into the tblAnswers.

Simple enough! haha.

Now, how can I get it to add the current CallID on the main form, run whenever a new record is created, and only if there are no records for that CallID in tblAnwers...so as not to create new records if they go BACK to a record?

edit: i guess that me.callID would work right, for adding the current CallID on the main form?

 

[stopher]

Hi again

One issue is that when you add a set of answers you will not have the AnswerValueID entered (because they haven’t been answered yet). So you need another possible answer of blank (I did this by adding 0 – blank to tblValues). Now when adding the questions, they will have a default of zero.

Of course, now it is possible for answers to be left blank. You may wish to at least have a check and a message telling the user when they exit the call record.

As you pointed out, you need to ensure that questions aren’t added twice. I have dealt with this by changing the database structure slightly. I have got rid of AnswerID from tblAnswers. Instead, I have made a composite PK from Call_ID and QuestionID. This ensures that a specific question can only be in each call_ID at most once. So when you run append query (INSERT INTO…) it will only add questions where it can.

As to the solution on how you insert, yes you can run the query you describe. You need to do this using an “event”. Events are things that happen when certain events take place e.g. On Open is the event that is fired when the form is opened (there are loads more). When the event happens, it can be made to run a macro or VBA code. I used the After Update event to fire some code that runs the query. The theory being you only really want to add the detail questions when the user has filled in the Call information. In fact you wouldn’t be able to add Answers until the Call records had saved (hence using After Update) This works fine but might not be perfect as the user actually has to exit the record to fire the code (which they would do if they clicked on the detail part or tabbed to the detail part for instance). So in my example, they could enter 1 in Reviewer_ID then wonder why the questions haven’t appeared (until they tabbed).

Just using the After Update event as describe above will get you going. If you want to address the issue of not tabbing, then you have to think more carefully. As mentioned, the Call record has to be saved before you can add Answer records. Simply typing in the boxes does not save it as a record in the table. Normally the save is initiated when the user move away from the current record e.g. by closing the form or moving to the next record. You can force the save with the following:

If Me.Dirty Then Me.Dirty = False

The Dirty attribute of the form is set to true if any changes are made on the form. By setting it to false, Access is forced to save the record (otherwise the attribute would be reporting incorrectly).

So you could consider using the On Change event for the Reviewer_ID control for example. However, again you need to think about this. You can’t save a record unless all the data for the record is valid. So you might want some validity checking with appropriate messages.

Take a look at the example. FrmCalls uses the simpler After Update approach. FrmCalls2 uses the After Update of Review_ID approach (without validation).

Apologies if all this is stating the obvious. Always difficult to know where people are up to knowledge wise.

Chris

 

[absolutered03]

Chris,
Thank you very much! This method populates the questions in the table correctly, and has gotten me one step closer to the functionality I need for the database.

Now that the questions are populating on tblAnswers correclty, is there a way to create a pulldown combobox, unbound, that when an answer is updated it will update the record for that AnswervalueID? It looks like I can use the SQL statement used to insert the values in the tblAnswers, but modified slightly to only add the AnswerValueID for the specific Call_ID and QuestionID?

 

[stopher]

Now that the questions are populating on tblAnswers correclty, is there a way to create a pulldown combobox, unbound, that when an answer is updated it will update the record for that AnswervalueID? It looks like I can use the SQL statement used to insert the values in the tblAnswers, but modified slightly to only add the AnswerValueID for the specific Call_ID and QuestionID?

I'm not sure I understand your question. When you use the Answer combo in the subform, it stores the value in tblAnswers. So if you answer Yes, it stores 1 in tblAnswers. No need to do anything else.

Chris

 

[absolutered03]

Sorry for the confusion.

The functionality of the subform works great...it does exactly what it is supposed to do.

However, if it is possible, it would be better if I didn't need to use a subform in datasheet format. Or perhaps if there were a way I could format it differently...?

My manager wants me to try and emulate an excel spreadsheet we currently use, including laying out the questions in sections with a note area per section.

I had originally designed the database with all the questions as fields in tblCalls.

I was told however, that this would not work the way I wanted it.

Now that I have the tables setup properly, and the information in those tables populating correctly, I just wanted to see if there was a way I could use the form design I had used for the originally tblcalls form, which only had the dropdowns for the values in tblValues (which in turn would store the values for the questions right in tblCalls) and next to the dropdowns were the questions as a label for that dropdown, rather than having all the questions jumbled on a datasheet and allowing the questions to be dropped down.

Basically, is there a way to do these questions without subforms...

 

[absolutered03]

I guess to be more specific:

1. I use the simpler after update method to populate the questions in tblAnswers for the current Call_ID

2. Using my main form, create combobox pulldown controls that relate to the newly populated records in table answers (ie. lets say I create control comboWelcome for question number 1, somehow code it so that when a value is chosen in the pull down that it will update Call_ID=Me.Call_ID, QuestionID=1, AnswerValueID = '<whatever is picked in the pulldown>'

That would enable me to keep all of the controls on the main form, and not use the subform 'subformAnswers'

I realize that I would need to add this code to each question control, but is it possible, since the records it would now be updating exist due to the afterupdate query being run?

 

[stopher]

My manager wants me to try and emulate an excel spreadsheet we currently use, including laying out the questions in sections with a note area per section.

Maybe you could post a picture as I can't quite see what you are trying to achieve.

That would enable me to keep all of the controls on the main form, and not use the subform 'subformAnswers'

What is it about subforms you don't like? You can format them so they blend with the main form. Nobody will know you are using subforms. You can also switch the view to "continuous" rather than datasheet. You can also have multiple subforms, one for each "set" of questions so they are nicely grouped.

If you really want a completely open design, you can create what is called an "Unbound" form. This is where you design the form freehand with all the fields you want but they have no connection to the tables. Instead you have to implement code that will write the data to and from the tables. This is certainly doable (and is often done) although it requires you to deal with a lot more that the database would have done naturally.

hth
Chris

 

[absolutered03]

Hey Chris,

Attached is a screenshot of the form the way I wish it could be done, in the 'unbound form' way. The top portion with simple call details could still be bound, but the bottome portion, with the questions I gather would have to be unbound.

Anyhow, I think with subforms, it just will be difficult with the longer questions...even in continuous form. Although, perhaps I'm just asking for too much.

Anyhow I am thinking about my options..but I think the unbound will be the way it will go unless I can play around with it to get it to look the way I want it with subforms.

Your advice and guidance is well appreciated! Thank you soo much for taking the time to help me with this...

 

[stopher]

Very useful pic and I can see why you are in this predicament. I will have a think but I have to go out now. I'll see if I can get some others to give an opinion as it is a good question.

Chris

 

[The_Doc_Man]

First comment: A boss who thinks "spreadsheetese" is going to be a pain in the toches at every turn. Unless he has talents you haven't described, he might not have the ability to understand the limitations imposed by spreadsheets or in fact any flat-file way of thinking.

Second, I have a question that may seem odd, but... what is the base table of your form? Let me explain that. Referring to your data structure from the original question, you have several obvious drop-down opportunities.

For instance, Call Types, Employees, and Reviewers are obvious candidates for selection via combo box or list box. But forms are based on a table or query. The query usually involves a selection from one or more tables, but in most cases only one table "drives" the content of the query. And therefore, only one table usually drives any forms based on that query, whatever it is. My question to you is, what drives your form?

I want to propose a thought to you that might be so nonsensical as to defy description, and maybe it will confuse you.

From your description, you drive the form off tblCalls. Your pain in the nether anatomy comes in representing the questions.

But just think about this. What if you rethink the design to center around tblAnswers as the driver, for which you select the call ID as a drop-down (or have a "New" function to add a new one) and then use continuous forms for the questions and answers as the primary feature of the table? I know it is a logic inversion, but if your problem is too many layers in the relationship, shift your focus to the part that is vexing you and handle the other part as the child form.

In either case, you have some code to write, but I'm thinking that the code might be less (maybe a little, anyway) if you drove the form from the Answers table, not the Calls table. On the other hand, this is a conjecture not strong enough to qualify as a bet. Just food for thought.

Not saying I know offhand how to make that work fully, but maybe by turning the problem upside down, you'll see something that helps you somehow. Get outside the box. (At least you'll get to see the outside of the box.)

 

[absolutered03]

bump!

Any ideas anyone? I'm still stuck on this...

 

[absolutered03]

You're correct, the table that drives the DB currently is tblCalls.

Those candidates for dropdowns- they are comobox lookups populating from other tables.

It is an interesting proposal that you've brought forth...and I'm still thinking my way through it to see if I can figure something else out.

and just so you know, the continuous form type is technically already working..just that I don't really like it that way because it is extremely cumbersome due to the number of questions.

The picture I've uploaded is simply how I'd *like it* to look and operate.

Using the code already provided by Chris, I've got the correct records being added to all the correct table.

So all of the information is there.
Now it's just a matter of writing some code that will tell each of those answer pull downs to record the chosen answer in the correct spot on the table.

Since all the questions are dynamically added once a new Call_ID is created..the code for the answers would logically have to work as follows:

Record chosen answer in answer drop box to Call_ID(current-must be dynamically created based on the new call_ID created in tblCalls), for questionID(hard coded question number), and AnswerValueID(value id for chosen drop down).

I will continue to think about this other option...but if any one else has ideas feel free to contribute!

Thanks so much and I appreciate everyones help.

and The_Doc_Man, I apologize, I didn't notice that you had responded..as your response ended up on page 2. Apologies for the delay in response!

 

[absolutered03]

Anyone else have ideas?

 

[absolutered03]

bump for more ideas