User Interface for Custom Properties

[Fran Lombard]

In my db, ive created a feature I'll call Custom Properties. This feature is intended to allow additional User Defined information to be captured about a particular entity and then the value(s) assigned to these properties to be used at decision points in processing logic.

An individual Property is much like any standard Access Property in that it will be defined to have one of a list of specific supported datatypes. Current planned supported datatypes are Long, String, date, Money and Boolean. It is envisioned that multiple properties will be assigned to a property group and the property group will be associated to specific records in a key set of tables. In any given table, different rows can utilize different property groups.

One example of where this will be used is in the Parts table. One part code may want to capture (Color, Edge Style, Length and Width)
And a differnt Part may want to capture (Length, Width and Height)

During data entry, the user can select a paticular property group and then assign valuse for each of the properties tied to that group.

Ideally, the data capture for these property values would occurr in a datasheet list, similar to the proerties window used when designing a form. The issue im having is trying to figure out how to create this interface when each row would represent a different property and have a different set of valid values to either present the user with selectable options and/or validation of the data entered against the properties definition of valid values.

Has anyone attempted a similar interface and or have suggestions regarding approach.
Im very familiar with working with datasheets but have not really played around with continuous forms.

Thanks in advance
Fran

 

[MajP]

This is referred to as an entity attribute value model. I was just discussing this yesterday

Solved - Need help regarding my databse. Seeking experts advice.

The junction table is a one time fill (until you get more components and configuration). Populating that is really an admin function. If you need help in making a form to assist in that, I can help. Once that is correctly populated anyone can add new units.

www.access-programmers.co.uk

 

[Fran Lombard]

This is referred to as an entity attribute value model. I was just discussing this yesterday

https://www.access-programmers.co.uk/forums/posts/1688691/

Having trouble with the link
Doesnt seem to be working

 

[MajP]

Try now

 

[Fran Lombard]

Thanks @MajP

 

[MajP]

Usually you have an attribute table with all the attributes. Along with a description you need to store the data type, because all values are stored as strings. If you have specific potential choices they are stored in another table. This may sound great, but to make this work well requires a lot of code.

 

[Fran Lombard]

Attached is the Data Structure I am starting with.
Property Group: - Table to Store Definition of a Property Group
PropertyGroupProperty - Many-to-Many Junction between PropertyGroup and PropertyDefinition
Property Definition: Define a Unique Property (Contains DataType identifier, ValidationType and valid values if ValidationType = Range
Property: Store Value of a Particular Property - RefDocType: Pointer to a Table the Property was used in, RefDocID Pointer to the Row in the table
This is the table to be populate by the user when defining an Entity that needs additional Information.
A record will be needed for each Property associated with the Property Group assigned to the Entity.

These table used to Define valid values for a Property that is set to ValidationType: List
PropertyValueListCode: Unique Code of a Set of Valid Values
PropertyValueListCodeValues: List of Valid Values

 

[MajP]

EAV models are pretty tailor-able depending on the level of detail and functionality desired. But looking at that, I would think you have a working model. So do you have specific questions?

Here is another thread with lots of EAV discussion and using crosstabs to display data

Extracting data to new tables

Hi folks it's been a while since I last visited but I've not been doing much DB development in the past few years. I've recently helped a PhD student by redesigning an access database setup for recording different types of artefacts. The database consisted of a central table holding artefact...

www.access-programmers.co.uk

 

[Fran Lombard]

So do you have specific questions?

Well yes- Going back to my initial post, my question was about a UI for the final Data Entry of the Property Value. It easy enough to create the set of rows for each property within a property group, but then im stuck on the one field for the property value. The property value for each property in this set, while saved as a string, should be enteted through a control best suited for the data type and validation requirements.

These data types could be as listed: String, a "number" type - currency, integer or percent, a date type, or a string from a list of valid values. As such, as the user moves to the next property the control for data entry should change from a textbox, or a date picker, or a formated for currency text box or a dropdown combo with a list of the valid values.

I've not had to solve this challenge before and was looking for suggestions as to the approch for building the UI to accomodate the above listed requirements.

I have not had a chance to look at the sample db you posted, so I don't know if its applicable.

Given all that, I guess my specific questions are:

Does your sample demonstrate a solution applicable to the aforementioned requirements, and if not, Do you have any suggestions?

Fran

 

[MajP]

See this demo. I only coded the "Edit" portion but the "Add" would be similar except you would shows the Attribute name combobox. One forms that controls the user input, with combo boxes, text boxes, date pickers, etc. This is the general idea, as long as you have metadata for the attribute you can code in all kinds of things:
default values
data validation
control presentation

 

[MajP]

Here is with ADD and Edit.

 

[Fran Lombard]

Hey @MajP
Just got back to this and checked out the demo. Thank you for your efforts.
I was trying to accomplish this with out having to open up an Add/Edit form so it operated like the properties window when designing a form, but I think this can not be accomplished

I was able to get the multiple attributes to display in a continuous SubForm - very similar to yours and was able able to load the validations into the combo, only problem is that process changes the row source for each row since. I'm gonna look at your demo 2 and change my implementation to have a popup screen

 

[MajP]

only problem is that process changes the row source for each row since.

If you are talking comboboxes on a continuous form this can be done with a few tricks. You overlay a textbox on top of the combobox.
If you are only using comboboxes you do not need to open a pop up. But I was using different types of controls. You still could probably do it. If the field is boolean simply have a yes no combobox. I just added the checkbox as a demo, not really needed. If it is a date it would be a little weird, but you can type into a combo with no rowsource. You can add a double click on the date fields to bring up the date picker.

 

[Fran Lombard]

If you are talking comboboxes on a continuous form this can be done with a few tricks. You overlay a textbox on top of the combobox.value

I was doing exactly as you say. The continuous form has three fiields
PropertyName
txtPropertyValue and cboPropertyValue

The combo and textbox are on top of each other andI change the visible property on theese controls based on the definition of the property on that row by responding to the Form_Current event.
If im displaying the combobox i set the rowsource to display the list of valid values. The problem is that the other rows that just happen to also be combos also have their rowsources updated and the the data displayed in thoose other rows no longer reflect the values they should be displaying.

What it seems i need/want to do is set the rowsource for a single combo box in a single row, moe like a "cell" in a spreadsheet as opposed to the whole column.

 

[MajP]

You have to be a little trickier. The textbox always sits on top of the combobox. Look at the demo I showed. In your case if the control type is a combobox the key down expands the combobox, but the value is really being displayed by the textbox. That is how I am doing a cascading combobox in a continuous form. I will see if I can demo on mine.

 

[Fran Lombard]

@MajP
I just reread your post and i think missed what you were saying the first time.

Are you suggesting that i add a Text box to display the value of the combobox. So if i leave a row, the text box comes to the front and doesnt change what it is displaying. I think this could work nicely.

The value i would want to display currently resides in cboPropertyValue.Column(1). Is this the same as as cboPropertValue.Value. i thought the .value was always the bound column or .column(0) in my case.

I gotta go play with this. I do have class objects defined that mirrow each of the tables in the above datastructure - i guess i can always refer to the objects to source the data to display in the overlayed textbox. Gonna go try.

 

[Fran Lombard]

@Maji must say you do have some tricky approaches, love it.

 

[MajP]

See frmUsers2

 

[MajP]

You may want to look at this example by @isladogs

Accurately locate/move forms & controls

This example application was developed in response to various forum questions including: http://www.accessforums.net/showthread.php?t=75572 https://www.access-programmers.co.uk/forums/showthread.php?t=302641 Moving (and resizing) objects to precise locations on the screen is very easy using...

www.access-programmers.co.uk

This would allow you to accurately place a pop up form on a continuous form. You could then have a form that is nothing but a combobox or a button or checkbox, etc. Then on the current record you could make it appear as if these controls are on the form. I kind of do a version of this to fake a Multi Value Field combobox. That is a form that appears below a regular combobox appearing to be a MVF combo

 

[Fran Lombard]

@MajP Thanks again for locating thie example from @isladogs . I will check it out. I did get to implement your textbox overlay example and it works real nice if I only needed combos, but with the additional requirement to have other rows on the same grouping have formatted textboxes and/or true/false options and maybe even a checkbox it seems like the approach you presented may fall short. Having said that, those textbox-overlay a combo will definitely find a home in my bag of tricks.