Help with Database Form

[msharohoney]

Hello Everyone,



I am new using Access Code and am working on a database derived from a SampleSurvey datatabase found on another site. In short, this database was designed to allow users to evaluate (or rate) certain areas based on their level of cleanliness. Users should be able to select an area, enter certain information (such as the date, time, etc…) and click an Evaluate button that opens another form. This form takes the users through a series of activities that they can rate for that area. There is an append query that will then take those answers and store them in a table.

However, I am experiencing some difficulty with coding to get the form to display the correct Activities for a particular area and append the records to the table. I’d like to be able to filter the number of activities that are rated based on an area’s selection. So, if users select an area – only certain activities for that area will appear in the second part of the form (and not all activities). I’d also like to be able to capture and append all other information (such as the date, time) in the table.

I've hit a snag and can't figure out a work around. Would anybody be able to help me with how to code for something like this or point in the right direction, it would be greatly appreciated. I attached a sample of the database that may help to clarify what I’d like to accomplish. Thanks in advance for any assistance!

Kris

[Micron]

Will take a look. In the meantime, check this out: your table info should be like this (at first I thought you may already know but were using text values until you get it right). I see that's not the case because you made Area a lookup field in your table. Not a great idea, and not normalized properly. I may have already hit on the problem...

EvaluationID	AreaID	ActivityID	EvaluationRating
1	2	1	2
2	2	2	2
3	1	1	3
4	1	2	1

[Micron]

I'm already stuck - there is no "tblResults" for qryAppendResults to append to. Did you forget it?

[msharohoney]

No, there is no tblResults - I am trying to get it to append to tblEvaluations which I suppose would serve as the results table in this database. I also see your example about saving the AreaID and ActivityID with the numerical values, but the department wants to be able to see what those values are, so that's why I did it like that. Thanks for the advice, I work on a better way to normalize the data.

[Micron]

Why would they care? They should not be looking at the tables but viewing/adding/deleting/editing data via forms. The forms display the desired values, not the id's. If you're allowing changes directly in tables, you are asking for trouble.
As for the missing table, how would you have us trouble shoot the db when code tries to run a query against a table that doesn't exist?

[msharohoney]

Not sure why they would care. Thanks for help on this, it appears that I have stumped the masses on this one. I'm working on an alternate solution. Hopefully it will be better.

[Micron]

it appears that I have stumped the masses on this one

Hardly.
I told you there was a missing table but you didn't provide it or a replacement database. Your query, as I recall, relies on the missing table.
Working on a better normalization scheme may be your best approach.

[msharohoney]

Okay, the table that you mentioned previously isn't missing per say, I just re-labeled from tblResults to tblEvaluations.

[Micron]

You just can't rename a table and expect your query to run. It is looking for the table by it's previous name. Given the age of your post, what is it that you'd like from us now?
If this thread is dead because you're working on an alternate solution, please mark it as solved so others don't keep looking at it. Otherwise, let us know what you'd like to do now.
Thanks.

[msharohoney]

Yes, I am aware but even though I've changed the query to point to the right table, I still can't get it to record the data properly and I am unsure why it's still looking for the table by its previous name - unless I've overlooked something somewhere. I don't understand VBA that well - this department just wants to be able to rate each activity for each area at one time but I need to record the data in such a way that it captures the date, time, headcounts, activity, area and rating as a new record in the table. So essentially they'd be creating multiple records at once. This is the best way to capture the data for reporting purposes. I don't know if this makes much sense but I haven't found any alternate solutions yet either because I've been busy with other tasks but ideally I would still like this to work. I just want to know what am I doing wrong and how I can correct it if it is even possible.

[Micron]

unless I've overlooked something somewhere

OK, I fixed the query so that the target table is now tblEvaluations. This was set by choosing the table to append to when in design view and you click on the Append button to set the query type and choose the target table. It seems you haven't gone there.

However, I am experiencing some difficulty with coding to get the form to display the correct Activities for a particular area

I'm not seeing a problem with this part. When I get to the second form and try to navigate through the records using your previous/next buttons, it provides a message that there are no more activities when I get to the end of the list (the list can be displayed by your qryEvaluationResults query).

record the data in such a way that it captures the date, time, headcounts, activity, area and rating as a new record in the table.

I'm not seeing how your db does any of this - there doesn't seem to be any provision for capturing all of that. Perhaps you were intending to get there when you solved the other issues. The first I'd say I've told you where the fix is. The second, as I mentioned, I see no issue with (the associated activities). Perhaps you'll be OK now that I've shown how to fix having the wrong target table?