if "None" then no other records permitted

[Guest]

I want to code my sfrmProfilesAllergens so that if "None" is selected in
tblProfilesAllergens.ProfilesAllergens no other records will be permitted.
Also, if any other selection is made that "None" can NOT be selected.

Can anyone point me to an example currently on the forum (I can't seem to
find one) or tutor me through this?

Thanks!

 

[BruceM]

I do not understand what you are trying to do. I susupect I am not the only
one. I will guess that sfrmProfilesAllergens is a subform. What is
tblProfilesAllergens.ProfilesAllergens? I can assume ProfilesAllergens is a
field in the table tblProfilesAllergens, but the format seems to be
something taken from a SQL statement. How are you going about selecting it?
It would probably be best if you describe the real-world situation you are
trying to address. We can't see your database, and do not know how the
information is organized. If you can add something about the database
structure and relationships, so much the better.

 

[Guest]

Thanks, Bruce!

I'll try to outline with more detail. My challenge consists of two tables:
tblProfiles
tblProfilesAllergens

These are linked in a one-to-many relationship.

tblProfilesAllergens is the record source for sfrmProfilesAllergens.

sfrmProfilesAllergens.ProfilesAllergens is a combobox that queries:
SELECT tblAllergenTypes.AllergenAbb, tblAllergenTypes.txtAllergenName FROM
tblAllergenTypes ORDER BY tblAllergenTypes.AllergenAbb;

"None" is a value in tblAllergenTypes.txtAllergenName along with 8 other
values. When "None" is selected in [ProfilesAllergens] I want to make that no
other record can be created. Also, when something other than "None" is
selected I want to make it so that "None" can NOT be selected.

I hope that's a little more clear!

 

[Guest]

On the After Update even of the field ProfilesAllergens put the following:

If me.ProfilesAllergens = "None" then
[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = False
Else
[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = true
End If

(This is assuming you have the sfrmProfilesAllergens on a main form based
off a query or table which includes the table tblProfilesAllergens).

You will also need to make sure that the AllowAdditions gets turned back on
again as needed based on your data entry. For instance, when you switch to a
new record, etc you will need to include

[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = true

on that event.

Hope this helps.

Jackie

 

[Guest]

Thanks, Jackie!

Here's my code based off your suggestion:
Private Sub cbAllergen_AfterUpdate()
If Me.cbAllergen = "None" Then
[sfrmProfilesAllergens].Form.frmFormulas.AllowAdditions = False
Else
[sfrmProfilesAllergens].Form.frmFormulas.AllowAdditions = True
End If

End Sub

At first that seemed like a real possible solution however it returned the
dreaded:
Run-time error '2465'
Microsoft can't find the field '|' referred to in your expression.

The debugger points to the line:
[sfrmProfilesAllergens].Form.frmFormulas.AllowAdditions = False

I can't understand that error.

Also, I use the subform for several other main forms. How can I change the
code to accommodate that...?


--
www.Marzetti.com

On the After Update even of the field ProfilesAllergens put the following:

If me.ProfilesAllergens = "None" then
[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = False
Else
[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = true
End If

(This is assuming you have the sfrmProfilesAllergens on a main form based
off a query or table which includes the table tblProfilesAllergens).

You will also need to make sure that the AllowAdditions gets turned back on
again as needed based on your data entry. For instance, when you switch to a
new record, etc you will need to include

[sfrmProfilesAllergens].Form.MainFormName.AllowAdditions = true

on that event.

Hope this helps.

Jackie

I want to code my sfrmProfilesAllergens so that if "None" is selected in
tblProfilesAllergens.ProfilesAllergens no other records will be permitted.
Also, if any other selection is made that "None" can NOT be selected.

Can anyone point me to an example currently on the forum (I can't seem to
find one) or tutor me through this?

Thanks!

 

[John W. Vinson]

At first that seemed like a real possible solution however it returned the
dreaded:
Run-time error '2465'
Microsoft can't find the field '|' referred to in your expression.

The syntax is backwards there... try

Private Sub cbAllergen_AfterUpdate()
If Me.cbAllergen = "None" Then
Forms!frmFormulas![sfrmProfilesAllergens].Form.AllowAdditions = False
Else
Forms!frmFormulas![sfrmProfilesAllergens].Form.AllowAdditions = True
End If

End Sub

This assumes that the name of the Subform Control on the main form frmFormulas
is sfrmProfilesAllergens - the name of the form object within that subform
control is not used, and might be sfrmProfilesAllergens or anything else you
want.

John W. Vinson [MVP]

 

[Guest]

Thanks, John!

The syntax is backwards there... try

Private Sub cbAllergen_AfterUpdate()
If Me.cbAllergen = "None" Then
Forms!frmFormulas![sfrmProfilesAllergens].Form.AllowAdditions = False
Else
Forms!frmFormulas![sfrmProfilesAllergens].Form.AllowAdditions = True
End If

End Sub

This isn't working as expected. The syntax appears to be fine AND when I
create a record of "None" I'm unable to create any further records BUT
navigating frmFormula records AFTER creating a "None" record renders
sfrmProfilesAllergens "inactive" - in other words I can navigate to another
record but the controls in the subform disappear. I have to exit the main
form and reopen it in order to make the subform "active" again.

Another problem is when I create a record other than "None". For example, if
I create records "E", "M", "CS", and "W" I should NOT be able to select
"None" however I can select it.

The third problem remains regarding the multi-use of the subform. It's used
in multiple forms therefore hard-coding it to frmFormulas renders it useless
in the other forms.

Have I stumbled on an impossible design challenge?

 

[John W. Vinson]

The third problem remains regarding the multi-use of the subform. It's used
in multiple forms therefore hard-coding it to frmFormulas renders it useless
in the other forms.

well... you didn't SAY that.

What I'd suggest instead is putting code in the Subform's BeforeUpdate event.
Use DLookUp to check to see if there is already a NONE record for this case;
if there is, cancel the update. Or if the user is attempting to add a NONE,
check to see if there are existing records and do likewise.


John W. Vinson [MVP]

 

[Guest]

well... you didn't SAY that.

I did actually - it just got lost in the shuffle.

What I'd suggest instead is putting code in the Subform's BeforeUpdate event.
Use DLookUp to check to see if there is already a NONE record for this case;
if there is, cancel the update. Or if the user is attempting to add a NONE,
check to see if there are existing records and do likewise.

Thanks. I'll explore that.