Results 1 to 5 of 5
  1. #1
    hwong is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2012
    Posts
    13

    Select Case Question & Combo Box Question

    Greeting All,

    Thank you to this forum and all the generous teachers here, I have learned much knowledge in Access and now become an Access VBA starter. Of course, a starter always encounter problems XD

    My 1st question is how I can use VBA to set a combo box's width auto-fit to the longest phrase of the list? Say
    1.Apple
    2.Orange
    3.Banana


    4.All of the above
    How can I set the combo box width to enable "All of the above" was shown in screen completely? I hope it can be automatically done so that I need not to amend the width again and again if I have to add a longer phrase later.

    My 2nd question: I have written the below codes and hoped that only ComboBox A shown when a new record was loaded. I found that the Price Label and Price Text Box were visible. I have changed to use "IF" and it functions well. However, I still want to know what's wrong with my codes, would anyone teach me? Thanks in advance.

    Code:
    Sub Form_Current()
    Dim ctl As Control
        For Each ctl In Me.Controls
            Select Case ComboBoxA.Value
    Case Null If ctl.ControlType = acTextBox Then ctl.Visible = False End If
    Case "Cat" With Label1 .Visible = True .Caption = "Cat Species" End With With Text1 .Visible = True .ControlSource = "CatSpecies"
    End With
    Case "Dog" If ctl.ControlType = acTextBox Then ctl.Visible = False End If Me.Price_Label.Visible = True Me.Price_Label.Top = 503 Me.Price_Label.Left = 60 Me.Price.Visible = True Me.Price.Top = 503 Me.Price.Left = 1923 End Select
    Next End Sub
    Thanks for answering!!!!

  2. #2
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    The width of a Combo Box is set in the properties Box. Set it to Auto provided the Box itself is wide enough. If not you will have to insert a width in cm. This could all be done in code if really required.

    Question 2

    Use the for each control to set all controls to not visible then follow that by setting the Combo's visibility to on.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    A gentleman who goes by the handle of ADezii wrote this hack a while ago. It shows how to accomplish your first goal:

    http://bytes.com/topic/access/insigh...own-list-width

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    hwong is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2012
    Posts
    13
    Thanks all above!!! I will try.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have to first determine what type of control it is in the FOR loop. Then you can check the value in the control.
    Try this:
    Code:
    Sub Form_Current()
       Dim ctl As Control
    
       For Each ctl In Me.Controls
          Select Case ctl.ControlType   '(get the control type)
                                                 '(Text box    = acTextBox    value 109)
                                                 '(Combo box = acComboBox value 111)
    
             Case acComboBox    '(type is combo box)
                If ctl.Name = "ComboBoxA" Then
                   Select Case ctl   '(default property is value - no need to type it)
                      Case "Cat"   '(combo box value is "Cat")
                         With Me.Label1
                            .Visible = True
                            .Caption = "Cat Species"
                         End With
                         With Me.Text1
                            .Visible = True
                            .ControlSource = "CatSpecies"
                         End With
                      Case "Dog"   '(combo box value is "Cat")
                         Me.Price_Label.Visible = True
                         Me.Price_Label.Top = 503
                         Me.Price_Label.Left = 60
                         Me.Price.Visible = True
                         Me.Price.Top = 503
                         Me.Price.Left = 1923
                   End Select
                End If
    
             Case acTextBox    '(type is text box)
                ctl.Visible = False
          End Select
       Next
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. combo box question
    By RedGoneWILD in forum Forms
    Replies: 2
    Last Post: 06-28-2012, 03:37 PM
  2. Simple Combo Box Multiple Select Question
    By ahamilton in forum Access
    Replies: 7
    Last Post: 03-17-2011, 01:38 PM
  3. Select query question
    By CoachBarker in forum Queries
    Replies: 2
    Last Post: 10-31-2010, 11:15 AM
  4. A combo box question
    By GeorgeD in forum Forms
    Replies: 3
    Last Post: 05-05-2008, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums