Issues trying to program rules for certain inputs

[RandomUser]

First of all, I'd like to let you know I am an absolute newbie at coding and I'm trying to write some of my first coding for an Access program I put together.

I'm trying to implement rules for specifics fields in the input screen. The fields are used while I conduct a survey. I'd like to eliminate the possibility of errors by making sure specific fields do not contradict each other.

To be specific, if a valve is not able to be located, it cannot be exercised.

I'm trying to write a code requiring one drop down box to auto-populate another drop down box when a specific option is chosen.

If a valve is not located, I want the option under "Exercised" to be "No"

I went into the input design and clicked on the "NOTES" field>property sheet, data>event>after update>click on three dots>code builder>click OK



If [NOTES] = "UNABLE TO LOCATE VALVE" Then
Me.[EXERCISED] = NO
End If
End Sub

I get an error "External Name not defined"
If I put Me. in front of [NOTES], I receive an error, "Invalid use of Me Keyword"


I have other fields to build rules into and if I can figure this one out, I should be able to apply the same logic to the other fields.





I put in the code I believe is correct (based on help I've received elsewhere), and I've hit a dead end.

 

[theDBguy]

Hi. Welcome to AWF!


It might be easier to help you if you could post a demo version of your db.

 

[arnelgp]

what is [exercised] datatype, Boolean?

Me.[EXERCISED] = False

 

[RandomUser]

Exercised data type is short text

Do I put Me.[EXERCISED] = False at top, underneath the Private Sub line?

 

[arnelgp]

short text, then:

Me.[EXERCISED] = IIF([NOTES]="UNABLE TO LOCATE VALVE", "NO", "YES")
End Sub

 

[RandomUser]

Private Sub Not_Exercised_AfterUpdate()

Me.[EXERCISED] = IIf([NOTES] = "UNABLE TO LOCATE VALVE", "NO", "YES")
End Sub

Error occurs, highlighting [NOTES (NOT EXERCISED)] and says "external name not defined"

 

[arnelgp]

NOTES is the fieldname, No_Excercised is the control name:

Private Sub Not_Exercised_AfterUpdate()

Me.[EXERCISED] = IIf([Not_Exercised] = "UNABLE TO LOCATE VALVE", "NO", "YES")
End Sub

 

[RandomUser]

NOTES is the fieldname, No_Excercised is the control name:

Private Sub Not_Exercised_AfterUpdate()

Me.[EXERCISED] = IIf([Not_Exercised] = "UNABLE TO LOCATE VALVE", "NO", "YES")
End Sub

My mistake, the field name is "NOTES (NOT EXERCISED)", not "NOTES"

 

[RandomUser]

This is strange, the code from earlier worked.

Private Sub Not_Exercised_AfterUpdate()

If [NOTES (NOT EXERCISED)] = "UNABLE TO LOCATE VALVE" Then
[EXERCISED] = "NO"
End If
End Sub

 

[RandomUser]

Next, I'm trying to put a code in for when "Valve #" is populated , AND the "Location" is null, a prompt appears to remind the user "Please enter the valve Location!"



Ive tried putting this into the BeforeUpdate and

Private Sub VALVE_#_BeforeUpdate(Cancel As Integer)
If Not IsNull([VALVE #]) And IsNull([VALVE LOCATION]) Then
[VALVE LOCATION].SetFocus
MsgBox "Please enter Valve Location!"
Cancel = True
End If
End Sub

Note: Some valve #s have alphanumeric characters, so it's a short text field.



The top line is red and nothing happens. No error, but also, no prompt pops up if valve # is left blank. It's as if nothing has changed.

 

[theDBguy]

Next, I'm trying to put a code in for when "Valve #" is populated , AND the "Location" is null, a prompt appears to remind the user "Please enter the valve Location!"

Ive tried putting this into the BeforeUpdate and

Private Sub VALVE_#_BeforeUpdate(Cancel As Integer)
If Not IsNull([VALVE #]) And IsNull([VALVE LOCATION]) Then
[VALVE LOCATION].SetFocus
MsgBox "Please enter Valve Location!"
Cancel = True
End If
End Sub

Note: Some valve #s have alphanumeric characters, so it's a short text field.

The top line is red and nothing happens. No error, but also, no prompt pops up if valve # is left blank. It's as if nothing has changed.

Hi. This could go on for a while. Are you not able to post a small copy of your db? Just wondering...

 

[arnelgp]

plz use Me., it so the Intellisense will appear and you can pick the correct Control name from the list.

 

[RandomUser]

Hi. This could go on for a while. Are you not able to post a small copy of your db? Just wondering...

I'm sorry but I can't.

 

[theDBguy]

I'm sorry but I can't.

Okay, thanks, at least I don't have to wonder anymore. Good luck!