• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2990
  • Last Modified:

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

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
filtrationproducts
Asked:
filtrationproducts
1 Solution
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Ess KayEntrapenuerCommented:
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
Independent Software Vendors: 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!

 
filtrationproductsAuthor Commented:
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
 
mbizupCommented:
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
 
filtrationproductsAuthor Commented:
I did a msgbox on lntret and it is returning "True"
0
 
mbizupCommented:
I think we cross-posted.  Did you try my last suggestion?
0
 
filtrationproductsAuthor Commented:
The top code worked perfectly! Thank you!

Dan
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now