Stopping Duplicate Records in Access97 1

[rocknrisk]

Hi all,

I am trying to add a VBA check to a TextBox object to ensure that I don't end up with duplicate records in an existing Access97 database (I can't change "Indexed" property in the table to "Yes (No Duplicates)" because the table is linked).

I have searched Tek-tips and found a bit of code by "RickSpr" in thread181-62060 that seems to do exactly what I need except it doesn't work for me (it worked for the chap he was responding to).

I get *** Run-time error '3075' - Syntax error (missing operator) in query expression 'Request Uid'. ***

This is my code:

Private Sub Request_Uid_AfterUpdate()
If Nz(Request_Uid) <> &quot;&quot; Then
If Not IsNull(DLookup(&quot;Request Uid&quot;, &quot;HH Acceptances&quot;, &quot;Request Uid = &quot; & Request_Uid)) Then
MsgBox &quot;Warning - Supply Request UID already exists in Database&quot;, vbInformation
End If
End If
End Sub

HH Acceptances = a table
Request Uid = a field in that table
Request_Uid = the TextBox in the form I'm working on

Please can someone help me. I have limited knowledge of Access to start with and I did not design and build the database.

Thank you in advance.

&quot;The important thing is not to stop questioning.&quot; - Albert Einstein

 

[RoyVidar]

One difference between the code in the other thread and yours, is that your field names contains spaces. Try using [brackets] on them.

[tt]DLookup(&quot;[Request Uid]&quot;, &quot;[HH Acceptances]&quot;, &quot;[Request Uid] = &quot; & Request_Uid)[/tt]

Roy-Vidar

 

[rocknrisk]

Hi RoyVidar,

Talk about the simple things. And they always seem to work.

It works.

Thank you. You're a star.

&quot;The important thing is not to stop questioning.&quot; - Albert Einstein