Save/Retrieve User Settings

[Scientific]

Hello all,

I have created different backgrounds that I use in a form and want to have
users choose which background to use when they use the database. I have
sample code below which assigns colors to different controls depending on
which background is in use. For example, if a user chooses the 49ers
background I would need to have the 49ers background and it's color settings
load into the form. Right now I'm doing things manually, but I figure there
has to be a way to automate this task.

Can something like what I described above be done using VB? Thanks in
advance.

-S

Select Case CurrentUser()
Case "Barney Rubble", "Fred Flinstone"
Me.Picture = "C:\DB\Bedrock.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "0"
Me.lblEmployee.ForeColor = "0"
Me.lblJob_Title.ForeColor = "0"
Me.lblNotes.ForeColor = "0"
' ComboBox Back Color
Me.cboDept.BackColor = "16777215"
Me.cboEmp_Name.BackColor = "16777215"
Me.cboJob_Title.BackColor = "16777215"
Me.cboNotes.BackColor = "16777215"

Case "Joe Montana"
Me.Picture = "C:\DB\49ers.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "16777215"
Me.lblEmployee.ForeColor = "16777215"
Me.lblJob_Title.ForeColor = "16777215"
Me.lblNotes.ForeColor = "16777215"
' ComboBox Back Color
Me.cboDept.BackColor = "0"
Me.cboEmp_Name.BackColor = "0"
Me.cboJob_Title.BackColor = "0"
Me.cboNotes.BackColor = "0"

 

[OssieMac]

You can set up a table where the properties are saved on the form close event
and then retrieved on the form open event.

The system that I did this on was split into front end and back end and
therefore I set the table up in the fornt end rather than a link to the back
end and that way each user had their own table and it only required one
record with a field for each control. (I didn't do it to save control
properties but rather the last filters used but the proncipal is the same).
However you could set up a table with the usernames and relationship set to
another table with the list of controls and fields for each of the properties.

 

[Scientific]

OssieMac,

Your concepts look workable for what I want to do. I'm not to good with the
coding part though but I will give it a try. I can visualize how it can
work, but I think it will take me a long time to get the coding part right,
if at all. Anyway, thanks for pointing me in the right direction. I'll post
back and let you know how things turn out

-S

 

[OssieMac]

Hi again,

Someone else asked a question this morning on how to code saving and
retrieving control values. Very similar for properties. I'll re-post the code
and see what you make of it. There are other ways of achieving this also.

It is untested so feel free to get back to me if you have a problem.

When in the VBA editor, (if not already done), you will need to select
Tools -> References and then scroll down to Microsoft DAO 3.6 Object Library.
Check the box (Ensure you check the box; don't just select the line) and
click OK.

'Code to Save ComboBox value to table
'The table has fields to save the various values.

Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

'The * select all fields from the recordset.
Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then
.Edit 'Places record in edit mode

.Fields("Setting") = Me.cboSection.value

'More fields can be placed here as per previous line if required

.Update 'Forces record to update (Otherwise it does not update)

End If

End With

rsCurrent.Close
Set rsCurrent = Nothing



'*************************************************


'Code to Copy values from table to ComboBox
Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then 'If EOF then not found
Me.cboSection = .Fields("Setting")

'Can retrieve more fields here as per previous line
End If

End With

rsCurrent.Close
Set rsCurrent = Nothing

End Sub

 

[Scientific]

OssieMac,

Thank you a million times over. By looking at the code you submitted, there
is no way I could have figured how to do this myself. Man, I really need to
buy some books and learn how to use VB. At any rate, I'll try what you have
provided and see how it goes. Regardless of what the outcome is, I really do
appreciate your trying to help me with this. You are a god send OssieMac

-S

Hi again,

Someone else asked a question this morning on how to code saving and
retrieving control values. Very similar for properties. I'll re-post the code
and see what you make of it. There are other ways of achieving this also.

It is untested so feel free to get back to me if you have a problem.

When in the VBA editor, (if not already done), you will need to select
Tools -> References and then scroll down to Microsoft DAO 3.6 Object Library.
Check the box (Ensure you check the box; don't just select the line) and
click OK.

'Code to Save ComboBox value to table
'The table has fields to save the various values.

Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

'The * select all fields from the recordset.
Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then
.Edit 'Places record in edit mode

.Fields("Setting") = Me.cboSection.value

'More fields can be placed here as per previous line if required

.Update 'Forces record to update (Otherwise it does not update)

End If

End With

rsCurrent.Close
Set rsCurrent = Nothing



'*************************************************


'Code to Copy values from table to ComboBox
Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then 'If EOF then not found
Me.cboSection = .Fields("Setting")

'Can retrieve more fields here as per previous line
End If

End With

rsCurrent.Close
Set rsCurrent = Nothing

End Sub

--
Regards,

OssieMac

OssieMac,

Your concepts look workable for what I want to do. I'm not to good with the
coding part though but I will give it a try. I can visualize how it can
work, but I think it will take me a long time to get the coding part right,
if at all. Anyway, thanks for pointing me in the right direction. I'll post
back and let you know how things turn out

-S

 

[OssieMac]

Hi again,

Feel free to get back and I'll help if I can. Time and patience are required
to learn and it doesn't matter how much you learn, there is always a little
more to learn.

Also there is an error in the code in the following line.

..FindFirst "ControlName = " & "'" & cboSection & "'"

cboSection should be the variable strCtrlName (both subs)
--
Regards,

OssieMac

OssieMac,

Thank you a million times over. By looking at the code you submitted, there
is no way I could have figured how to do this myself. Man, I really need to
buy some books and learn how to use VB. At any rate, I'll try what you have
provided and see how it goes. Regardless of what the outcome is, I really do
appreciate your trying to help me with this. You are a god send OssieMac

-S

Hi again,

Someone else asked a question this morning on how to code saving and
retrieving control values. Very similar for properties. I'll re-post the code
and see what you make of it. There are other ways of achieving this also.

It is untested so feel free to get back to me if you have a problem.

When in the VBA editor, (if not already done), you will need to select
Tools -> References and then scroll down to Microsoft DAO 3.6 Object Library.
Check the box (Ensure you check the box; don't just select the line) and
click OK.

'Code to Save ComboBox value to table
'The table has fields to save the various values.

Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

'The * select all fields from the recordset.
Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then
.Edit 'Places record in edit mode

.Fields("Setting") = Me.cboSection.value

'More fields can be placed here as per previous line if required

.Update 'Forces record to update (Otherwise it does not update)

End If

End With

rsCurrent.Close
Set rsCurrent = Nothing



'*************************************************


'Code to Copy values from table to ComboBox
Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then 'If EOF then not found
Me.cboSection = .Fields("Setting")

'Can retrieve more fields here as per previous line
End If

End With

rsCurrent.Close
Set rsCurrent = Nothing

End Sub

--
Regards,

OssieMac

OssieMac,

Your concepts look workable for what I want to do. I'm not to good with the
coding part though but I will give it a try. I can visualize how it can
work, but I think it will take me a long time to get the coding part right,
if at all. Anyway, thanks for pointing me in the right direction. I'll post
back and let you know how things turn out

-S

:

You can set up a table where the properties are saved on the form close event
and then retrieved on the form open event.

The system that I did this on was split into front end and back end and
therefore I set the table up in the fornt end rather than a link to the back
end and that way each user had their own table and it only required one
record with a field for each control. (I didn't do it to save control
properties but rather the last filters used but the proncipal is the same).
However you could set up a table with the usernames and relationship set to
another table with the list of controls and fields for each of the properties.

--
Regards,

OssieMac


:

Hello all,

I have created different backgrounds that I use in a form and want to have
users choose which background to use when they use the database. I have
sample code below which assigns colors to different controls depending on
which background is in use. For example, if a user chooses the 49ers
background I would need to have the 49ers background and it's color settings
load into the form. Right now I'm doing things manually, but I figure there
has to be a way to automate this task.

Can something like what I described above be done using VB? Thanks in
advance.

-S

Select Case CurrentUser()
Case "Barney Rubble", "Fred Flinstone"
Me.Picture = "C:\DB\Bedrock.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "0"
Me.lblEmployee.ForeColor = "0"
Me.lblJob_Title.ForeColor = "0"
Me.lblNotes.ForeColor = "0"
' ComboBox Back Color
Me.cboDept.BackColor = "16777215"
Me.cboEmp_Name.BackColor = "16777215"
Me.cboJob_Title.BackColor = "16777215"
Me.cboNotes.BackColor = "16777215"

Case "Joe Montana"
Me.Picture = "C:\DB\49ers.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "16777215"
Me.lblEmployee.ForeColor = "16777215"
Me.lblJob_Title.ForeColor = "16777215"
Me.lblNotes.ForeColor = "16777215"
' ComboBox Back Color
Me.cboDept.BackColor = "0"
Me.cboEmp_Name.BackColor = "0"
Me.cboJob_Title.BackColor = "0"
Me.cboNotes.BackColor = "0"

 

[Scientific]

OssieMac,

Yea, I was looking at that line "strCtrlName" and noticed that although it
was defined it wasn't used anywhere else. I intended to ask you about it but
I have been really busy and haven't been on the computer lately. Anyway,
thanks for noticing and for the correction. I plan to spend the weekend
seeing if I can implement your code into what I am trying to do with current
DB I have.

Again, thank you very much for all your posts and continued support OssieMac.

-S

Hi again,

Feel free to get back and I'll help if I can. Time and patience are required
to learn and it doesn't matter how much you learn, there is always a little
more to learn.

Also there is an error in the code in the following line.

.FindFirst "ControlName = " & "'" & cboSection & "'"

cboSection should be the variable strCtrlName (both subs)
--
Regards,

OssieMac

OssieMac,

Thank you a million times over. By looking at the code you submitted, there
is no way I could have figured how to do this myself. Man, I really need to
buy some books and learn how to use VB. At any rate, I'll try what you have
provided and see how it goes. Regardless of what the outcome is, I really do
appreciate your trying to help me with this. You are a god send OssieMac

-S

Hi again,

Someone else asked a question this morning on how to code saving and
retrieving control values. Very similar for properties. I'll re-post the code
and see what you make of it. There are other ways of achieving this also.

It is untested so feel free to get back to me if you have a problem.

When in the VBA editor, (if not already done), you will need to select
Tools -> References and then scroll down to Microsoft DAO 3.6 Object Library.
Check the box (Ensure you check the box; don't just select the line) and
click OK.

'Code to Save ComboBox value to table
'The table has fields to save the various values.

Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

'The * select all fields from the recordset.
Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then
.Edit 'Places record in edit mode

.Fields("Setting") = Me.cboSection.value

'More fields can be placed here as per previous line if required

.Update 'Forces record to update (Otherwise it does not update)

End If

End With

rsCurrent.Close
Set rsCurrent = Nothing



'*************************************************


'Code to Copy values from table to ComboBox
Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then 'If EOF then not found
Me.cboSection = .Fields("Setting")

'Can retrieve more fields here as per previous line
End If

End With

rsCurrent.Close
Set rsCurrent = Nothing

End Sub

--
Regards,

OssieMac


:

OssieMac,

Your concepts look workable for what I want to do. I'm not to good with the
coding part though but I will give it a try. I can visualize how it can
work, but I think it will take me a long time to get the coding part right,
if at all. Anyway, thanks for pointing me in the right direction. I'll post
back and let you know how things turn out

-S

:

You can set up a table where the properties are saved on the form close event
and then retrieved on the form open event.

The system that I did this on was split into front end and back end and
therefore I set the table up in the fornt end rather than a link to the back
end and that way each user had their own table and it only required one
record with a field for each control. (I didn't do it to save control
properties but rather the last filters used but the proncipal is the same).
However you could set up a table with the usernames and relationship set to
another table with the list of controls and fields for each of the properties.

--
Regards,

OssieMac


:

Hello all,

I have created different backgrounds that I use in a form and want to have
users choose which background to use when they use the database. I have
sample code below which assigns colors to different controls depending on
which background is in use. For example, if a user chooses the 49ers
background I would need to have the 49ers background and it's color settings
load into the form. Right now I'm doing things manually, but I figure there
has to be a way to automate this task.

Can something like what I described above be done using VB? Thanks in
advance.

-S

Select Case CurrentUser()
Case "Barney Rubble", "Fred Flinstone"
Me.Picture = "C:\DB\Bedrock.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "0"
Me.lblEmployee.ForeColor = "0"
Me.lblJob_Title.ForeColor = "0"
Me.lblNotes.ForeColor = "0"
' ComboBox Back Color
Me.cboDept.BackColor = "16777215"
Me.cboEmp_Name.BackColor = "16777215"
Me.cboJob_Title.BackColor = "16777215"
Me.cboNotes.BackColor = "16777215"

Case "Joe Montana"
Me.Picture = "C:\DB\49ers.jpg"
' ComboBox Label Colors
Me.lblDepartment.ForeColor = "16777215"
Me.lblEmployee.ForeColor = "16777215"
Me.lblJob_Title.ForeColor = "16777215"
Me.lblNotes.ForeColor = "16777215"
' ComboBox Back Color
Me.cboDept.BackColor = "0"
Me.cboEmp_Name.BackColor = "0"
Me.cboJob_Title.BackColor = "0"
Me.cboNotes.BackColor = "0"