Solved

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

Posted on 2013-11-01
8
2,832 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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 61

Accepted Solution

by:
mbizup earned 200 total points
Comment Utility
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
Comment Utility
I did a msgbox on lntret and it is returning "True"
0
 
LVL 61

Expert Comment

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

Author Closing Comment

by:filtrationproducts
Comment Utility
The top code worked perfectly! Thank you!

Dan
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now