Prevent duplicate values based on more than one field

[AdamSmith]

Hello everybody...

I have form based on table (Borrow) with following column name:
Borrowid PK (autonumber), Bookid, Satuts

My questions is that, for "Bookid" with "Satuts", I would like to prevent duplicates when entering in the form.

I can't make all the fields primary key because "Bookid" and "Status" can be duplicated.

I tried this code in macro builder but it didn't work with me and i got this message:the object does not contain the automation object ms access

If(DCount([BookId],[Borrow],[BookId]=[Combo175] And [Status]="Borrowed")>0 Then
MsgBox "this book has been borrowed"
Me.Undo

Anybody can help me?

there are attachment

 

[Gasman]

Create an index with the fields and make the index unique.

 

[AdamSmith]

Create an index with the fields and make the index unique.

thank you for your reply. but the first field is PK (autonumber), and other fields can be duplicated.

 

[isladogs]

Can you ever get duplicates in the combination of BookID and Status taken together.

In other words will you ever have two records showing the same book as borrowed?

 

[Gasman]

thank you for your reply. but the first field is PK (autonumber), and other fields can be duplicated.

No, I mean combine the fields to create a new index and make that index unique.

I have just tried it on a table of mine using the PK and another field and it did not complain.?

 

[AdamSmith]

Can you ever get duplicates in the combination of BookID and Status taken together.

In other words will you ever have two records showing the same book as borrowed?

On. You can get duplicates Just If the same book as "returned" Not |borrowed"?

 

[isladogs]

No, I mean combine the fields to create a new index and make that index unique.

I have just tried it on a table of mine using the PK and another field and it did not complain.?

Whilst the index idea is good (hence my last reply), if you include the PK with another field in an index it has to be unique due to the PK!
The combined index needs to be on the BookID and Status fields only and set to No duplicates

 

[isladogs]

On. You can get duplicates Just If the same book as "returned" Not |borrowed"?

Posts crossed. In that case you need an additional field StatusDate with a record of when it was borrowed or returned. If you include time as well as date, the records will always be unique on those three fields so index them as a group.

 

[AdamSmith]

I don't know if I explained my idea clearly. but I will try right now:
this access file is for a library, so any member borrow any book, the other member can't borrow the same book unless the book is returned.

Example:
No--------- Books--------- Status
1---------- Book1 --------- borrowed. True
2---------- Book1 --------- returned. True
3---------- Book1 --------- borrowed. True
4---------- Book1 --------- returned. True
5---------- Book1 --------- borrowed. True
6---------- Book1 --------- borrowed. False

Notice that, the same record will Update status field from "borrowed" to "returned" if the book returned Indeed as you can try in my access file.

Thank you in advance

 

[Gasman]

Wouldn't you just have one record for each book, with author, genre etc with a status.?
Keeping track of when it goes out and comes back in would be in a separate table.?

 

[isladogs]

Just had a quick look.
Your Borrow/Return table structure could be improved
Gasman's suggestion is worth exploring further

But even with your existing tables, I believe my 3 field unique index idea is valid

 

[MajP]

I think I would just have the single Borrow Return table with the date fields and no status fields anywhere in the DB. To me that is a calculated field. You can query the DB to determine if a book is checked out. If there is a record with a null DateReturned then that book is checked out.
DateBorrowed
DateDue 'If necessary
DateReturned

On the forms the choices to check out a book would be limited by a query to only those books that are not checked out. Same thing on the form to return a book. Your list would be filtered to the books checked out.

 

[AdamSmith]

Thank you Gasman, isladogs and MajP for your discussions...
appreciate your help.

I solved this Issue by MajP's suggestion