Solved

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

Posted on 2013-11-01
8
2,862 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

785 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