Please advice, thank you.

[joe789]

Hi Folks,

I would like to (have to) automate a way to collect some survery data for a non-profit governmental agency into a database... the problem is that, each one of the surveys that come in from the agencies are 24 pages in length and therefore add up to several hundred fields on forms in the access database I created.

Reaching the maximum size on each form, and the maximum number of fields in each table behind the form ... I basically had to use 4 forms with each form covering 6 pages of the 24 page survey. Furthermore, the first and second form (pages 1 - 12) utilize one table, whereas the third and forth form (pages 13 - 24) use a seperate table. The multiple forms and multiple tables were the only way to get around the limitations of access (both limitations on the form size and limitations on the number of fields permissable per table).

My problem is basically a spanning problem: After someone enters a new survey into the database via Form1 which covers the first 6 pages of the 24 page survey (let's assume this is the 4th survey the person has entered into the system ... so it would be record 4) and then that person goes to the next form covering the next 6 pages, it of course takes them back to record 1 because this is a new form (but that form is from the same table as the first form ... just the last half of the fields involved in that table). Is there anyway to force the spanned forms and tables to maintain consistancy ... so that the person entering data won't end up entering 4 surveys with the first form to end up realizing that all the other forms that have overwritten the previous data? This is the same problem that would occur when jumping from form 2 to form 3, as form 1 and form 2 are 1 table ... and form 3 and form 4 are a second table. Is there a way you can maintain consistancy using something like keys or something ... this can get pretty complicated. Any help would be greatly appreciated.

Thank you very much,

Joe

 

[TessB]

I am assuming you have a primary field that links all these tables together such as an AutoNumber for the first table and Number for the rest of the tables joined to AutoNumber on the first table.

First of all, let's initially open the forms in the appropriate view.
Make a form that the user can choose whether or not they want to add a survey, or view all surveys.

If they click "Add survey" it would open form1 in AddMode and on that click event, I would, for everyone's benefit, set a label's caption to a nice bold "You are ADDING a Survey!" (Otherwise, the other button would set the label to "You are EDITING a Survey!")

Upon completion of adding a survey with Form1, you have a command button to go to Form2. In that button's On Click Event, I would store the AutoNum into a variable "intAutoNum", close Form1, Open form2 to find record where AutoNum field = intAutoNum. Set label's caption on form 2 "You are ADDING a Survey!" (If they were editing, the code would be the same except for setting the label's caption.)

When you have to enter into a new table and new form, if the label's caption reads "You are ADDING a Survey!" open the form in Add Mode and set the Number field to the AutoNum stored in intAutoNum. If the caption reads "You are EDITING a Survey!" open the form and find the record where Number = intAutoNum.

Is this making sense to you?

 

[joe789]

Thank you

TessB,

Thank you, I am going to try that right now. It makes complete sense to me. I will let you know if I have problems, but I don't anticipate any as this is a great idea and should work ...

Thank you very much,

Joe

I am assuming you have a primary field that links all these tables together such as an AutoNumber for the first table and Number for the rest of the tables joined to AutoNumber on the first table.

First of all, let's initially open the forms in the appropriate view.
Make a form that the user can choose whether or not they want to add a survey, or view all surveys.

If they click "Add survey" it would open form1 in AddMode and on that click event, I would, for everyone's benefit, set a label's caption to a nice bold "You are ADDING a Survey!" (Otherwise, the other button would set the label to "You are EDITING a Survey!")

Upon completion of adding a survey with Form1, you have a command button to go to Form2. In that button's On Click Event, I would store the AutoNum into a variable "intAutoNum", close Form1, Open form2 to find record where AutoNum field = intAutoNum. Set label's caption on form 2 "You are ADDING a Survey!" (If they were editing, the code would be the same except for setting the label's caption.)

When you have to enter into a new table and new form, if the label's caption reads "You are ADDING a Survey!" open the form in Add Mode and set the Number field to the AutoNum stored in intAutoNum. If the caption reads "You are EDITING a Survey!" open the form and find the record where Number = intAutoNum.

Is this making sense to you?