Form Cycles to New Record Before I Can Save???

[needadvice]

I have a two page form based on two tables. The first field on page one is (Request#). As I go to the second page, the fields are from the second table. If I enter data into any of these fields, when I return to page one Request# is reset to "0".
If I don't complete any of the fields on page two, then the Request# remains at the entered value. What's causing Requst# to reset to "0"? How do I stop this?

Any help greatly appreciated.

 

[mtnsurfer22]

Are the two tables joined in any way? Also, I assume from your subject that entry from page one is not being stored... but did you check your table and make sure that it is not storing it and then displaying a new record that is show when returning to page one? (obviously this would still be a problem but would require a different solution)

eric

 

[needadvice]

The tables are related by "Request#". The value in Request# on page one is not being stored. It stays until I enter a value in any textbox on page two which covers the other table. Request# is the PK of both tables and appends when a save is possible. The only time I can save the record is if I skip over the items on page two. Then the request number is as entered when I page up and I can save using the save command button.
Other items on page one remain as entered only the "Request number changes to "0".
Very confusing.

 

[mtnsurfer22]

OK...you have a couple of options. I think the basic problem is that Request# is the PK of BOTH tables. It is best when forming relationship if you have a Primary Table in the relationship. make another field in table two and call it Key (or anything you want) and set it as your PK and make it an auto number. Then form the relationship between the Primary Request# in Table1 and the other Request# in Table2. I assume since they were both primary keys before that you want this to be a one to one relationship. In which case you can set the Indexed property of Request# in table2 to 'Yes(No Duplicates)'. It seems that could also 'Enforce Referential Integrity' with Cascade Update and Cascade Deletes in your relationship.

Now, when you create your form you will have the option of viewing the fields from table two as a subform or a linked form. (the form wizard will walk through all of this). Both of these seem like fine ideas to me but if you still want to go with the page down deal then choose to view by Table2. Then you can insert your page break where ever you need. Be sure to include the join of table2 (request#). you don't need to view it on your form but it must be in the query of fields that make up your form. You will not need to append anything through a separate query because the relationship will be taking care of it.

I think this is accomplishing what you need and is making use of the power of relationships. the other thing you will want to consider is that in a lot of one to one relationships there is really no need for two separate tables and is sometimes better just to have one table with all the fields.

Hope this is what you need. Sorry if I missunderstood and your end goal is something different.

Eric

 

[needadvice]

A lot to think about. Actually a co-worker of mine hit on many of the same points. I'll give some a try. However, I found that if I put the Reqest# field at the end of page two, it all works OK. The return to "0" occurs before the data Entry into Request# and it saves the record as entered. Choosing the Request# at the end is probably not a bad idea. I've seen it done this way in other applications.