Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Why does MS Access GUI ignore some combobox properties being set through VBA?

Posted on 2012-04-12
14
Medium Priority
?
353 Views
Last Modified: 2012-04-18
After some modifications mainly in VBA code combobox in one of the most important forms
lost some portion of its functionality. It displays it in the older version, but I cannot figure out why it does not in the newer one. Please help with at least where to look at advice.
The attached image illustrates how the same actions in Immediate Window result in different effects in both versions of MS Access database. How can I restore functionality wanted?
Thanks.
Disobedient-BorderColor.jpg
0
Comment
Question by:midfde
  • 7
  • 7
14 Comments
 
LVL 21
ID: 37840061
What version of Access is the newer version?

If you set the color manually in design mode on the form.  Open the form, What does is then show as the color code in the immediate window?
0
 
LVL 1

Author Comment

by:midfde
ID: 37840279
Both versions of MDB files run under the same Access 2003 and are located in the same folder with different file names.

?Syscmd(acSysCmdAccessVer)
11.0

Open in new window

See also the attached image.
Although I investigated properties in form's design view, I did whatever I could to prevent from any changes anywhere except for VBA code.
env.jpg
0
 
LVL 21
ID: 37840452
Ah ... older versions of the database not older version of Access.

I make sure there is not an issue with the VBA code not getting recomplied properly, I would recommend decompling the VBA code for the database and recompiling.

Is your database split? Do you you compile the database into an mde? Do you have the same issue with the mde?

.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:midfde
ID: 37840587
The database has 4 categories of (~250) tables:
1) ~20 local tables;
2) -3) 200+ linked to two SQL Server databases;
4) the rest are linked to an MDB file.

This application does not ever use MDE rendition to work with.
 This
c:\1>"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" /decompile "\\fdefs1...
did not help
0
 
LVL 21
ID: 37840700
I like to be sure the VBA has not got corrupted. If this was the issue,  it is very difficult to track it down. Now we know the VBA is OK.

Next we need to see if it may be a corrupted form.  I like to export the form to a text file. delete the form. compact. import the form back into the database.  See: Backing up and Restoring Objects

If that does not fix the issue the next thing I would do it create a blank new database and import in all the objects.

<<This application does not ever use MDE rendition to work with.>>
I deploy front ends as a MDE/ACCDE for many reason. One of the main reasons is that it insures the VBA code stays properly compiled and prevents VBA code corruption.
0
 
LVL 1

Author Comment

by:midfde
ID: 37840818
Thanks, TheHiTechCoach.
I need some time.

>>...and import all objects.
Sounds (fairly) good for the 6 kinds, but what about relationships, I/O specs, anything else that does not come to my mind right away and that is not queries, tables,.., macros.?

I'll conduct additional investigation of other similar forms / controls.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 800 total points
ID: 37840979
<,Sounds (fairly) good for the 6 kinds, but what about relationships, I/O specs, anything else that does not come to my mind right away and that is not queries, tables,.., macros.?>>

Relationship are actually defined  in the back end. Be sure to select the options>> button to get to the advanced options. You can select to import everything but the VBA references..

Import Options
0
 
LVL 1

Accepted Solution

by:
midfde earned 0 total points
ID: 37843021
The subject text of y question was “Why [in the world] does Access ignore...?”
The best answer I found was: It happens because sometimes settings for the BorderStyle, BorderColor, and BorderWidth properties are ignored by design.
0
 
LVL 1

Author Comment

by:midfde
ID: 37843091
The follwing code does work:
Private Sub setMdtBorderColor()
    Const SUNKEN = 2, FLAT = 0
    Const TRANSPARENT = 0, SOLID = 1
    With frm(SOURCE_DATA_TABLE_NAME)
        If TableExists(.value) Or Trim(Nz(.value)) = vbNullString Then
            .SpecialEffect = SUNKEN
            .BorderStyle = TRANSPARENT
            .BorderColor = vbBlack
            .ControlTipText = "Verified trend data table name"
            
        Else
            .SpecialEffect = FLAT
            .BorderStyle = SOLID
            .BorderColor = vbRed
            .ControlTipText = "Expert has not found this table in the database"
        End If
    End With
End Sub

Open in new window

0
 
LVL 21
ID: 37843099
<<It happens because sometimes settings for the BorderStyle, BorderColor, and BorderWidth properties are ignored by design.>>
That does not explain why it was once working but now it is not working.

You originally said:
<<... ,It displays it in the older version, but I cannot figure out why it does not in the newer one.  >>
I took that as meaning it once was working but has stopped.

In almost every data entry form I change the  BorderStyle, BorderColor, and BorderWidth properties for controls.    I have never had it ignored are runtime.  

In your case it may be corruption or other VBA code that is blocking it.
0
 
LVL 1

Author Comment

by:midfde
ID: 37843341
>>...It displays it in the older version, but I cannot figure out why...
Meticulous collation of properties was sufficient to find out why. It is verifiable, whilst this
"...corruption or other VBA code that is blocking it"
does not look like it is.
Thanks again.
0
 
LVL 21
ID: 37843904
<<The only my excuse is that I consider a MDB file as “on-demand” instance of the application that by itself resides in Visual Source Safe (VSS) database, whose.”Data and Misc Objects” thing has been doing its job very well for years.>>

I have used VSS for many applications. I still deploy the front end as an MDE without any issues.  It is not different that using VSS with other programming languages. The source code (with Acess it is an .mdb) is tracked with VSS. When ready to deploy you compile the application into an MDE with Access or a .exe with other languages.

Good luck with your project.
0
 
LVL 1

Author Closing Comment

by:midfde
ID: 37859693
Dear TheHiTechCoach,
I thank you for your interest and collateral information. I did not know how easy it was to handle relations and (!) toolbars with “Import Objects” dialog box. The only my excuse is that I consider a MDB file as “on-demand” instance of the application that by itself resides in Visual Source Safe (VSS) database, whose.”Data and Misc Objects” thing has been doing its job very well for years.  However this is definitely a (lyrical) digression.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

824 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