locked
Other (Please Specify) RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I have a combobox, and one of the values in the list is "Other (Please Specify)"

    I was thinking that if the user selected this value, I'd cancel it in the BeforeUpdate event, hide the combobox, show a textbox, remove the ControlSource from the combobox and add it to the textbox. Then the user can add "Other" stuff in the textbox to the field. The combobox RowSource is a separate table, and I don't want to add the "other" stuff to this table

    This, however doesn't work. I get sooo many errors trying to move the focus around etc. I must be barking up the wrong tree. IS there a better way to do this? Errors like I need to save the record first before I do a setfocus method... etc.

    Thanks,
    Brad

    Saturday, October 31, 2015 5:02 AM

Answers

  • As you have found, you cannot set focus to another control in the Before Update event of a control, it leads to a conflict. You must use the After Update or On Exit event for that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 31, 2015 6:46 PM
  • Thanks to both of you for your replies.

    I thought I'd provide a bit more info just in case...

    I deleted my code and started over. Here is what I have and the error I get:

    I am simply trying to move the focus to a button. I thought this was ok?

    Here is the form in FormView (just the field in question):

    The List button is where I'm trying to set the focus in the BeforeUpdate event.

    This is what the form looks like in DesignView: I have the TextBox over to the side for now just so I can see it when I make it visible. If I can get this to work, I'd put it under the combobox so it looks like they're in the same spot to the user.

    It would be really good if I don't have to add another field as this will cause extra work when running queries and reports.

    The List button and the ProdType text box are after the ProdType combobox in the tab order. Also, the text box name is ProdTypeAlt, combobox is just ProdType.

    Brad

    .....OK, I gave up on the BeforeUpdate, and used the AfterUpdate. I decided just to remove the value that is set "Other (Please Specify)" and then move the focus around and hide and show controls

    Private Sub ProdType_AfterUpdate()
        If Me.ProdType = "Other (Please Specify)" Then
            Me.ProdType = ""
            Me.cmdSeeProdTypeList.Visible = True
            Me.ProdTypeAlt.Visible = True
            Me.ProdTypeAlt.SetFocus
        End If
    End Sub


    Private Sub ProdTypeAlt_GotFocus()
        Me.ProdType.Visible = False
    End Sub

    This is a solution that works well.

    Thanks again for your replies.

    Brad




    What I don't understand about your setup is that you have a list button displaying if the user doesn't want what's already in the list given by the combo box.  Why???

    As they say, you can only lead a horse to water, but you can't make them drink.  With the post I provided to you earlier, you're standing right in front of the trough.

    Your product type should be a required value and likely drawn from a reference table of product types.  It should also be a numeric foreign key value, not a text string.  If the user selects "Other," your additional field will be enabled or displayed or however you want to manage that, and the user will enter their own input.  You will not want to delete the value of ProductTypeID because that tells you that the user has entered something other than what is available in the list.  So that input will be in addition to the field value of ProductType equaling an id for "Other."  All that said, you should have two fields in your table and form: ProductTypeID and ProductTypeOther, similar to what I've already illustrated for you above.

    It would also be a good practice if you named your form controls as to the type of control you're working with.  See the following link for more information on naming conventions.

    http://access.mvps.org/access/general/gen0012.htm




    Saturday, October 31, 2015 8:09 PM

All replies

  • I would make sure that the text box follows the combo box in the tab order of the form.

    Set the Enabled property of the text box to No.

    Create a conditional formatting rule for the text box.

    Select 'Expression Is' from the first dropdown, and enter

    [ComboBoxName]="Other (Please Specify)"

    in the box next to it, substituting the name of the combo box of course.

    Make sure that the text box will be enabled:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 31, 2015 9:16 AM
  • Hello Brad,

    First, you need to design your table with an additional field: SubjectOther, where "Subject" is whatever the name of your original field is that is supplied by the combo box on your form.  Then on your form you will have the SubjectOther control following just after the combo box.  The text box will be disabled initially.  When the user selects "Other," the text box will become enabled. 

    What's important to understand is that your table will always have a value for Subject regardless of whether it is "Other" or not.

    So for example your tables and form will look something like the following where we have ColorID and ColorOther:

    The code behind your form will look like the following:

    Option Compare Database
    Option Explicit
    
    Private Sub Cbx_ColorID_AfterUpdate()
        
        Txt_ColorOther_Enable
        
    End Sub
    
    Private Sub Form_Current()
        
        Txt_ColorOther_Enable
        
    End Sub
    
    Private Sub Txt_ColorOther_Enable()
        
        'Enable if ColorID = 7 (Other)
        Me.Txt_ColorOther.Enabled = Nz(Me.Cbx_ColorID, 0) = 7
        
    End Sub



    • Edited by RunningManHD Saturday, October 31, 2015 11:05 AM
    Saturday, October 31, 2015 10:54 AM
  • Thanks to both of you for your replies.

    I thought I'd provide a bit more info just in case...

    I deleted my code and started over. Here is what I have and the error I get:

    I am simply trying to move the focus to a button. I thought this was ok?

    Here is the form in FormView (just the field in question):

    The List button is where I'm trying to set the focus in the BeforeUpdate event.

    This is what the form looks like in DesignView: I have the TextBox over to the side for now just so I can see it when I make it visible. If I can get this to work, I'd put it under the combobox so it looks like they're in the same spot to the user.

    It would be really good if I don't have to add another field as this will cause extra work when running queries and reports.

    The List button and the ProdType text box are after the ProdType combobox in the tab order. Also, the text box name is ProdTypeAlt, combobox is just ProdType.

    Brad

    .....OK, I gave up on the BeforeUpdate, and used the AfterUpdate. I decided just to remove the value that is set "Other (Please Specify)" and then move the focus around and hide and show controls

    Private Sub ProdType_AfterUpdate()
        If Me.ProdType = "Other (Please Specify)" Then
            Me.ProdType = ""
            Me.cmdSeeProdTypeList.Visible = True
            Me.ProdTypeAlt.Visible = True
            Me.ProdTypeAlt.SetFocus
        End If
    End Sub


    Private Sub ProdTypeAlt_GotFocus()
        Me.ProdType.Visible = False
    End Sub

    This is a solution that works well.

    Thanks again for your replies.

    Brad




    • Edited by mbrad Saturday, October 31, 2015 6:26 PM
    Saturday, October 31, 2015 6:13 PM
  • As you have found, you cannot set focus to another control in the Before Update event of a control, it leads to a conflict. You must use the After Update or On Exit event for that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 31, 2015 6:46 PM
  • Thanks to both of you for your replies.

    I thought I'd provide a bit more info just in case...

    I deleted my code and started over. Here is what I have and the error I get:

    I am simply trying to move the focus to a button. I thought this was ok?

    Here is the form in FormView (just the field in question):

    The List button is where I'm trying to set the focus in the BeforeUpdate event.

    This is what the form looks like in DesignView: I have the TextBox over to the side for now just so I can see it when I make it visible. If I can get this to work, I'd put it under the combobox so it looks like they're in the same spot to the user.

    It would be really good if I don't have to add another field as this will cause extra work when running queries and reports.

    The List button and the ProdType text box are after the ProdType combobox in the tab order. Also, the text box name is ProdTypeAlt, combobox is just ProdType.

    Brad

    .....OK, I gave up on the BeforeUpdate, and used the AfterUpdate. I decided just to remove the value that is set "Other (Please Specify)" and then move the focus around and hide and show controls

    Private Sub ProdType_AfterUpdate()
        If Me.ProdType = "Other (Please Specify)" Then
            Me.ProdType = ""
            Me.cmdSeeProdTypeList.Visible = True
            Me.ProdTypeAlt.Visible = True
            Me.ProdTypeAlt.SetFocus
        End If
    End Sub


    Private Sub ProdTypeAlt_GotFocus()
        Me.ProdType.Visible = False
    End Sub

    This is a solution that works well.

    Thanks again for your replies.

    Brad




    What I don't understand about your setup is that you have a list button displaying if the user doesn't want what's already in the list given by the combo box.  Why???

    As they say, you can only lead a horse to water, but you can't make them drink.  With the post I provided to you earlier, you're standing right in front of the trough.

    Your product type should be a required value and likely drawn from a reference table of product types.  It should also be a numeric foreign key value, not a text string.  If the user selects "Other," your additional field will be enabled or displayed or however you want to manage that, and the user will enter their own input.  You will not want to delete the value of ProductTypeID because that tells you that the user has entered something other than what is available in the list.  So that input will be in addition to the field value of ProductType equaling an id for "Other."  All that said, you should have two fields in your table and form: ProductTypeID and ProductTypeOther, similar to what I've already illustrated for you above.

    It would also be a good practice if you named your form controls as to the type of control you're working with.  See the following link for more information on naming conventions.

    http://access.mvps.org/access/general/gen0012.htm




    Saturday, October 31, 2015 8:09 PM