Disabling x3 Fields Based on a Response in the Fourth?

[DocOyibo]

Can anyone help me out with this one?

I’m trying to get x3 fields in a form to DISABLE and go a FUNNY COLOUR when I tick a Check Box (called HazYN)
I’ve placed the code in the forms ‘CURRENT’ event?

I’ve tried to get the code below to do this for me, but all I get are error messages (e.g. ‘Expected End Sub’ etc.). Any chance of putting me straight before I loose my marbles/sanity etc??

Private Sub Form_Current()

Sub OpenADORecordset()
'Declare and instantiate the object variables
Dim cnHazSelection As ADODB.Connection
Dim rsHazards As ADODB.Recordset
On Error GoTo Error_Handler
'Set connection to current database
Set cnHazSelection = CurrentProject.Connection
'Instantiate a new recordset object
Set rsHazards = New ADODB.Recordset
'Open the Hazards table
rsHazards.Open "Hazards", cnHazSelection, adOpenDynamic, adLockOptimistic, adCmdTable

rsHazards.MoveFirst
If Me!HazYN = "0" Then
Me!Index.Enabled = True
Me!HazCat.Enabled = True
Me!HazSubCat.Enabled = True
Else
Me!Index.Enabled = False
Me!Index.BackColor = 13948116
Me!HazCat.Enabled = False
Me!HazCat.BackColor = 13948116
Me!HazSubCat.Enabled = False
Me!HazSubCat.BackColor = 13948116
End If
rsHazards.MoveNext

'Close the object variables, connections and clean up
rsHazards.Close
cnHazSelection.Close
Set rsHazards = Nothing
Set cnHazSelection = Nothing
Exit Sub

Error_Handler
MsgBox "An error occurred. The error number is " & Err.Number & _
" and the error description is " & Err.Description
Exit Sub

End Sub

Any pointers/tips gratefully received!

 

[mdav2]

You have two subs declared:

Private Sub Form_Current()
Sub OpenADORecordset()

but I can only see one endsub in your code. You should just need to add another end sub to fix the problem.


Mark Davies
Warwickshire County Council

 

[Remou]

You cannot embed a sub within a sub:

[tt]
Private Sub Form_Current()

Sub OpenADORecordset()[/tt]

You can create an OpenADORecordset sub and call it from the Form_Current sub.

 

[Remou]

Um, snap?

 

[DocOyibo]

MMMmmmmm! Added another End Sub but still getting the same message?? Just started messing with/manipulating recordsets, so I am still at the bottom of the learning curve here? Do I just nest the other End Sub on top of the other one? Is that appropriate?

Thanks.....

 

[randy700]

Do I just nest the other End Sub on top of the other one? Is that appropriate?

Absolutely not!

Read Remou's post and follow his advice.

Randy