Validation Rule question

[lmcc007]

I have two tables. The main table fieds are: Word, Pronunciation, and Meaning. The second table fields are Word and Homonym--both are primary keys. Word and Homonym should never be the same--that is, hear and hear. How do I write a validation rule to keep the data entry accurate or to keep this from happening?

Word Homonym
Hear here
Here hear
Hear hear (should never be allowed)
Here here (should never be allowed)

 

[DJkarl]

I have two tables. The main table fieds are: Word, Pronunciation, and Meaning. The second table fields are Word and Homonym--both are primary keys. Word and Homonym should never be the same--that is, hear and hear. How do I write a validation rule to keep the data entry accurate or to keep this from happening?

Word Homonym
Hear here
Here hear
Hear hear (should never be allowed)
Here here (should never be allowed)

If you're using a form for entry put the rule on the before update event for both fields, if they are the same then cancel the change.

If you are wanting a validation rule actually IN the table itself then I think you're out of luck, I don't think SQL allows multiple columns to be used in validation rules.

 

[lmcc007]

DJKarl,

The new entries will be in a form. How do I do that in the form? (I’m a dummy at this, so can you write it very, very simple.)


There are thousands of records in Excel that we copied and pasted into Access, which now we're seeing errors. We're trying to not have to go and do each one.

 

[DJkarl]

DJKarl,

The new entries will be in a form. How do I do that in the form? (I’m a dummy at this, so can you write it very, very simple.)


There are thousands of records in Excel that we copied and pasted into Access, which now we're seeing errors. We're trying to not have to go and do each one.

You would need to replace Text2 and Text4 with your names but basically this what I would do.

Private Sub Text2_BeforeUpdate(Cancel As Integer)
If Me.Text2.Value = Me.Text4.Value Then
    MsgBox "You cannot have the same value in both fields!"
    Cancel = True
End If
End Sub

Private Sub Text4_BeforeUpdate(Cancel As Integer)
If Me.Text2.Value = Me.Text4.Value Then
    MsgBox "You cannot have the same value in both fields!"
    Cancel = True
End If
End Sub

 

[lmcc007]

Thanks DJKarl,

I'll try this.

But, I like working in tables because I'm used to Excel. I was trying to use the <> in the Validation Rule but it didn't work. I read something like that a while back regarding dates (that is, making sure the termination date is not the same as the start date), which was done in tables.

 

[RoyVidar]

When in table design view, bring up the properties sheet (Alt+F4), then in the validation rule property (among the table properties), enter

[Field1]<>[Field2]

What you were probably working with, was a column level validation (i e, working with the validation rule property of a field), while the above, is a row level validation - which can refer to more than one column in the same row.

(You can even get table level validation using Jet, but that's somewhat lacking in support within the Access interface, and you'd need ADO/DLL to create/maintain)

 

[lmcc007]

Thanks RoyVidar!

Works perferfectly.

Also, my "Alt+F4" keys takes me out of Access, but "Alt+Enter" will open the validation rule property dialog box.

Thanks!!

 

[RoyVidar]

Ah, yes Alt+Enter, of course...

 

[The_Doc_Man]

Another thought is that you could use an APPEND query based on a raw import of the spreadsheet to an INTERMEDIATE table.

First, do the raw import to a new table.

Then run DELETE queries on records that violate any of your screening laws. Like for example, DELETE * FROM TEMPTABLE WHERE [MainWord]=[Homonym] ;

You can also do UPDATES on the temp table to upcase or downcase everything in the mainword and homonym columns. And Trim$() for the words, too.

In other words, massage the holy Hell out of the TEMP table. When you are done, then and only then, import it to the final destination.

If you are going to index the destination table, my suggestion stops you from thrashing the crud out of the indexes so badly that you need to compact the result before you even try to use it. Indexes aren't needed in the TEMP table if you are doing (1) row-local updates and testing, and (2) simple edits for cases and spaces. Doing the append to the table with indexes already defined ONLY after you've done the filtration will make the result easier and cleaner to load. And less likely to give you key violation errors.

You'll still have to be careful, though, because many homonyms are more than duals. Like, maybe "their" and "there" and "they're" ?