Form "with row and collumn headers" (subform and many to many relatioship) (1 Viewer)

[Corneliu]

Hi, I was trying to get some help on how to create a form where you chose a record from a table and then you can fill with values on the "line headers" from another table.

It's like a cross-reference query, but instead of doing some grouping you can fill the value, and the 1st row you select what field you want, and inte the lines you put the values from the fields of another table.

It's 3 tables: table1 has a ID PK, table2 has a ID PK and table3 has the 2 PKs and a field.

In the primary form you select one record from table1 with a combobox.

Then the subform has a recordset of table3 and you can enter the value for the field.

But instead of picking the ID from table2 with a combobox, I wanted to display all ID's from table2 so I can just enter the value on the field.

I tryed a continous form, but it gets errors. You cant put subforms in continous forms...

Maybe there's some VBA where you need to create blank rows? But Isn't that bad? Having black rows?

 

[theDBguy]

Hi. Welcome to AWF!

But Isn't that bad? Having black rows?

Yes, I wouldn't recommend creating/using placeholder records.

 

[Galaxiom]

It's like a cross-reference query, but instead of doing some grouping you can fill the value, and the 1st row you select what field you want, and inte the lines you put the values from the fields of another table.

What you refer to is called an Entity-Attribute-Value data model. It has been discussed at length before on this site.

The other alternative is to use a form where all the lines are available but only those where values are entered are stored in the table. See how this is done in the sample database in post 3 of this thread.

 

[Corneliu]

I tried to do it, but it didn't work as intended because I wanted to select from the main form the record that I want, in that exemple: the instructor.
The forms arent linked.
Because they aren't linked, in my db, for exemple, its show in the subform the records that are already exist on the table that has the 2PK.
But i wanted also to show the rows from the other field of the other table.
In my dat base, in the form "Cópia de Misericóridas", and in the sub form, i wanted to display all fields from "rblValências" so I don't have to check what is every "Valências", every time i want to inser data from each "Misericórdia".

 

[Corneliu]

"The other alternative is to use a form where all the lines are available but only those where values are entered are stored in the table. See how this is done in the sample database in post 3 of this tread."

I don't think that this exemples applies to my case.

And I tried to search about the Entity-Attribute-Value data model and in this case I think I'm building my DB correctly.

I just don't know how to create a form to be user-friendly and more convenient.

 

[arnelgp]

Is this what you have in mind?

 

[Corneliu]

At short term yes.

That's the appearence that I actuallly wanted.

But in the DB it creates the blanck values that I was talking about.

Maybe there's no way to get pass that.

 

[arnelgp]

But in the DB it creates the blanck values that I was talking about.

Maybe there's no way to get pass that.

i do not understand this portion.

do you mean there is still "predefined" values for each specific record.
you can always put them to a table and then lookup the values and put it
in your form.

 

[Corneliu]

Yes, of course I can create a new table with the values.

I thought there would be a way where the "predefined" values didn't had to be created in to the tables.

 

[arnelgp]

there are many ways.
use a form (open but hidden) with "predefined" textbox values or label captions.
use Global variables to hold those predefs.

 

[Corneliu]

Global variables on VBA?

How do I display the predefined fields with those variables?


Before this I tried to use a subform of the predefined values, I just couldn't link to the values of the table with the 2 PK.