Enable/Diable controls on a subform

G

Guest

I think am goffing somewhere but this is my need, I have 4 controls on my
subform and I would like to enable and disable them depending on how I select
information on one of the controls:
I have status combobox, pending combobox, cancellation combobox, and
Received date textbox
NB: My subform is a continous subform

So when i select a Status from the status combobox (which contains [Pending,
cancellation and complete] ) e.g., Pending I would like to enable the Pending
Reason, the rest should reimain disabled and cleared
If I select Cancel , the Cancellation combobox and the Received Date textbox
should be enabled, and the rest disabled and cleared.
If I select the Complete, the Received date should be enabled and the rest
disabled...

What am having a problem is that if I select a status, all the records in
that column change to true.

Attached is my code with where the beforeupdate event is in the Subform and
the afterupdate is in the afterupdate event of the status combobox

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Dim strControl As String
strControl = ""

If (Me!TaxDOcStatus.Column(0) = "Pending") And IsNull(Me.TxDocPendReason) Then
strControl = strControl & " Select a pending reason " & vbCrLf
Me.TxDocPendReason.SetFocus
cancel = True
End If
'**************************************************************************
If (Me!TaxDOcStatus.Column(0) = "Cancelled") And IsNull(Me.TxDocCxlReason)
Then
strControl = strControl & " Select a Cancellation reason " & vbCrLf
Me.TxDocCxlReason.SetFocus
cancel = True
End If

If (Me!TaxDOcStatus.Column(0) = "Cancell") And IsNull(Me.TxDocDateRcvd) Then
strControl = strControl & " Enter a date when this was Cancelled " &
vbCrLf
Me.TxDocDateRcvd.SetFocus
cancel = True
End If
'**************************************************************************

If (Me!TaxDOcStatus.Column(0) = "Complete") And IsNull(Me.TxDocDateRcvd) Then
strControl = strControl & " Need Date when this was completed " &
vbCrLf
Me.TxDocDateRcvd.SetFocus
cancel = True
End If

If strControl <> "" Then
MsgBox "The following information is required:" & vbCrLf & strControl,
vbInformation, "Incomplete Information"
'Cancel = True
End If
Exit_BeforeUpdate_err:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Description
Resume Exit_BeforeUpdate_err

End Sub

Private Sub TaxDOcStatus_AfterUpdate()
On Error GoTo TaxDocStatus_AfterUpdate_Err

Dim strControl As String
Dim cancel As Boolean
strControl = ""

If (Me!TaxDOcStatus.Column(0) = "Pending") Then
Me.TxDocPendReason.Enabled = True
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
Else
Me.TxDocPendReason.Enabled = False
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
End If
'**************************************************************************
If (Me!TaxDOcStatus.Column(0) = "Cancelled") Then
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = True
Me.TxDocDateRcvd.Enabled = True
Else
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
End If
'**************************************************************************

If (Me!TaxDOcStatus.Column(0) = "Complete") Then
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = True
End If

If strControl <> "" Then
MsgBox "The following information is required:" & vbCrLf & strControl,
vbInformation, "Incomplete Information"
'Cancel = True
End If
Exit_AfterUpdate_err:
Exit Sub

TaxDocStatus_AfterUpdate_Err:
MsgBox Err.Description
Resume Exit_AfterUpdate_err

End Sub
 
G

Guest

Hi JOM,

I don't know if this article will help you, but I always remember it when I
read combo boxes and continuous forms:

Tip #16: Data in a combo box control on a continuous form/datasheet disappears
http://www.fmsinc.com/free/tips.html#tip16foraccess


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I think am goffing somewhere but this is my need, I have 4 controls on my
subform and I would like to enable and disable them depending on how I select
information on one of the controls:
I have status combobox, pending combobox, cancellation combobox, and
Received date textbox
NB: My subform is a continous subform

So when i select a Status from the status combobox (which contains [Pending,
cancellation and complete] ) e.g., Pending I would like to enable the Pending
Reason, the rest should reimain disabled and cleared
If I select Cancel , the Cancellation combobox and the Received Date textbox
should be enabled, and the rest disabled and cleared.
If I select the Complete, the Received date should be enabled and the rest
disabled...

What am having a problem is that if I select a status, all the records in
that column change to true.

Attached is my code with where the beforeupdate event is in the Subform and
the afterupdate is in the afterupdate event of the status combobox

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Dim strControl As String
strControl = ""

If (Me!TaxDOcStatus.Column(0) = "Pending") And IsNull(Me.TxDocPendReason) Then
strControl = strControl & " Select a pending reason " & vbCrLf
Me.TxDocPendReason.SetFocus
cancel = True
End If
'**************************************************************************
If (Me!TaxDOcStatus.Column(0) = "Cancelled") And IsNull(Me.TxDocCxlReason)
Then
strControl = strControl & " Select a Cancellation reason " & vbCrLf
Me.TxDocCxlReason.SetFocus
cancel = True
End If

If (Me!TaxDOcStatus.Column(0) = "Cancell") And IsNull(Me.TxDocDateRcvd) Then
strControl = strControl & " Enter a date when this was Cancelled " &
vbCrLf
Me.TxDocDateRcvd.SetFocus
cancel = True
End If
'**************************************************************************

If (Me!TaxDOcStatus.Column(0) = "Complete") And IsNull(Me.TxDocDateRcvd) Then
strControl = strControl & " Need Date when this was completed " &
vbCrLf
Me.TxDocDateRcvd.SetFocus
cancel = True
End If

If strControl <> "" Then
MsgBox "The following information is required:" & vbCrLf & strControl,
vbInformation, "Incomplete Information"
'Cancel = True
End If
Exit_BeforeUpdate_err:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Description
Resume Exit_BeforeUpdate_err

End Sub

Private Sub TaxDOcStatus_AfterUpdate()
On Error GoTo TaxDocStatus_AfterUpdate_Err

Dim strControl As String
Dim cancel As Boolean
strControl = ""

If (Me!TaxDOcStatus.Column(0) = "Pending") Then
Me.TxDocPendReason.Enabled = True
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
Else
Me.TxDocPendReason.Enabled = False
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
End If
'**************************************************************************
If (Me!TaxDOcStatus.Column(0) = "Cancelled") Then
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = True
Me.TxDocDateRcvd.Enabled = True
Else
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = False
Me.TxDocDateRcvd = ""
End If
'**************************************************************************

If (Me!TaxDOcStatus.Column(0) = "Complete") Then
Me.TxDocPendReason.Enabled = False
Me.TxDocPendReason = ""
Me.TxDocCxlReason.Enabled = False
Me.TxDocCxlReason = ""
Me.TxDocDateRcvd.Enabled = True
End If

If strControl <> "" Then
MsgBox "The following information is required:" & vbCrLf & strControl,
vbInformation, "Incomplete Information"
'Cancel = True
End If
Exit_AfterUpdate_err:
Exit Sub

TaxDocStatus_AfterUpdate_Err:
MsgBox Err.Description
Resume Exit_AfterUpdate_err

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Before Update not checking combobox! 6
DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70 2
The domenuitem action was canceled 2
acSaveRecord 1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuV 0
Excel Is it possible to ask a question within a question? 0
complicated subform beforeupdate 1
applicaton.echo / docmd.echo screen still pulsing - Why? 1

Top