Making a normalised table have a decent form

[oli82]

Thanks for your help in advance, we have built a normalised table and now have the issue of trying to get this data in a form view to both view and edit the data. I have having trouble with which method to use to do this.

I have investigated a few methods ADO looks good but I don’t know how I can pull back all the records for a project at once – it would be fine if it was only one record at a time but this isn’t the case the aim is to show all the different properties that could be populated. Is it possible to run an ADO record retrieval for each property in a project and then the next record button changes moves to the record for each of the properties which have their recordset filtered by property – so only values for Property 1 are shown in the Prop1 field of the form.

The other option was to try to run T-SQL which pivots the data and then presents it in an unnormalised form which would be easy to create a form for – however I don’t know if this will work because in some cases there is more than 1 value for a property.


Thank you so much for your help, I just need some guidance as to which path to go down.


Oliver

 

[DCrake]

You could create a layout to match your example and have each property value expressed in a combo box. So in effect you would your project number in the form header. Then in the detail section you would have 8 combo boxes (2 x 4) spaced out on the screen each combo would have its own row source such as



Combo Box 1
Select [Value] From Table Where ProjectId = 1 And PropertyID= 1


Combo Box 2
Select [Value] From Table Where ProjectId = 1 And PropertyID= 2

Etc

Where 1 is the currently select project and PropertyID is a known Unknown.

If the Project doe snot have a matching PropertyID the combo box will not have any items in it.

To be able to add to your table you will need to employ the NotInList functionality With the Limit to List set to True. So if a user enters a value in the combo box that does not already exist the user can be promted as to whether they want to add this value to the propertyID or not.

Then in you form footer section you can have you < Back and Next > buttons.


David

 

[oli82]

DCrake,

Thanks for this, it's a great start. Do I need to bind the form to a control source to get this working, at the moment I have combo boxes like above but they do not display anything only when you drop down do they display the value - as opposed to holding the value inside the box.

Cheers,

Oliver

 

[oli82]

To better explain this if I link the form to the data table and then set up the combo's the drop downs work but the actual values are displaying the first record.

Where am I going wrong. Many thanks for this help.

Oliver