By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Store personal settings

P: n/a
Hi all,

A have several forms with unbound combo boxes, the combo boxes are used to
filter the data in the forms. What I would want is that my application
stores the most recent settings of these comboboxes. I would like to store
them in a table, so the next time the user starts the application, his/her
personal settings are retrieved and used. I tried a lot and also looked
into google, but no succes so far. I am a newbie and enjoying learning to
code ms access, I hope someone can help me with this.

Thnks!

Newbie
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Newbie" <no@spam.nl> wrote in message
news:Xn*********************@194.109.133.20...
Hi all,

A have several forms with unbound combo boxes, the combo boxes are used to
filter the data in the forms. What I would want is that my application
stores the most recent settings of these comboboxes. I would like to store
them in a table, so the next time the user starts the application, his/her
personal settings are retrieved and used. I tried a lot and also looked
into google, but no succes so far. I am a newbie and enjoying learning to
code ms access, I hope someone can help me with this.

Thnks!

Newbie


Storing these settings in a table would make sense, but if the database is
deployed in a multi-user environment, make sure you split the database front
and back end so that users open their own front ends. The table of settings
should be stored locally (not linked to the shared database) so that each
user has his own settings.

At its simplest the table would be:

tblSettings:
SetID = long integer, primary key
SetName = text, indexed, no duplicates
SetValue = text

So that all settings are simply stored as text. Then create a new module
and paste in the following functions:
Public Function GetMySetting(strSetting As String, _
strValue As String) As Boolean

On Error GoTo Err_Handler

Dim strWhere As String
Dim strReturn As Variant

strWhere = "[SetName]=""" & strSetting & """"

strReturn = Nz(DLookup("SetValue", "tblSettings", strWhere), "")

If Len(strReturn) > 0 Then
strValue = strReturn
GetMySetting = True
End If

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
Public Function SetMySetting(strSetting As String, _
strValue As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "UPDATE tblSettings SET tblSettings.SetValue="""
strSQL = strSQL & strValue & """ WHERE "
strSQL = strSQL & "tblSettings.SetName="""
strSQL = strSQL & strSetting & """"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffected = 1 Then
SetMySetting = True
End If

Exit_Handler:

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


To see this sort of thing in action, add a row to the table with values (1,
"FormColour", 255) and create a form with a button named cmdColour and paste
in the following simplified example:

Private Sub cmdColour_Click()

Dim strColour As String

strColour = InputBox("Enter Colour Number")

If SetMySetting("FormColour", strColour) Then
Me.Detail.BackColor = CLng(strColour)
End If

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim strColour As String

If GetMySetting("FormColour", strColour) Then
Me.Detail.BackColor = CLng(strColour)
End If

End Sub


Nov 12 '05 #2

P: n/a
Make a table with each field to store the combo box.

Then, make the form BOUND (instead of un-bound).

When you open the form, simply restrict the form to the ONE record based on
the users logon name. If you don't use logon name, then use the workstation
name.

To open the form to the one record, you would use:

docmd.OpenForm "comboForm",,,"userID = '" & currentUser & "'"

Thus, the combo boxes will now remember their setting, since you bound them
to your "usersDefaults" table that you create. That table simply will have a
columns for each combo box, or field you want to remember, and one
additional column called userID.

As mentioned, if you don't implement security, then currentUser will always
return the name of "admin". In that case, you would need to retrieve the
workstation logon name.

You can get the current network logon name with:
http://www.mvps.org/access/api/api0008.htm

And, the current computer name with:
http://www.mvps.org/access/api/api0009.htm

As mentoend, if using ms-access security, then currentuser() will return the
ms-access logon.

I often log all 3 of the above values in some applications.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

Nov 12 '05 #3

P: n/a
Newbie <no@spam.nl> wrote in message news:<Xn*********************@194.109.133.20>...
Hi all,

A have several forms with unbound combo boxes, the combo boxes are used to
filter the data in the forms. What I would want is that my application
stores the most recent settings of these comboboxes. I would like to store
them in a table, so the next time the user starts the application, his/her
personal settings are retrieved and used. I tried a lot and also looked
into google, but no succes so far. I am a newbie and enjoying learning to
code ms access, I hope someone can help me with this.

Thnks!

Newbie


Create a "Mainform" with your combos and a subform for your "filtered"
data. The main form can be bound to a newly created table that will
have a field per combo value. The combos will use the existing SQL as
their table/query data, but will be bound to the table you created.
Use the Child and Parent fields properties of the subform to link the
fields on the subform to the combo value.

I think that should work, but be prepared to spend a bit of time
perfecting this. I prefer to use an event which will (usually on a
button click but in your case on form load as well) take the values
from the combos and set the recordsource of the subform.

Have fun, and many sleepless nights.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.