Data Validation

[Marfun]

I am creating a database in Microsoft Access '97. My question is: how can I validate one field in a table/form with the data from another field? For instance, first I have a Yes/No field which is followed by a text field. What I want to do is that if in the first field No is chosen, then the second field becomes such that you are not allowed to enter data into it, or even if you do enter data into it, an error message comes up stating that no data should be entered into this field since you have chosen No. On the other hand, if you chose Yes in the first field, you can freely type into the second field without any error message coming up. How may I program my database so as to achieve this?

 

[Atomic Shrimp]

Here's how I would do it; (there might be an easier way):

The module for the form looks like this:

Option Compare Database
Option Explicit
Public temptext As String

Private Sub TITLE_Enter()
temptext = Me.TITLE.Value
End Sub

Private Sub TITLE_Exit(Cancel As Integer)
If Me.lock.Value = True And Me.TITLE.Value <> temptext Then
Me.TITLE.Value = temptext
MsgBox "You Tried to edit a locked field", , "Amendment cancelled"
End If

End Sub


('lock' is the yes/no field, 'title' is the text field that you want conditionally locked)

you could also (selectively by user) enable or disable the entire 'lock' column so that only certain users can unlock and edit the locked cells.

Hope this is what you were looking for

Mike

 

[Atomic Shrimp]

By the way, Posting the same question everywhere is just as likey to annoy as to get an answer!

 

[DML]

Here's another way (just to give you options):

NOTE: If Yes/No field is Check box then use the following code. If it is a combo or list box, you will need to adjust the code accordingly.

On the After Update event of the Yes/No field, put this code:

if [me].[nameofYesNoField] = Yes then
[me].[nameofTextbox].locked = false
[me].[nameofTextBox].setfocus '/// if this is the next field
else
[me].[nameofTextBox].locked = True
[me].[nameofFieldThatShouldHaveFocus].setfocus
end if

 

[Carol]

Here is another option for you.

Private Sub TextBox_AfterUpdate()
If TextBox = Yes Then
TextBox: 2ndTextBox.Visible = True
2ndTextBox.SetFocus
Else
Me.2ndTextBox.Visible = False

End If

End Sub

You must also set your 2ndTextBox Visible Property to "No"

What this does is hide the second text box if you state No, but makes it visible ready for entry if you state Yes. Replace the field names as appropriate.

Good Luck

 

[Marfun]

I had some problems when I tried to do the following:
if [me].[nameofYesNoField] = Yes then [me].[nameofTextbox].locked = false
[me].[nameofTextBox].setfocus '/// if this is the next field
else
[me].[nameofTextBox].locked = True
[me].[nameofFieldThatShouldHaveFocus].setfocus
end if

When I tried to compile it, I was given the error message that the "[me]" has a 'compile error: Invalid outside procedure'.

What can I do to fix this? I'm sorry but I do not know much about Visual Basic and so I really need help.

Thanks, Marfun

 

[Marfun]

I had some problems when I tried to do the following:
if [me].[nameofYesNoField] = Yes then [me].[nameofTextbox].locked = false
[me].[nameofTextBox].setfocus '/// if this is the next field
else
[me].[nameofTextBox].locked = True
[me].[nameofFieldThatShouldHaveFocus].setfocus
end if

When I tried to compile it, I was given the error message that the "[me]" has a 'compile error: Invalid outside procedure'.

What can I do to fix this? I'm sorry but I do not know much about Visual Basic and so I really need help.

Thanks, Marfun