Create user-defined fields and dynamic forms from them 1

[scriggs]

I have a database and I want the user to be able to define fields (with different field types) and then open a form which reads the fields and allows data to be added.

This is for a sort of contact manager application which I have predefined with normal tables/field, etc. But the client(s) will want to add lots of unique info for each contact. These may be numbers, strings, drop-down lists, etc.

I don't really know where to start and hoped someone might have done it already. I have been working on something like below which sort of works. My main problem with this is displaying the data in a form...

tblFields
---------
intFieldID strFieldType strFieldName
-------------------------------------------------------
1 string Favourite Football Team
2 integer Send Christmas Card
3 combo Contact Rating


tblCombos
---------
intComboID intFieldID strCombo
1 3 Gold
2 3 Silver
3 3 Bronze


tblData
-------
intDataID intFieldID intContactID strDetails
------------------------------------------------------
1 1 1 Manchester United
2 1 2 Chelsea
3 2 1 -1
4 2 2 0
5 3 1 Gold
6 3 2 Silver

 

[dhookom]

I agree with your table structure. I would expect to see a subform based on tblData. You could add some code that would append records to tblData based on tblFields and intContactID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scriggs]

Thanks for the reply. I was hoping someone would have a better idea, but indeed my structure seems to work OK.

The bigger problem I have is creating a dynamic form of the fields. How can I make the form check what fields have been created and add them to the form for display. Especially for a combo box.

I was thinking of setting up a single view form and then thinking of getting VB code to read through the fields and add them one under each other on a blank form. But I don't know if that would work, and certainly haven't been able to get it to work at all.

 

[BobRodes]

I'm not sure I do agree with your table structure, it would depend on your business rules. It seems to me that you have it set up that a given client can define a given attribute for their own clients, and then define also a series of lookups for them. The fact that "Favourite Football Team" happens to be the same for two different clients is coincidence. Suppose, for example, one client wanted to allow a user to select from a list of Premier league only, and another from a broader list? Where would you be then?

You're also going to run into real problems setting up the strFieldType in any meaningful way in the tblCombos table. If you're looking to make a new table for each client, well, what if they want different field types for each of their custom fields? Looks like a scaling nightmare. So, I'd dump that field entirely, and do it a different way.

I believe what you are looking for is a structure something like this:

tblCustomFields

intFieldID intClientId strFieldName
-------------------------------------------------------
1 1 Favourite Football Team
2 2 Send Christmas Card
3 2 Contact Rating

tblClientList

intClientID strClientName (Address, etc.)
-------------------------------------------------------
1 Sir Paul McCartney
2 HRH Queen Elizabeth II

tblCustomFieldValues

intCFVID intFieldID strCFVValue
----------------------------------------------------------
1 1 Chelsea
2 1 Manchester United
3 1 Wigan
4 2 Yes
5 2 No
6 3 Gold
7 3 Silver
8 3 Bronze


tblCustomerList

intCustID strCustName
----------------------------
1 Sir Mick Jagger
2 George W. Bush
3 David Beckham

tblCustClient

intCCID intCustID intClientID
1 1 1
2 1 2
3 2 2
4 3 1



tblCustomerFieldData

intFDID intCCID intFieldID
----------------------------------
1 1 3
2 2 2
3 2 6
4 3 5
5 3 8
6 3 1

That's a decent first pass. It could be tightened up some. (For example, the tblCustClient field might not be necessary.) But Mick Jagger is on the Queen's christmas list and his favorite team is Wigan, Our Man George, a bronze guest, is not on the list, and David Beckham has a secret love of Chelsea, which is the real reason he left Manchester.

HTH

Bob

 

[scriggs]

Bob

Thanks for the reply. An interesting structure, but I think it fulfils a different need.

My aim is for the end user to be able to setup custom fields to record against a client record. If I was doing this in the initial setup I would just add the fields to the tblCustomerList to record the data.

Once the user-defined field is added, this field should be available for all items in tblCustomerList. Hence I think my concept of tblFields and tblData should work OK.

The reason I added the strFieldType is so that I can determine the field type, I was planning to use this in generating the form.

The tblCombo was added so that if the strFieldType was combo then the user can add predefined choices and setup their own combo.

If you think your structure is better then please let me know why, because I really want this to be setup and work the best. That's why I'm trying to put in the time up front before I write it....

The other issue I'm struggling with is how to present this in a form... is there an tek-tips restriction on me reposting this question in the forms forum for help on that?

 

[dhookom]

scriggs,
To see a form that uses the same kind of lookup based on intFieldID, check the data entry form for At Your Survey at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The question is similar to your intFieldID and the possible responses are like your tblCombos.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scriggs]

Duane

Thanks for the helpful link, the setup is very similar and I like how that operates with defaults, lists, etc. I see the approach on the form is to set everything as a combo which works and an approach I may take. I would prefer to generate a chkbox, combo or string depending on data type but have a fall back plan.

I am looking at VBA coding to either generate the form, or else activate a batch of visible=false string, combo, etc. and position them. Depends how it goes!