User defined possibility to see records

[dand_dd]

Hi,

In my database I have one huge table (almost 65 Column and 25000 records). When I start the application I must enter a password and access know which user came into the application. I have a form (frmMain) where is a list with all data from the table (all fields and records). I want to make an option and I have no idea how to make this.
So, now I will write about my problem.

I want to make a button that opens another form (frmUserDefined) where the user can choose what he wants to see in the previous form (frmMain). Explicitly, in the frmMain the list contains all the columns and all the records from database table. In the frmUserDefined the user have the possibility to choose what to see in frmMain and he?s options will be saved into another table and when he starts his applications always he see in the frmMain the list with his options.

For example:

The normal list looks like entire table.

A?B?C?D?E?F ?G?H?I?J?K?L?M?N-- ?
??..
???
Where A, B, C, ??are fields name from the table.

The user chooses in the form frmUserDefined his options and save them (for example he wants to see only columns A, C, F, J). Now when he opens the frmMain instead to see the whole list (A?B?C?D?E?F ?G?H?I?J?K?L?M?N-- ?) he sees only his list (A, C, F, J).

My database is a multi-user database into a LAN network, where the user can access simultaneously the database. So, each user has his option list, each user can see only the data list with his desired columns.

In moment is one form for each user; bur now the users number is growing and I cannot work with 20 ? 50 different mask.

With this option, user defined (custom) I can resolve this complicated situation.

In the next days I will attach a example database with what I have made with this concept.

Please help me with any idea or example or code.

Thanks

[JimmyTheHand]

Hi :hi:

I found this problem very interesting :) Let me sketch up my general idea about solving it.

First of all, create a query that contains all fields from all tables that you ever might want to display on this form. Let the query be called Query1 for the sake of this example.
Now, you should have a table (Table1) for user field selection. This table looks like this:

Design of Table1:

ID (counter)
UserID
QueryID (in case you want to use this technique on several forms)
FieldID (fields that the user wants to see) Now, when displaying that particular form, you query Table1, filtered by UserID and QueryID, and get something like this:



Results of Query2:

UserID - QueryID - FieldID
john - Query1 - LastName
john - Query1 - FirstName
john - Query1 - Age

Then you build an SQL query based on the above results. It would be something like:

Dim mySQL As String
mySQL = "SELECT [LastName], [FirstName], [Age] FROM [Query1]" These field names, of course, are coming from Query2, by looping through the retrieved rows. So, this SQL is characteristic of the user called John, because results of Query2 are based on his user ID.

Then you set the form's recordsource to this SQL query:
frmCustomerForm.RecordSource = mySQL

You might even try to store filtering conditions in Table1, so that the user can not only restrict fields, but recors as well. Building mySQl would be a little more complicated, but not impossible.

What do you think?

Jimmy

[OBP]

One Table & 65 fields sounds a bit Non-Normalized.
Jimmy, any idea how the Form will handle the "Design Layout" of the SQL changes between users?
Or were you thinking in terms of "Datasheet" View?

[JimmyTheHand]

I'm not sure what you mean, these terms are unfamiliar to me. :dunno
But I can a guess. And so my answer is that Query2 is, by nature of its role, a temporary query, needed only to build that SQL. So this Query2 would be creatd run-time, via VBA, just before showing the appropriate form.

Have I answered the question?

Jimmy

[OBP]

Jimmy, I just wondered what the Form would look like, would it only have the fields from the SQL on it, or all of the fields?
I am not sure what the "frmCustomerForm.RecordSource = mySQL" form looks like as I have never used it. :dunno

[JimmyTheHand]

Jimmy, I just wondered what the Form would look like, would it only have the fields from the SQL on it, or all of the fields?Oops :o:
Now I see what you mean. Haven't thought of it that way.
Well, there is the possibility to pre-design the form, with controls for all possible fields, then hide fields that are not needed. The resulting form, with all the "missing" fields, would bear a touch of incompleteness, I guess...

Also, it is not unheard of to generate a form run-time, i.e. add field controls via VBA. After all, each such control has a ControlSource property, so they can be bound to fields programmatically. But they must be positioned and dimensioned somehow on the form, and juggling around with 1 to 65 controls... :ack:
We'll se what the OP thinks about this, when they come back.


I am not sure what the "frmCustomerForm.RecordSource = mySQL" form looks like as I have never used it. :dunno It's nothing special. "frmCustomerForm" is just a name I gave to my imaginatory example form. And RecordSource is a property of the form object, that is used to bind the form to a table or query. Can be set in design mode or run-time.