Hide and Unhide Column and Change textbox to combobox in datasheet view (1 Viewer)

sxschech

Registered User.
Local time
Today, 09:18
Joined
Mar 2, 2010
Messages
791
Apologies if this may have been explained in other threads.

Here is how to hide and unhide a column in datasheet view since Visible=False does not work on Datasheet view. Issue was that have a textbox used for comments, however, for one particular activity, need to pick a name from a combobox and have that value get stored in the comments field. What I did was add a combobox using wizard to the form and changed the tab order so that it will be after the comment textbox, since this is in datasheet view, we don't have to worry about how it looks or its position in design view. Then put some code in the Form Load, GotFocus, OnClick and LostFocus events so that when user tabs or clicks on the comments text box and certain conditions are met, the comments field is hidden and the combobox is unidden and after user has made the selection, it reverts back to show the comments and hides the combobox. Added a double click option in order to change the question writer name, if the existing name needs to be changed in the future. Here is the code:
Code:
Private Sub Form_Load()
'Display Comments TextBox and Hide the
'QuestionWriter Combobox until needed
'20180118
    Me.Comments.ColumnHidden = False
    Me.cboQuestionWriter.ColumnHidden = True
End Sub

Private Sub Comments_GotFocus()
'Display QuestionWriter Combo box if adding
'their name so that name is standardized
'20180118
    If Me.ActivityID = 1 And Nz(Me.Comments, "") = "" Then
        Me.Comments.ColumnHidden = True
        Me.cboQuestionWriter.ColumnHidden = False
        Me.cboQuestionWriter.SetFocus
    Else
        Me.Comments.ColumnHidden = False
        Me.cboQuestionWriter.ColumnHidden = True
    End If
End Sub

Private Sub cboQuestionWriter_LostFocus()
'Hide QuestionWriter Combo box after adding
'their name so that comments text box is
'visible
'20180118
    Me.Comments.ColumnHidden = False
    Me.cboQuestionWriter.ColumnHidden = True
End Sub

Private Sub Comments_Click()
'Display QuestionWriter Combo box if adding
'their name so that name is standardized
'20180118
    Call Comments_GotFocus
End Sub

Private Sub Comments_DblClick(Cancel As Integer)
'Display List of Names for Question Writers for that activity
'if Need to change the question writer's name
'20180112
    If Me.ActivityID = 1 And Nz(Me.Comments, "") <> "" Then
        Me.Comments.ColumnHidden = True
        Me.cboQuestionWriter.ColumnHidden = False
        Me.cboQuestionWriter.SetFocus
    Else
        Me.Comments.ColumnHidden = False
        Me.cboQuestionWriter.ColumnHidden = True
    End If
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom