Storing user preferences
in [Help]
|
From: Lars Brownies on 28 May 2010 19:16 I want users to be able to store personal preferences for paths, layout, report dates, etc. I'm thinking to store the info in the following tables tblUser UserID* UserLastnam etc. tblUserPreference UserID* PreferenceID* tblPreference PreferenceID* PreferenceName PreferenceGroup PreferenceValue Problem is that I need to store values of different data types in the field PreferenceValue. My questions are: - How do I deal with the diffences in data type? Should I add a field for every data type? - If I enter a new user should I automatically add all Preferences to him or should I only add a preference if he wants to differ from the default value? - Or shouldn't I bother with normalisation in this case? Thanks, Lars
From: Arvin Meyer on 28 May 2010 21:24 I would use text, then use IsNumeric() to determine if it's a number. I have also written a custom function to determine alpha (it's in my book <g>) Public Function fIsAlpha(varIn As Variant) As Boolean On Error Resume Next fIsAlpha = Not Asc(LCase(Left(varIn, 1))) = Asc(UCase(Left(varIn, 1))) Exit_Here: Exit Function End Function -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "Lars Brownies" <Lars(a)Browniew.com> wrote in message news:htpit3$2rhe$1(a)textnews.wanadoo.nl... >I want users to be able to store personal preferences for paths, layout, >report dates, etc. I'm thinking to store the info in the following tables > > tblUser > UserID* > UserLastnam > etc. > > tblUserPreference > UserID* > PreferenceID* > > tblPreference > PreferenceID* > PreferenceName > PreferenceGroup > PreferenceValue > > Problem is that I need to store values of different data types in the > field PreferenceValue. > > My questions are: > - How do I deal with the diffences in data type? Should I add a field for > every data type? > - If I enter a new user should I automatically add all Preferences to him > or should I only add a preference if he wants to differ from the default > value? > - Or shouldn't I bother with normalisation in this case? > > Thanks, > > Lars > > > > >
From: Allen Browne on 28 May 2010 21:34 Use a Text field, since you can store any data type there. Add another field to indicate what data type it should be. In Form_BeforeUpdate, test if the value matches the data type before you write it to the table. For this field, I use the vbVarType values. For a combo with a Value List, the RowSource is: 2;"Integer";3;"Long";5;"Double";6;"Currency";7;"Date";8;"String";11;"Boolean";17;"Byte" You can then match the values to the members of vbVarType. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lars Brownies" <Lars(a)Browniew.com> wrote in message news:htpit3$2rhe$1(a)textnews.wanadoo.nl... > I want users to be able to store personal preferences for paths, layout, > report dates, etc. I'm thinking to store the info in the following tables > > tblUser > UserID* > UserLastnam > etc. > > tblUserPreference > UserID* > PreferenceID* > > tblPreference > PreferenceID* > PreferenceName > PreferenceGroup > PreferenceValue > > Problem is that I need to store values of different data types in the > field PreferenceValue. > > My questions are: > - How do I deal with the diffences in data type? Should I add a field for > every data type? > - If I enter a new user should I automatically add all Preferences to him > or should I only add a preference if he wants to differ from the default > value? > - Or shouldn't I bother with normalisation in this case? > > Thanks, > > Lars > > > > >
From: Lars Brownies on 29 May 2010 03:44 Thanks Arvin, Allen, I've been giving it some further thought. Since it is very unlikey that the number of preferences will exceed 15, it seems more practical to store these values in a personal .ini file and let the user fill the respective keys by unbound form controls. That way: - I can easily use unbound checkboxes in my preferences form - I can easily show all preferences for the user to edit, also when a user hasn't set a value for it - I only have to store values that the user actually sets. The key in the ..ini file is created when a user hasn't set a value for it yet. - Opposed to having a separate table field for every preference (denormalized), with an .ini file a backend design change is not needed. - The design will be simpler Any thoughts? Lars "Arvin Meyer" <arvinm(a)invalid.org> schreef in bericht news:nqydnX_ba79F8Z3RnZ2dnUVZ_j6dnZ2d(a)earthlink.com... > I would use text, then use IsNumeric() to determine if it's a number. I > have also written a custom function to determine alpha (it's in my book > <g>) > > Public Function fIsAlpha(varIn As Variant) As Boolean > On Error Resume Next > > fIsAlpha = Not Asc(LCase(Left(varIn, 1))) = Asc(UCase(Left(varIn, 1))) > > Exit_Here: > Exit Function > > End Function > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.accessmvp.com > http://www.mvps.org/access > Co-author: "Access 2010 Solutions", published by Wiley > > > "Lars Brownies" <Lars(a)Browniew.com> wrote in message > news:htpit3$2rhe$1(a)textnews.wanadoo.nl... >>I want users to be able to store personal preferences for paths, layout, >>report dates, etc. I'm thinking to store the info in the following tables >> >> tblUser >> UserID* >> UserLastnam >> etc. >> >> tblUserPreference >> UserID* >> PreferenceID* >> >> tblPreference >> PreferenceID* >> PreferenceName >> PreferenceGroup >> PreferenceValue >> >> Problem is that I need to store values of different data types in the >> field PreferenceValue. >> >> My questions are: >> - How do I deal with the diffences in data type? Should I add a field for >> every data type? >> - If I enter a new user should I automatically add all Preferences to him >> or should I only add a preference if he wants to differ from the default >> value? >> - Or shouldn't I bother with normalisation in this case? >> >> Thanks, >> >> Lars >> >> >> >> >> > >
From: Bob Quintal on 29 May 2010 08:52
"Lars Brownies" <Lars(a)Browniew.com> wrote in news:htpit3$2rhe$1(a)textnews.wanadoo.nl: > I want users to be able to store personal preferences for paths, > layout, report dates, etc. I'm thinking to store the info in the > following tables > > tblUser > UserID* > UserLastnam > etc. > > tblUserPreference > UserID* > PreferenceID* > > tblPreference > PreferenceID* > PreferenceName > PreferenceGroup > PreferenceValue > > Problem is that I need to store values of different data types in > the field PreferenceValue. > > My questions are: > - How do I deal with the diffences in data type? Should I add a > field for every data type? > - If I enter a new user should I automatically add all Preferences > to him or should I only add a preference if he wants to differ > from the default value? - Or shouldn't I bother with normalisation > in this case? > > Thanks, > > Lars > you would be better with this structure tblUserPreference UserID* PreferenceID* PreferenceValue <- this is text, store numbers as text tblPreference PreferenceID* PreferenceName PreferenceType <-use this to store the value's type (number or text) PreferenceGroup PreferenceDefault <- see PreferenceValue Only store non-default preferences in TblUserPreferences. dLookup the user's preference value for a specified PreferenceId, if null use the default from tblPreferences. Use the PreferenceType to control which conversion function to apply to the value (cInt, cBool, cDouble, etc.) |