Duplicate Records in Access 2010

[Taloker]

If somebody can help on the following case
I have car rental Table with Following fields
Plate
Model
Status (Combo) ( Could Be “AVL” or “Closed”
No problem if “Closed ” is duplicate

Plate Status
123 Closed
123 Closed
123 AVL

But Plate with AVL should not be repeated
How this can be done in ACCESS 2010 VBA

thanks

[hvsummer]

??? == you mean you want to create table restrict input that only 2 field closed and AVL which AVL can't be duplicate ?

I have 2 method pop-up in my mind:

first method, I think about validation rule, but this can't prevent duplicate input.

secondly I think about query the table with 1 additional field
"valid" with Subquery

Select Plate, Status, Q.Valid
From Tbl inner join 
(Select PLate, count(Plate&Status) as Valid 
From tbl
Group by Plate) as Q ON tbl.Plate = Q.PLate

ok, now you can combine 2 step, restrict input by Close and AVL
then check the status valid by the query (if valid field have value 2, then status of that "PLate" is duplicate - recheck it)


the second method is to use form.
create simple form from that table.
on afterupdate event of "status" textbox/combobox/listbox (whatever you need)

Private sub status_Afterupdate()
'we will notify people that AVL is duplicate and undo what they typed
if me.status = AVL then
     if Dlookup("status", "table", "plate ='" me.plate"'") like "AVL" then
msgbox "You have typed duplicate status for "&me.plate&", recheck infomation!", vbInformation
me.undo
     end if
end if
end sub

now, whenever people input status in status field (textbox/listbox/combobox), VBA will check it after they finish input and show up an error and undo if AVL is duplicate.
Code will need somemore tweak for another situation can happen, but that is the right way for you I think.


edit:
method 3 is just let they input what they want, then delete those duplicate with query lol