Select Case Question & Combo Box Question

[hwong]

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. :banghead: 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.

Sub Form_Current()
Dim ctl As Control
    For Each ctl In Me.Controls
        Select Case ComboBoxA.Value[INDENT]Case Null
            If ctl.ControlType = acTextBox Then
            ctl.Visible = False
End If
[/INDENT][INDENT]Case "Cat"
            With Label1
            .Visible = True
            .Caption = "Cat Species"
            End With
            With Text1
            .Visible = True
            .ControlSource = "CatSpecies"
End With
[/INDENT][INDENT]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
[/INDENT]Next
End Sub

Thanks for answering!!!!

 

[hwong]

Would someone help? Thanks in advance!

 

[vbaInet]

1. Restrict the number of characters a user can enter to a particular number and change the width of your combo box to match that. If there's no max and a user enters thousands of characters, would you increase the textbox to fit that too?

2. Can you explain what you mean by "when a new record is loaded"