Full Version: Subform help
UtterAccess Forums > Microsoft® Access > Access Forms
HongHu
Hi, I'm trying to develop a database that tracks questionaire handlings. The process is like this: A subject have multiple visits, in each visit, certain questionaires are distributed, and they will be collected in next visit. I need to display a checklist for each subject, each visit, and the person who handle the visit will be able to check each item off. I have three tables for this:
ain table
SubjectID (PK)
CurrentSession
RequiredQuestionaire table
ActionID (PK)
SessionID
ActionTypeID (distribute, collect, etc)
QuestionaireTypeID
QuestionaireLog table
QuestionaireID (PK)
SubjectID (FK)
ActionID (FK)
Completed (Y/N)
I'm thinking to use a subform to display the required questionaires for each session, and to insert a record to the QuestionaireLog table when the completed field is checked.
Right now my main form is based on the main table, the subform is based on a query that joined the RequiredQuestionaire table and the QuestionaireLog table, with the CurrentSession = SessionID as the link between the form and the subform. I can get the correct display of list of required questionaires for each session, but how do I make it automatically fill in the SubjectID field in the subform and before the record is inserted to the QuestionaireLog table? Or is this not the correct way to do it?
Also, I would like to insert a check that all required questionaires are completed. The idea is to count the total records in the subform and the total number of "yes" for the completed field. How do I refer them with VB codes?
I'd greatly appreciate any helps!
strive4peace
Based no what you said, expand your link to:
inkChildFields --> SubjectID, SessionID
LinkParentFields --> SubjectID, CurrentSession
on the subform
SubjectID:
Visible --> No
HongHu
I'm still having problems with this. When I link the subject ID fields, the required questionaire list doesn't show up any more in the subform, I guess because there isn't any record in the questionaireLog table yet. I think something is wrong in my way of doing things?
strive4peace
sounds to me like your subform is not set up to allow data entry either...
orm Properties:
AllowEdits --> Yes
Allow Additions --> Yes
Data Entry --> No (this means addition ONLY)
also, until you get this problem sorted out, make all your fields visible
make sure the data type is the same on the fields you are linking
also, make sure that the control NAME matches the CONTROLSOURCE property on your fields
HongHu
Ok I'm attaching a small test database with the three tables, the query, the form and subform. I would greatly appreciate if you (or somebody else) could give it a look and let me know what I did wrong? Thanks so so much!
The thing I wanted to achieve is when I open the main form, I would enter the session number (say 1) in the main form, and then the subform will list all the required items for session 1. Then when I check the yes/no field, the record gets to be entered into the action log table.
Thanks again!
S_R_T
i think you should just post the whole thing, i would like to try to help you but i am not very good with little piece i need to see the big picture lol
strive4peace
the reason that you cannot add records to the subform is because you have a many-to-one relationship in your query that the subform is based on.
Here is your database back. I have made some changes to get you on the right path...

you need to rethink your logic -- base your subforms on tables, not queries
changed RecordSource of your main form to "Main Table"
created "ActionLog_subform" based on Action Log table and replaced your subform
made the TabStop property = No for Action ID since it is an autonumber field
In Action Log table, changed the DefaultValue property to Null for Subject ID and Action Type ID since 0 (the current DefaultValue and what Access always picks for numeric fields) will never result in a valid reference.
Change DefaultValue of Completed to No
Created RequiredActions_Subform based on Required Actions table
Changed PrimaryKey in Action Log table to the combination of Subject ID and Action Type ID
Created "Creat Actions" button on the main form that runs the query qry_Create_Actions_for_subform
Note: Do not use spaces in table names or fieldnames; it is allowed but it will bite you later. I have left them for you to change if you wish.
HongHu
Thanks sooo much!!! I will look at it now and see if I could understand everything.
HongHu
Ok I think the major thing I realize is that I have to separate the listing of required items and record the complete items. I will make these changes in my real database and see if it will work. One small question. If I use the combination of subject ID and Action Type ID as the primary key in Action Log table, do I really need the autonumbered action ID field?
nyway, it is very very helpful! Thanks so much!
HongHu
It's going pretty good, except for one problem. I was using another control to change the session number. It's two check boxes. When I check the first one, the current session becomes 1, and when I check the second one, the current session becomes 2. However, although the session number did change in the form, when I press the button to do the append query, the session number in that query appearantly didn't change. Do I have to force save to the table or something?
HongHu
In other words, when I press the button to activate the append query, it tells me that it was adding 0 records, because the session ID field was empty. Then if quit the form, and reopen it, and then press the button, it will do it for me. What's the problem here?
strive4peace
I need to better understand what is the purpose of this...
Are you creating records in the main table, or just using the form to generate action items?
there were no descriptive fields, so it was difficult to understand the benefit of the database
Action ID is probably not necessary in the Action Log. The reason I used the combination of fields for the primary key is so that when the query to add action items is run, it does not duplicate records.
In order to force Access to change the Required Actions display when you change the current session ID in the main form, you can save the record -->
on the left, click the pencil in the long record selector are of the main form -->
this makes Access write the changes to disk and the subforms will be rfreshed
HongHu
Yes! That little pencil sovles my problem. However, is there a VB code I could use to do the same thing? I'm hoping I don't have to train the user on this kind of details.
HongHu
Looks like this should solve this problem right?
If Me.Dirty Then Me.Dirty = False
strive4peace
clicking on the pencil forces the record to be saved
oCmd.RunCommand acCmdSaveRecord
HongHu
I read another post by mishei that says:
The .Dirty property is a property of a bound form. The advantage of this method over other methods is that the save is applied to a specific form whereas other methods rely on the form having the "focus". Depending on a form to have the focus so that you can do something like:
DoCmd.RunCommand acCmdSaveRecord
is dangerous since it is possible that the form where you intend to save a record may not succeed if somehow the focus shifted somewhere else. You want to be as specific as possible when specifying what to save and that's why using a reference to the form (by using the "Me" reference) is a good thing. "
Anyways, it works! Thanks very much Crystal!
strive4peace
Glad you got it. You're welcome wink.gif happy to help
I have not heard of using the Dirty property this way, thanks for passing on the info. I would trust anything that mishei says, he is brilliant!
mishej
Thank you Crystal for the nice words but I'd caution readers that I make mistakes too and I would be concerned if anyone took my help as always correct or alway the best way to do things.

The issue that I was addressing is a small possibility that commands that rely on the focus being set to a particular object can be the source of frustrating hard to reproduce errors. Another good example of this is the common code (that even the Microsoft wizards generate):
CODE
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
    DoCmd.Close
Exit_Command0_Click:
    Exit Sub
Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

This is directly from the Command Button wizard. Note that the DoCmd.Close will close the object that has the focus. Normally this would be the form that has this command button in it. But there is a small chance that after clicking on the button but before that line of code executes, the focus inadvertantly shifts to another form or an open report, then that form or report is closed.

Oalways modify the above code to this:
CODE
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
    DoCmd.Close [color="red"]acForm, Me.Name[/color]
Exit_Command0_Click:
    Exit Sub
Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

Note the text in red which indicates I want to close a form that has the current form's name. Even if the focus shifts to another object, the form I want to close will close.

Small issue unless you get hit with this problem! BTW.. I learned this from more seasoned Access programmers than myself years ago....
strive4peace
great point! Thanks, John.
Have a Happy Thanksgiving week!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.