Avoiding duplicate record 1

[flaviooooo]

Hi,

I have a question that I think will be simple to answer, but I can't figure it out.

I have a table that has a key consisting of Purchase_Order and Item.

I also have a form where the users can create new records into that table.

On the form is a button that triggers the following code:
DoCmd.GoToRecord , , A_NEWREC

But I have a problem when people try to enter a record that already exists. Where do I check for this, and how can I avoid the error messages?

I tried something like this on the after update of the Item field:
strsql = "select * from AFROEPEN_AANKOOP_HEADER where [Purchorder] = '" & Me.PURCHORDER & "' and [ITEM] = '" & Me.ITEM & "'"
Set rs = CurrentDb.OpenRecordset(strsql)

If rs.EOF = False Then

MsgBox ("This order is allready in the table")

' DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTRECORD_V2, , A_MENU_VER20
' DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_DELETE_V2, , A_MENU_VER20

Me.RecordsetClone.FindFirst "[Purchorder] = '" & Me.PURCHORDER & "' and [ITEM] = '" & Me.ITEM & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
end if

But this gives me an error that I can't go to the specified record.

Thanks in advance

 

[TheAceMan1]

How are ya flaviooooo . . .

Have a look at [blue]DLookUp[/blue]!

The Idea is to actually look in the table and see if the key exist . . .

See Ya! . . . . . .

 

[flaviooooo]

But if I do that it will also just check if the value exists, when the record is being created -> error Duplicate Values.

 

[NvrSaDi]

You might want to consider using the Access "indexed" feature in table design "Yes(No duplicates)". This will block input of data in one or more selected and indexed fields.

 

[PHV]

Have you tried this ?
...
Me.Undo
Me.Bookmark = Me.RecordsetClone.Bookmark
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue]But if I do that it will also just check if the value exists, when the record is being created -> error Duplicate Values.[/blue]

Realize when a recorded is edited [blue]its in a noncomitted state[/blue] (not in the table) until its saved. So a DLookUp should produce any duplicates! . . . Get the Idea? . . .

[blue]Your thoughts . . .[/blue]

See Ya! . . . . . .

 

[flaviooooo]

Thanks for all the answers guys.

The Me.Undo was so simple I almost overlooked it, but it did the trick perfectly!