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