Make a form with much tables

[SoulGame]

Good people, I have to create a form to fill 4 different tables and the only way I have found, to do it, is to add subforms in the form, linking the ID fields to be autoplanted, I attached an image of the form 'shoddy' Which I have performed.




I wish I could have the free fields in a form to order them for my decision, but being inside a subform I can not. How can I get the same result without subforms?


Thank you very much.

[CJ_London]

subforms is the usual way to go.

I don't understand what 'I could have the free fields in a form to order them for my decision' means.

you can try using a single form based on a query which links all the tables together, then in your form properties, change the recordset type to 'dynaset inconsistent updates' but this only works if the joins are straightforward and there are not many and you may need additional code to populate family keys in the form before update event

[Missinglinq]

...How can I get the same result without subforms...

The only practical way is going to be using an Unbound Form and this will require a huge amount, especially for an Access novice, of VBA code, to populate the various Tables. It will also mean that you can't view existing Records using the same Form that is used for data entry, nor use the same Form for editing Records, without an equal amount of code to retrieve a given Record for editing.

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

1. Do Data Validation
2. Prevent Duplicate Records
3. Do Formatting of Data before it's Saved
4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, including data entry, has simply made a bad choice in deciding to work in Access.

Linq ;0)>

[NTC]

perhaps there is a misunderstanding: a form is always used to input info into a table - - as to put data directly into a table you don't get the benefits of the features a form offers.

Depending on the structure of the data - sometimes a query can join tables. Then a form can use the query, rather than the individual tables. But sometimes this is not possible, again depending on the structure of the data.

So there is going to several forms involved - and the only question is whether they are stand-alone forms or whether they are related. If the data structure is Parent:Child (1:Many) then you set up a Main form and insert the 2nd form as a sub form.

If the data is not related then just have separate forms. There is no reason - either way - to put separate forms together, unbound, into the same form - except for a desire to have that type of user interface. In some cases perhaps one does want that - in other cases maybe not. It really is just up to the way you want things to be seen.

[Micron]

My suggestion would be to build a single select query from the tables and if you can update ANY field and add records, a single form is the way to go.
On this sort of issue, my mantra is, working query first - form second.
Joins are not the only reason a query may not be updatable. If one of the 'tables' is a totals or union query or involves calculated fields, the final query may not be updatable.
Then again, if the OP currently has a form/subform relationship going, is that because the situation is one-to-many, or is it not and it ended up this way due to not knowing how to do it in one form?

[SoulGame]

subforms is the usual way to go.

I don't understand what 'I could have the free fields in a form to order them for my decision' means.

you can try using a single form based on a query which links all the tables together, then in your form properties, change the recordset type to 'dynaset inconsistent updates' but this only works if the joins are straightforward and there are not many and you may need additional code to populate family keys in the form before update event

Hi, thanks for the reply, with 'I could have the fields free in a way to sort them for my decision' I wanted to say that the fields of the form I need to put them anywhere.


I will prove to make a query and with the query make a form.


Thank you !

[SoulGame]

The only practical way is going to be using an Unbound Form and this will require a huge amount, especially for an Access novice, of VBA code, to populate the various Tables. It will also mean that you can't view existing Records using the same Form that is used for data entry, nor use the same Form for editing Records, without an equal amount of code to retrieve a given Record for editing.

Linq ;0)>

Hi, thanks for the answer. I know that it is expensive to create forms without saver anything, I like programming and that's why I got myself into this mess. I think I'll prove the solution you gave me.


My tables are all linked, I attach an image of my relationships.


I will try to make a query and with the query make a form.


Thank you !

[SoulGame]

perhaps there is a misunderstanding: a form is always used to input info into a table - - as to put data directly into a table you don't get the benefits of the features a form offers.

Hi, I have all the related tables, but as you can see I have some many to many relationships. Can you also link all the fields through the query?


And if you query the ID fields will autocomplete themselves when adding users?


Many thanks, to the post above I leave an image of my relationships with the tables.


The tables that need to be joined in a form are: 't_alumne' 't_familia' 't_parents' t_inscripcio'

[CJ_London]

I wanted to say that the fields of the form I need to put them anywhere.

you can, within the context of the same form i.e. you cannot dragea control from a main form to a subform or visa versa. You can turn off the autolayouts that access uses if you use the wizards to create a form.

[SoulGame]

My suggestion would be to build a single select query from the tables and if you can update ANY field and add records, a single form is the way to go.
On this sort of issue, my mantra is, working query first - form second.
Joins are not the only reason a query may not be updatable. If one of the 'tables' is a totals or union query or involves calculated fields, the final query may not be updatable.
Then again, if the OP currently has a form/subform relationship going, is that because the situation is one-to-many, or is it not and it ended up this way due to not knowing how to do it in one form?

Hi, to the previous post I say the relationships that I need to join, most are one to many, but I have one of many to many that is: 't_escola' 't_inscripcio' 't_alumne'


With the subject of the queries I can solve it? If so, what would be the best?


Thank you

[Micron]

I can't see some of the symbols in your relationships picture so I might be wrong when I say I don't see the many to many relationship you speak of. Then again, I can't read the words either. About all I can do is reiterate what I've already said. IF you can create a query and IF that query is updatable, that is what is usually best to do. If not, an unbound form is probably your only other option as was already mentioned. Sorry, but I cannot tell from here if it is possible just by looking at your posted image.

[SoulGame]

I can't see some of the symbols in your relationships picture so I might be wrong when I say I don't see the many to many relationship you speak of. Then again, I can't read the words either. About all I can do is reiterate what I've already said. IF you can create a query and IF that query is updatable, that is what is usually best to do. If not, an unbound form is probably your only other option as was already mentioned. Sorry, but I cannot tell from here if it is possible just by looking at your posted image.

I am trying to make a query with all the fields that I need, but at the time of creating the form it will not let me add the fields. Same wrong query. I am a newbie in Access, do you recommend any guide?

The relations many to many is 't_escola' > 't_inscripcio' > 't_alumne'
Attached again the image of the relations:

[Micron]

For me, there is a language problem that is not helping. Not that I entirely trust Google translator, but it seems this is Catalan or some form of Spanish, so I have no hope of reading it. I'm not even sure if what I write here is being completely understood. This means I cannot understand your relationships or if things are properly normalized. I have tried to do this using a translator, but it doesn't make much sense. About all I can get out of it is that there is some family and student related data - after that, I don't understand what you have.

In two different posts, you have said

The relations many to many is 't_escola' > 't_inscripcio' > 't_alumne'
The tables that need to be joined in a form are: 't_alumne' 't_familia' 't_parents' t_inscripcio'

What I think I see:
t_familia.CodiFamilia > t_alumne.CodiFamiliaAlumne (one to many)
t_alumne.CodiAlumne>t_inscripcio.CodiInsncripcio (one to many)
t_familia.CodiFamilia >t_parents.CodiFamiliaParten (one to many)
I don't see a many to many in those relationships, or using the same logic, in 't_escola' > 't_inscripcio' > 't_alumne' (I don't actually see a "t_escola")
Note that I suspect a spelling mistake on CodiTipusFamiliaParten, but I doubt that is your problem.

All things considered, I don't think I can help with what I can't read, and you haven't posted any information about your actual query anyway. Perhaps another forum member can enlist the help of someone who speaks the language and knows Access. Sorry.