Creating a table based on user defined fields?

[Keberhard]

I need to know if there is a way to let the user decide which fields they want to show up on a form. For example: When entering inventory each user probably has different information needs and may wish to specify which fields they need and also which order they should appear in. They may or may not need fields such as "item number", "description", "pattern", "color", "size".
I know this can be done, and I have some ideas but I'm not sure about syntax or whether or not this can be done by a series of queries or by creating a recordset in code. Any ideas?

 

[rewdee]

I don't have the code written out for you but I would think the method would be something like:

1)create a listbox and set its rowsource type property to field values and its rowsource to the table or query.

2)When the user selects and hits some cmdbutton, have a proceure that iterates through the items select adding to some string. Something like:

Dim i    As Integer
Dim sSQL As String
For i = 0 To lstbxSomething.ListCount - 1
    If lstbxSomething.Selected(i) Then
       sSQL = CStr(lstbxSomething.Column(0, i)) & ","
    End If
Next i

3) strip the last "," off otherwise your SQL statement won't work--Right$(sSQL,1)

4) Add the rest of the SQL statement -- sSQL=sSQL & " From tbl1"

5) You are left with the row source for a query, continuous form, Report, etc.

Hope this Helps,
Rewdee

 

[TrojanRabbit]

I'm just throwing an idea here. To avoid data conflicts, you may want to have a generic table made for each user, one with just a set of fields that can be used when needed. (i.e., Field1, Field2, etc.) Make a form based on the table with fields corresponding to the fields in the table. When users select their fields, change field types and captions as needed, but leave the field names so that the form works. Open the form and change captions as needed. Determine the .visible and .enabled properties for each field according to what the user is using. You can also set size, etc.