"On Current" Event based on subform data

[Guest]

Hi all,

I'm trying to control a form's text boxes (whether they are enabled or not)
based on the value of a field in a subform.

I have it designed so you can have many records in the main form which
relate to one record in the subform.

I have tried using SQL in the main form (SELECT CEP FROM tb_Frame_Download)
to no avail - I keep getting '#NAME?'

I am absolutely tearing my hair out over this, could anyone please help?!!

Many Thanks,
Phil


PS -

Also as for redesigning the schema, I'm afraid there simply isn't a way
round it - I have to have this many to one relationship.

And it is essential I grey out these boxes or else it will comprise data
quality in that only certain combinations of values are allowed.

 

[Douglas J Steele]

Pretty hard to give advice without seeing the actual code that's failing.

 

[Guest]

I know this doesn't work, but this is what I am trying to achieve.

Private Sub Form_Current()

If SELECT CEP FROM tb_Frame_Download = "ALOP" Then 'this being the subform
ALOP.Enabled = True
Else
ALOP.Enabled = False
End If

End Sub

 

[Douglas J Steele]

You can't use SQL in VBA like that.

Does tb_Frame_Download only contain a single row? If so, try:

If Nz(DLookup("CEP", "tb_Frame_Download"), "") = "ALOP" Then
ALOP.Enabled = True
Else
ALOP.Enabled = False
End If

or

ALOP.Enabled = (Nz(DLookup("CEP", "tb_Frame_Download"), "") = "ALOP")

If tb_Frame_Download has more than one row, are you simply interested in
whether any row has ALOP in it, or do you need to look for the value of CEP
for a specific row?

 

[Guest]

Perfect!! To answer your question, Frame_Download will only every have one row.

Many thanks got your help Doug, much appreciated - the dlookup was the
breakthrough - and not at all obvious.

thanks
Phil