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

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 (AT) Browniew (DOT) com> wrote

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;"Da te";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 (AT) Browniew (DOT) com> wrote

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 (AT) invalid (DOT) org> schreef in bericht
news:nqydnX_ba79F8Z3RnZ2dnUVZ_j6dnZ2d (AT) earthlink (DOT) com...

"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote in
news:htpit3$2rhe$1 (AT) textnews (DOT) wanadoo.nl:

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.)

On Fri, 28 May 2010 21:24:42 -0400, "Arvin Meyer" <arvinm (AT) invalid (DOT) org>
wrote:

Hi Arvin,
What is your definition of Alpha?
From your function it seems to be about the first character in the
string to be [a-zA-Z]. There are other definitions out there.

-Tom.
Microsoft Access MVP

Thanks Bob,
I was a bit too hasty when typing the fieldnames. Indeed that was the idea.

The problem is that I want to present the preference possibilities to the
user as in other windows programs (Extra-Options etc.). So every preference
must be visible as a field, also if a user hasn't set a value for it. I
can't think of a query that gives the right result and remains editable as
well.

Other thing is that I want to use checkboxes for some preferences and I see
no way of doing that when I store all values in a text field.

Lars


"Bob Quintal" <rquintal (AT) sPAmpatico (DOT) ca> schreef in bericht
news:Xns9D875A45C2268BQuintal (AT) 69 (DOT) 16.185.250...

"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote in
news:htrese$ha1$1 (AT) textnews (DOT) wanadoo.nl:

You have the TblPreferences to start with. A form could show the
PreferenceName Field in a listbox, allowing the user to click on one
and poping up a form that shows the name, mayh some help text and has
a textbox/checkbox/calendar/whatever to receive user input, the type
being tied to the PreferenceType field. A save button then runs an
appen or update query to add to TblUserPreferences, then closes the
popup, and if necessary, requeries the listbox...

number, "T" of "F" as text, "Y" or "N" as text. what you need is a
small function to translete "T" to -1 and "N" to 0.

It is similar to the function required to translate "-7654321" to
-7654321

Thanks for the idea. I was actully heading for a more Office like GUI, not
with a lisbox.

I know how to do that. The reason I mentioned it was because my intention
was to let the user enter the value in the field itself on the basis of a
query. Since in a continuous form you can't have the same field be a textbox
in one record and a checkbox in another record. I'll do some more fiddling
to see which option to choose.

Lars


"Bob Quintal" <rquintal (AT) sPAmpatico (DOT) ca> schreef in bericht
news:Xns9D87A8D089DC9BQuintal (AT) 69 (DOT) 16.185.250...

In the past, I've used a table in the front-end, a table in the back-end,
and an .ini file for this purpose. The first worked well for individual
preferences if the user only ever used the app from their own computer. The
second worked well when users might use the app from different computers,
and just as well if they only used one.

It's been so long since I used the .INI that I really don't remember what I
thought about using it -- back in Access 2.0 days. There were (are? don't
know if they were dropped in Vista and Win 7) APIs for accessing personal
and database-level INIs.

You can use the <the very thought of it makes me queasy> Registry, or define
Properties for the database (front-end?) to store preferences -- but I
haven't used the last two.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote