Solved

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

Posted on 2013-11-01
8
2,866 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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 200 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

808 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