"On Current" Event based on subform data

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.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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.

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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.

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?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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.

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

"Douglas J Steele" wrote:

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?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Phil Davy" wrote in message
...
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.