HELP! SUBFORM issue!

[Belinda7237]

I have created a form that will be used for data entry. The main form has a
couple of fields, and then i have used the tab control feature to add an
additional 5 tabs. with many more data entry fields. Fields on these tabs
sit on two different tables, and 2 of the 5 tabs are from subforms.

I have a final tab that is a summary of the results of the 5 tabs which is
compiled using a query.

I have two questions/problems:

First, I have linked my subforms to the main form using a field that is
common in both tables "stats package number". When I get to my summary page
- it doesnt automatically polulate the totals. If I advance to a new record
and then go back it is populated.
Is there another way I can have the summary automatically populated when
they open the summary tab? I have also found that if I hit F9 the summary
populates, however one of my totals does not populate. I think this is a
result of a subform issue. Why doesnt that tabs total populate? If it was a
subform issue then my other subform shouldn't populate either but it does?

My second question is: What is the command I would use to have my form upon
finishing a record automatically advance to a new record upon hitting a
complete button?

 

[John W. Vinson]

I have created a form that will be used for data entry. The main form has a
couple of fields, and then i have used the tab control feature to add an
additional 5 tabs. with many more data entry fields. Fields on these tabs
sit on two different tables, and 2 of the 5 tabs are from subforms.

Well... stop. You've got it backwards, or at least you're phrasing it
backwards!

The tabs aren't "from subforms". The tabs don't "sit on different tables".

The tables are primary; the form is just a window, a tool to edit data in the
tables. The tab control is just a way of managing screen real estate. The
controls on the main form - whether or not they are on a tab page - are either
bound to a field in that Form's Recordsource, or they are unbound and will
need code to manage them. Similarly, controls in a Subform (whether that
subform is on a tab page or not) are bound to the Subform's recordsource.

I have a final tab that is a summary of the results of the 5 tabs which is
compiled using a query.

I have two questions/problems:

First, I have linked my subforms to the main form using a field that is
common in both tables "stats package number". When I get to my summary page
- it doesnt automatically polulate the totals. If I advance to a new record
and then go back it is populated.

You can Requery the Subform that you presumably have on the fifth page, in the
afterupdate event of the mainform and/or each subform which contributes to it.

Is there another way I can have the summary automatically populated when
they open the summary tab? I have also found that if I hit F9 the summary
populates, however one of my totals does not populate. I think this is a
result of a subform issue. Why doesnt that tabs total populate? If it was a
subform issue then my other subform shouldn't populate either but it does?

Since you didn't post your code or the control source of this subtotal, all I
can say is "there's something wrong with this subtotal".

My second question is: What is the command I would use to have my form upon
finishing a record automatically advance to a new record upon hitting a
complete button?

What constitutes "finishing a record"? The mainform's record will be saved to
disk ("finished" by one definition) the instant you set focus to any control
on any subform; a subform record will be saved to disk when you move off the
record, or move off the subform.

You can put code in a command button's VBA to move do a new record:

Private Sub cmdNext_Click()
DoCmd.GoToRecord acDataForm, Me.Name, acNewRecord
End Sub

 

[Belinda7237]

Thanks John - i didnt explain very well, though I am a novice - I had to
create two tables because by initial main form with the 5 tabs counldn't hold
all of my fields. I have 140 total fields (these are combo boxes that the
user has to select if the item was correct, Yes, No, NA and I have a default
of NA) then I have a calculated field next to it for errors and potential
errors so that if the answer is Yes, then errors = 0 and Potential errors = 1
etc) so each field needed 3 fields on the table - thus I needed two tables to
hold all the data. So I built a couple of subforms off of my second table
and used the tab controls so the user doesnt feel overwhelmed by all of these
fields (Its a quality review process that we are building).

I inserted the requery at one point on the subform but the query table came
up when i inserted that code and I didnt want that to happen so I took it
off. The summary tab is built off of a select query that basically adds all
of the fields on tab one and inserts it, then all of the fields in tab 2 and
inserts it etc. then the summary tab is merely pulling from those total
fields on the query.

It seems that the tab that is giving me the trouble on the total of the
summary field is a subform, and when I look at that forms table, the records
seem to be duplicating each other. I have the forms linked by the stats
package number, however, I took the primary key off of the stats package
number becasue i was getting an error that said the record is being
duplicated due ot a primary key issue - but it does that on launch of the
subform and the subform is only pulling the stats package number from the
main form so i dont know why it would be duplicating.

I dont think the form knows when to go to a new record so I will try your
code to see if that works. I have a start button, that itserts the now time
into it, and a complete button that inserts the now time into it so that i
can calculate how long it takes to complete - so i can insert the code after
the completion button is clicked.

can I send you my form so that you can see the code for the summary page?

Thanks again!

 

[John W. Vinson]

Thanks John - i didnt explain very well, though I am a novice - I had to
create two tables because by initial main form with the 5 tabs counldn't hold
all of my fields.

50 fields is an ENORMOUSLY wide table. I needed 60 once.

If you have more than that *your table design IS WRONG*.

I have 140 total fields (these are combo boxes that the
user has to select if the item was correct, Yes, No, NA and I have a default
of NA) then I have a calculated field next to it for errors and potential
errors so that if the answer is Yes, then errors = 0 and Potential errors = 1
etc) so each field needed 3 fields on the table - thus I needed two tables to
hold all the data. So I built a couple of subforms off of my second table
and used the tab controls so the user doesnt feel overwhelmed by all of these
fields (Its a quality review process that we are building).

No. You need *three tables* - Questionnaires, Questions, and Answers. Rather
than 140 FIELDS for your 140 questions, you need 140 RECORDS in an answer
table.

See
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'
for a properly normalized approach - one which will not (as your design does)
require restructuring your table, all your queries, all your forms, and all
your reports whenever you need to add or delete a question.

can I send you my form so that you can see the code for the summary page?

Sorry... but I'm not willing to help sustain a flawed design. I would really,
really recommend that you step back, stop "committing spreadsheet" and
normalize your tables! You will find the application much more flexible and
more usable in the long run (however painful it is for now).

 

[Belinda7237]

thanks, I have revised my tables and forms so that I have created three
tables as you suggested. I certainly understand the benefit, and since I am
a novice, I didn't plan out my my end stat as well as I could have.

Thanks also for the sample site.

 

[John W. Vinson]

thanks, I have revised my tables and forms so that I have created three
tables as you suggested. I certainly understand the benefit, and since I am
a novice, I didn't plan out my my end stat as well as I could have.

That's a *very* common trap to fall into, so don't feel bad about it! Glad you
were caught before you had a ton of data entered.