?
Solved

Using VBA to change background color but setting not saving when form closed

Posted on 2013-11-01
8
Medium Priority
?
2,924 Views
Last Modified: 2013-11-04
I have a module that I call that changes the background color using the color picker. But the problem is when the form closes and reopens the color setting is not saving. How would I go about forcing these settings to save when either the color is changed or the form is closed.

Attached is the code I use on the button to change the background color.

Thanks!
Dan

Dim lngRet As Boolean

    lngRet = aDialogColor(Me.Form.Detail.Properties("BackColor"))

Open in new window

0
Comment
Question by:filtrationproducts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39616976
Create a table called tblLastColorSetting, with a field bgColorSetting, and store the setting:


Dim lngRet As Boolean
Dim strSQL as string 

    lngRet = aDialogColor(Me.Form.Detail.Properties("BackColor"))
    strSQL = "UPDATE tblLastColorSetting SET bgColorSetting = " & lngRet
    currentdb.Execute strSQL, dbfailOnError

Open in new window



Then in your form's open event:

Me.Detail.BackColor = NZ(DLookup("bgColorSetting", "tblLastColorSetting"), Me.Detail.BackColor )

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39616977
there are properties that you can only change and save when the form is in design mode.


so, you have to open the form in design mode to change the value of the property.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39616982
You need to save the settings, otherwise the get lost.


Either you save them to a database, or to a file on the harddrive

For a database you will need to create a table with the settings, and link them to the user

If you choose to create a file, you dont need to save the user.

when the form starts up, you will have a function On_Load to read all the setting, and set the forms accordingly

good luck
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:filtrationproducts
ID: 39621947
mbizip,

I added the table and code to my database. But when I change the color the only value it stores in that table is -1. No matter which color I choose.

Dan
0
 
LVL 61

Accepted Solution

by:
mbizup earned 800 total points
ID: 39621971
Sorry - revise the code like this:

Dim lngRet As Boolean
Dim strSQL as string 

    lngRet = aDialogColor(Me.Form.Detail.Properties("BackColor"))
    If lngRet = true then
        strSQL = "UPDATE tblLastColorSetting SET bgColorSetting = " & Me.Form.Detail.Properties("BackColor")
        currentdb.Execute strSQL, dbfailOnError
   end if

Open in new window


If that does not work, try this:

Dim lngRet As Boolean
Dim strSQL as string 

    lngRet = aDialogColor(Me.Form.Detail.Properties("BackColor"))
    If lngRet = true then
        strSQL = "UPDATE tblLastColorSetting SET bgColorSetting = '" & Me.Form.Detail.Properties("BackColor") & "'"
        currentdb.Execute strSQL, dbfailOnError
   end if

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 39621972
I did a msgbox on lntret and it is returning "True"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39621982
I think we cross-posted.  Did you try my last suggestion?
0
 
LVL 1

Author Closing Comment

by:filtrationproducts
ID: 39622153
The top code worked perfectly! Thank you!

Dan
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses
Course of the Month12 days, 3 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question