Problems stitching my forms together

[Linda354]

I'm creating a very long input form for an AA-type counseling group, about eight pages long with many, many fields. I found early on that I needed to break it down into smaller tables. Now I've created forms for each table. I'm creating a navigation that will lead us from one page to the next, like turning the pages of the paper form these people are accustomed to. I've set up the relationships so that all tables are linked on an "ID" field, and all the tables are joined to the first table, like a fan.

I'm trying to input now and that ID field is not remaining consistent on its own. I can't figure out how I set the intial ID as the variable to be used in creating all the subsequent "pages" (forms), I'm not even sure if that's what I should be trying to do.

Can someone point me in the right direction? This seems like it wouldn't be such an unusual situation, but I can't seem to find any examples on how to handle this situation.

Many thanks for your help. I'm stumped.

 

[jazzz]

You description is not quite clear. If you set up your tables and created the relationships correctly you should be OK if they are all on one form with subforms? Access will see to that by linking via a Parent Child relationship of the form.

If the additional forms are not on the main *parent form* rather they are individual forms that you are opening by clicking on a command button or something similiar then you need to tell access what to do.

You can try a few things, such as the openargs would be one way to carry your ID to the next form. I have a database where I open up a customer order form based on the current customer here is how I handle it. On my order form I have an object called CustIDFK. This is my link to my main customer Form which has a PK called CustIDPK, so in the default value of the object CustIDFK on the customer order form I placed the following =[Forms]![frmOrders]![CustIDPK] so now the order form is in sync with the main customer form via the CustIDPK *Customer ID Primary Key* which is associated with the order table by CustIDFK *Customer Order Foreign Key*. This is how Access now knows how to handle a New Order for my customer.

One more thought, you mention you created individual tables for your data which I am sure is a good thing. You are aware you can build a query and link your tables in the new query? Then add tabs or create page breaks to your form to input your data? Which in theory would eliminate opening all these extra forms?

I hope this helps or at least gives you a starting point? Life's a journey enjoy the ride...

jazzz

 

[B827]

After a brief look at this I would say that if you set everthing up correctly then a Master Form with a series of sub-forms on a Tab Control should emulate your paper system.

You'll have to set up Relationships and Referential Integrity so that the tables are linked. The Help covers this quite well. Sandy

 

[Linda354]

I started out thinking I would set up subforms, but couldn't see how I could be creating separate pages with them. I didn't trust Access to run a continuous form eight pages long without crashing.

I don't yet understand queries well enough to do what you're saying. Better get my head back into some books.

If I do the master form with the tab system, will the users be able to see what pages they've filled out already? I'm concerned pages will be skipped inadvertently if I use tabs.

Anybody have any idea which of these options will be the easiest to switch to?

 

[jazzz]

Linda, there are a lot of ways to assure you get complete forms/tabs. Which would require more space than allowed to cover it here. For example the before update event of the parent form you could place a value in the tag property of all the required objects and then call a function or sub to cycle through your controls to assure that you got all the required data based on the tag value of the control.

Another way to handle it is in your main tables set the fields as required to be sure that they receive a value. Now bear in mind you will generate errors if these fields are null or left blank so you will need to handle such errors at the form level to assist the user in determining what field is blank or what the Access Error is trying to tell them you would write your own Error Messages. There are many solutions I just don't know what you are trying to accomplish.

Finally a query is nothing more than a copy of your table *in theory* *BUT* you now have more ways to control the data that you will display or how you will display it. Also, you can create joins within the query to other tables along with creating expresssions in the main query itself. As a habit I base ALL my forms on queries it is a matter of preference and good database design habits. Life's a journey enjoy the ride...

jazzz

 

[Linda354]

Thanks, since my invidiual forms are all set up, would I loose ground switching to a query approach? I know the tabs method would quickly stitch together everything I already have.

Thanks again, I've got plenty here to digest.

 

[jazzz]

Linda, you would loose no ground by switching to a query. All you would need to do is switch the forms source to your new query as opposed to your exsisting table. None of your fields or objects would change. They would be based on the same data.

Try this click on queries, click on New, choose design view. Now select a table that one of your forms are based on, and select it. Close the Show Table box. Now you should have the table on the top half of the design view select all the fields in the table by clicking on the first one and scroll to the bottom, while holding the shift key down select the last one, this should select all the fields. Now drag them all into the bottom half of the query design and drop them on the field they should now be filled in. Save your query now qryMyQuery. Your done.

Now to protect your form that is based on the table which we want to change to a query create a copy so we don't damage the original. Click on the form hold the control key down and move the form up in the window and release the control it will now create a copy.

Open the copy of the form in design view and select properties then the all tab now in the all tab select the qryMyQuery *or whatever you named it in the Record source*. That's it your done. Open up the form and it should work just as before. If you referenced the table in any code you could now change it to the query if need be. Life's a journey enjoy the ride...

jazzz

 

[Linda354]

Thanks so much for taking your time to explain all this. I appreciate it very much.

So you think I will be better off using the query approach? Keep in mind that I may in theory have more control with the queries, but seeing as how I don't know how to USE that control yet, I may be a menace to myself.

I think I follow your instructions, except for the part about copying the form. Couldn't I just Control-A, Control-C, then Control-V it the way I usually copy things? Or just cut and paste through the menu? Or for that matter, just right clicking on the name of the file and clicking "copy? Is that what you're talking about? Will those methods produce the same result?

 

[Bry12345]

I hate to throw this thread off in another direction, Linda, but are you really sure that you need multiple tables? Unless there is some relational issue here, the fact that you have a lot of fields to fill is not usually a good reason to use multiple tables. (There may be some other reason, tho?)

It might be better to use one table a multiple forms where each form fills in a portion of the table. This way you get around the relational issues.

Bry

 

[Linda354]

I started at doing that and I froze up with the message that I could only have 255 records in a table. And I was only half way through. I've still had a lot of trouble with that "memory leak" business and having to re-boot every hour, or after any amount of edits on a couple of forms it still freaks out.

It sure would be simpler to have it all together, but at this time it's broken into logical sections.

 

[B827]

You guys have obviously been having an interesting discussion overnight (for me). Just to add to the dicussion so far I have a system which has 20 sub tables along with a master record. Not all tables are populated for each master but any 3 are as a minimum. The number of records in the subtables can range from 1 to tens of thousands.

I have used the sub forms in the tab control as the method of displaying the data in some sort of order. Performance can be a bit slow but that is out weighed by the ability to see all the dataon a single form.

Because of the number of Tabs I put in a navigation assistant using a Combo Box and the afterupdate property. I have to display the record type (a 3 digit number) rather than a description on the Tab.

Although the system only has about half a dozen users it seems reliable enough. Sandy

 

[jazzz]

Linda, good practice is using queries Access Developers Handbook is a good source for explaining all of this. It is very simple to copy the form as I described. Click on your original form in your object pane now hold down the control key and your left mouse button and just move the form up your cursor will display a square and a plus release the mouse button and you will now have a copy of your form. You can rename it to anything at this point just don't use an exsisting name. Life's a journey enjoy the ride...

jazzz