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

value in combo box will not change in version 2007 but is OK in 2003

I designed my application in access 2003 and when running 2007 the combo box value will not change when I click on a different value in the combo value.

How do I correct this?
0
Don-White
Asked:
Don-White
  • 20
  • 15
  • 5
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Often these sorts of issues can be resolved by simply building a new database in 2007 (you can use the older .mdb format to maintain compatibility with others) and moving all your objects to that database. To do that:

1) Make a backup
2) Compact: Click the Office Button - Manange - Compact
3) From the VBA EDitor, click Debug - Compile. Fix any errors. Compile again. Continue doing so until the menuitem is disabled (which means it's compiled).
4) Compact again
5) Build a new, blank database in 2007, using the older .mdb format if desired. Import everything into it. Make sure to transfer over any references, if needed.
6) Debug - Compile again
7) Compact again
0
 
Don-WhiteAuthor Commented:
I get an error when I compile stating variable not defined.  I got this code for my menu on internet.  I do not know how to correct this.  Can you help.  The code is below (part of the switchboard code).  the varible is with exitlabel. I defined it as a string,but was not correct.  My menu would not work then.

Private Sub cmdExit_GotFocus()
    Dim intOption As Integer
   
    'If the Exit Button has received the focus, turn off the focus on all the menu options
    For intOption = 1 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
    Next intOption

    exitlabel.FontUnderline = True
End Sub

Private Sub cmdExit_LostFocus()
    exitlabel.FontUnderline = False
End Sub


Private Sub cmdExit_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    exitlabel.FontWeight = conFontWeightBold
End Sub
0
 
Don-WhiteAuthor Commented:
I define the variable as a label and that worked. nd now I do not get any compile errors.

Now I will following your instructions, however, I have to go to work and will not be able to work on this until later this evening.
Thanks, and I will see if your instructions work.

0
Industry Leaders: 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!

 
puppydogbuddyCommented:
Your problem sounds like the following bug in ms access 2007 that has been dpcumented by Allen Browne at this link:    http://allenbrowne.com/Access2007.html

Combo becomes transparent A combo may display as transparent when focus leaves it. Hotfix (post SP2.)  Workaround: set AlternateBackColor property of (Detail?) Section to something other than "No Color", e.g. #80000027.

Microsoft has issued the following post sp2 hotfix to correct the problem:
            http://support.microsoft.com/kb/974995
0
 
Don-WhiteAuthor Commented:
I installed the hot fix and it did not fix my problem.
0
 
puppydogbuddyCommented:
Have you installed Office Suite Suite Service Pack 2 (SP2)?  It contains numerous bug fixes to Access 2007.
       http://www.microsoft.com/downloads/details.aspx?FamilyID=b444bf18-79ea-46c6-8a81-9db49b4ab6e5&displaylang=en
0
 
Don-WhiteAuthor Commented:
I nstalled the SP2  and it did not correct my problem.
0
 
Don-WhiteAuthor Commented:
I have also compiled and transfered all information to a blank 2007 Access and the application runs OK, except the combo box will not allow me to change to a different value in the combo box.

What do I do now?
0
 
puppydogbuddyCommented:
Don-White,
Try making the changes shown below. If these changes do not fix the problem, please provide more info on what you mean by "the varible is with exitlabel. I defined it as a string,but was not correct."
 and " I defined the variable as a label and that worked. And now I do not get any compile errors."

Change this:
           For intOption = 1 To conNumButtons
                    Me("Option" & intOption).Visible = False
                    Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
            Next intOption

To this:
           For intOption = 1 To conNumButtons
                    Me("Option" & Str(intOption)).Visible = False
                    Me("OptionLabel" & Str(intOption)).FontWeight = conFontWeightNormal
            Next intOption
0
 
puppydogbuddyCommented:
oops, typo:
Str  s/b  CStr in the expressions I posted previously.
0
 
Don-WhiteAuthor Commented:
I corrected this by making the variable defined as a label.

My original problem is with the combo box.  In 2003 access the cobo box will allow me to use the mouse to click which field to select from the combo box, but when I use the program under 2007 access it will not work with the mouse, I have to key in the value.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<under 2007 access it will not work with the mouse>

This sounds more like OS or Access/Office installation issues, or issues with your mouse. I've never seen a situation where a healthy PC, with a healthy mouse, failed to work correctly in terms of that mouse.
0
 
Don-WhiteAuthor Commented:
No it is not the mouse.  It has to do with the combo box only.  It works OK with 2003 and not with 2007 access.  I deleted the combo in 2007 and reentered and still have the same problem of not being able to click on the value in the combo box.  When I build a new test DB it works OK.  It is the DB I have built, but unable to correct the problem in the 2007 version.  I made a new blank Db and then imported all information and compacted and the problem is still there.  I have now loaded SP3 and still does not correct the problem.

Do you any other suggestions to try?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So if you build a new db in 2007, and then add a combo to a form in that new db, then you do NOT have the issue?

If so, then I'd suggest you have a corrupt form. In the problem db, can you add a new form, add a combo and see if the issue recurs on that new form?
0
 
puppydogbuddyCommented:
If you have any YourCombo_BeforeUpdate() code or any code for your combo that says "Cancel = True', please post it.

Also, invoke the property sheet for your combo, and verify that all the events that should be activated when your code runs, show the words "Event Procedure"......otherwise the event proc won't fire.
0
 
Don-WhiteAuthor Commented:
I do not have any events for the combo that does not work.  As stated before the cobo works in 2003..
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried my suggestion?
0
 
Don-WhiteAuthor Commented:
Yes, I put another combo box and that one did not work also.  I cannot click to select value, but can key in the value.  However, it works OK with 2003 access.
0
 
puppydogbuddyCommented:
                http://office.microsoft.com/en-us/access/HA102389881033.aspx#4
According to this authoritative source for Access 2007, the order of mouse events is as follows
Mouse events
When you click and release a mouse button while the mouse pointer is on a control on a form, the following sequence of events occurs for the control:
                 MouseDown MouseUp Click


On a hunch, try changing this:
Private Sub cmdExit_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    exitlabel.FontWeight = conFontWeightBold
End Sub

To This:
Private Sub cmdExit_Click()
    exitlabel.FontWeight = conFontWeightBold
End Sub
0
 
puppydogbuddyCommented:
This could be the answer.    http://msdn.microsoft.com/en-us/library/bb238446.aspx
If I understand correctly, it appears that Access 2007 uses a MouseDown event for the combobox selection vs a Click event like I am used to.

 expression.MouseDown(Button, Shift, X, Y) >>>>where expression is a variable that represents a ComboBox object.

This (the MouseDown) event does not apply to a label attached to another control, such as the label for a text box. It applies only to "freestanding" labels. Pressing and releasing a mouse button in an attached label has the same effect as pressing and releasing the button in the associated control. The normal events for the control occur; no separate events occur for the attached label.
0
 
puppydogbuddyCommented:
Could your problem be related to this hotfix issue?  This hotfix fixes the following issue that was not previously documented in a Microsoft Knowledge Base article:
                              http://support.microsoft.com/kb/939185
You close or compact and then close an Access 2007 database. Then, you reopen the database. After you do this, you cannot use some menus or some menu items.
0
 
Don-WhiteAuthor Commented:
I am sorry but I donot understand what I need to try.  I tried the mouse down but not sure I did it correctly

I delected below:
Private Sub cmdExit_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    exitlabel.FontWeight = conFontWeightBold
End Sub

and replaced with this below:
Private Sub cmdExit_Click()
    exitlabel.FontWeight = conFontWeightBold
End Sub

Did I do this correctly?

0
 
puppydogbuddyCommented:

Yes, as a test .....what happened?

sorry to confuse you......it is hard to visualize your setup when I don't have it in front of me......so some of what I posted was like me thinking out loud...thoughts were jumbled and in the wrong order.

One question...... I am not sure whether cmdExit is a button on the form or whether it is the combobox.  Until I hear otherwise I am going to assume that cmdExit is a button.

Basically, comboboxes that I am used to working with use either a Click event or an afterUpdate event to update the results of the selection made. Then, I found the Microsoft Reference Source that made it appear that Access 2007 uses a MouseDown event for the combobox selection.  So, if the CmdExit_Click event did not work, try the combo's MouseDown event as shown below, replacing the illustrative name "YourCombobox" with the actual name of your combobox: Please provide feedback as to what happened after you made the changes.

Private Sub YourCombobox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    exitlabel.FontWeight = conFontWeightBold
End Sub
           
0
 
Don-WhiteAuthor Commented:
As you have the code I get an error run error 424, object required.

This is the code I put for the mouse down:

Private Sub YourCombobox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    exitlabel.FontWeight = conFontWeightBold
End Sub
0
 
puppydogbuddyCommented:
You need to replace "YourCombobox" with the actual name of your combobox.
0
 
Don-WhiteAuthor Commented:
I do have my combo box with the actual name, I only showed the code from your code and did not change it in the example back.  Below is the actual code and still get the eror 424.

Early in the person who wanted me to compile the code, I got an error with code where a variable was not defined.  It was exitlabel.  I defined as a label and that corrected the undefine variable.  Does that have anything to do with this error?

Here is my actual code:

Private Sub Combo48_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
exitlabel.FontWeight = conFontWeightBold

End Sub
0
 
Don-WhiteAuthor Commented:
Attached is the error alone with the code
ScreenPrint.zip
0
 
Don-WhiteAuthor Commented:
Well, I decided to send you the application.  The AFUMC_tabless.mdb must be in the c root drive.  That is my linked tables.  The Church.mdb can be anywhere.
Open Church.mdb
click on find members
Status and Service combo boxes are the one that will not allow me to change the value with the click on the value.  I have to key in the value.  In 2003 either key or click will work.

This should help you to see my problem

Thanks.
DB-Programs.zip
0
 
puppydogbuddyCommented:
Don-White,
Unfortunately, I don't have Access 2007.  Could you send Access 2003 using Access 2000 database format?  If not, could you post/attach a snapshot of your Access 2007 combobox in design view and in form view?  Thanks.
0
 
Don-WhiteAuthor Commented:
Attched is the Db in 2003 with 2000 format.  This DB workd OK with the combo boxes.

The table DB must be ibn the c drive root.
Church.zip
0
 
puppydogbuddyCommented:
OK, I looked at your find member combobox (combo59), and you do have a click event procedure and an afterUpdate event procedure for Combo59.  To see for yourself, highlight Combo59 in design view and go to the combo59's property sheet and click the Event tab.  There, you will see thewords [EventProcedure] to the right of the Click and AfterUpdate events.  If you highlight [EventProcedure], you will see a button with 3 dots (the code builder button).  If you click the code builder button, it will take you to the actual code for that event procedure.

Compare this to what you see in the 2007 version, and let me know if there is anything different.
0
 
Don-WhiteAuthor Commented:
I know it is the same.  I just copied the 2003 DB to the PC with the 2007 and ran it.  I have both the 2003 and 2007, one on desktop and other on my notebook.

The code is exactly the same.
0
 
puppydogbuddyCommented:
The code may be the same, but did you see the words [EventProcedure] showing in the event tab?  If you do, click the code builder button to the right of the AfterUpdate [EventProcedure] and change the following:
             Me.RecordSource = "SELECT * FROM Members WHERE ID = " & Me!Combo59 & ";"

to this:  Combo59.RowSource = "SELECT * FROM Members WHERE ID = " & Me!Combo59 & ";"
0
 
Don-WhiteAuthor Commented:
When I change the after update code as you indicate the cobo box59 will not work correctly.

It is the status and service combo that does not work.  The combo obx 59 works OK.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<The table DB must be ibn the c drive root.>

This is a poor design choice, and will affect you as your users move to new Windows platforms. Vista and Win7 are very restrictive in that area, and you'll run into issues with this.

Have you added all the various locations as Trusted Locations in Access 2007?
0
 
Don-WhiteAuthor Commented:
I have several different people entering the data from there homes.  My tables are linked so I can make changes to the application and not effect the data tables.  Where would you suggest as to where the table DB reside?  I thought C drive would be safe and my VB code would be the same for everyone.
0
 
puppydogbuddyCommented:
I am on the run this moning, but I managed to take a quick look at combo48 and combo50 and noticed the rowSources for both were 2 column value lists with the bound column not hidden, and the default value set to spaces.  I have two quick suggestions.
(1) Hide the bound column (set column width = 0)
(2) Remove your current setting (= " ") for default value.  If you don't want a default value, leave it empty.
 
See if the above fixes your mouse click problem.  If that doesn't help. let me know and I will take a deeper look this afternoon when things are not so hectic.
0
 
Don-WhiteAuthor Commented:
I did what you suggested and it still does not work.  I do want the default to be M for status combo box and blank for service combo box.

However, set bound to 0 and taking out the default does not correct the problem.

I hope things get better for you later today.  I am not in any kind of hurry.  I can key in the value under 2007.  I would like to know why it works OK under 2003 and not under 2007.  It has to be a bug in 2007.

Have a great day.
0
 
puppydogbuddyCommented:
Don,
The only thing that I have found that looks incorrect is that the field definitions for Status and Service allow zero legth fields (spaces) which is not compatible with using these fields as the bound columns in a combobox that uses a value list as a row source.  It is incorrect whether or not it caused the problem ou experienced in Access 2007.  Likewise, setting the field's default value = " " in the combobox instead of leaving it blank doesn't make sense either. As a general rule the setting for Allow Zero Length should be no.
see what Allen Browne has to say about Allowing Zero Length     http://allenbrowne.com/bug-09.html 
also see this microfoft free analyzer for determining potential problems converting 2003 to 2007.

Microsoft 2003 Analysis Tool to Determine Readiness for Conversion to 2007
http://www.microsoft.com/downloads/details.aspx?FamilyId=2E861E76-5D89-450A-B977-980A9841111E&displaylang=en
0
 
Don-WhiteAuthor Commented:
Thanks for all your time.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 20
  • 15
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now