Help With Validation Rule

[Eyeswideopen11]

Im creating a library management system and I created a form for adding new borrowing events, it includes book, member name, book status, default value for the checkout date that will automatically populate for current date, due date that automatically calculates with dateadd function and return date. I need to figure out a validation rule that will prohibit a new member from checking out a book if the book status is out. The only idea I have is to prevent the checkout date from populating if status is "Out".

Thanks,

 

[ebs17]

Books with the status "Out" should not even be displayed in the form, i.e. they should be filtered out.

 

[plog]

Seems similar to your prior post that you never acknowledged nor followed up on:

Query Criteria

Hi, I have a query that shows all the books that have been borrowed. I created a form to show all these events they include Date Out Date Returned whether the book is out or available. I need to be able to add new borrowing events to this form, but I need a way to restrict it based on whether...

www.access-programmers.co.uk

 

[Eyeswideopen11]

Seems similar to your prior post that you never acknowledged nor followed up on:

Query Criteria

Hi, I have a query that shows all the books that have been borrowed. I created a form to show all these events they include Date Out Date Returned whether the book is out or available. I need to be able to add new borrowing events to this form, but I need a way to restrict it based on whether...

www.access-programmers.co.uk

Sorry I have been busy completing the project. The suggestion to the previous post was valid and if I could start all over I would have done that. Unfortunately I am almost done and since I am new to access I don't want to screw anything up that I have already completed. I just thought there was a way to modify what I currently have with a validation rule for the form instead of creating another table, query etc. Thanks for the suggestion.

 

[plog]

Unfortunately I am almost done and since I am new to access I don't want to screw anything up that I have already completed.

The most unfortunate thing about that is the mindset. Even though this database is new and not complete, you'd rather not address an issue because your so close to an improperly finished database than a properly finished database, that it just isn't worth it.

 

[Eyeswideopen11]

ok thank you. Its not that at all I don't want to screw anything up that I have already done because I am not as experienced with access. Also it is due by midnight tonight.

 

[Gasman]

I would also vote for not showing anything they cannot select.?

 

[Eyeswideopen11]

I would also vote for not showing anything they cannot select.?

thanks

 

[The_Doc_Man]

It might be harder to use the result of a validation rule than it would be to filter the form to exclude books that are already out.

To use the validation rule, you can say that you can't mark a "checked out" flag if it is already marked, but that would trigger an error event (constraint violation). I.e. you would have to program an error trap.

To use a filter to prevent showing a checked-out book in the first place, you can set a form's .Filter property, which acts like a supplement to a WHERE clause. See this link:

Form.Filter property (Access)

Office VBA reference topic

learn.microsoft.com

Setting a filter? Takes about two minutes tops and no errors are involved unless ALL books are checked out (i.e. no books left!)
EDIT: Two minutes during DESIGN phase - and you can leave it set for normal execution where you'll never see it happen, it will just happen!

 

[Eyeswideopen11]

It might be harder to use the result of a validation rule than it would be to filter the form to exclude books that are already out.

To use the validation rule, you can say that you can't mark a "checked out" flag if it is already marked, but that would trigger an error event (constraint violation).

To use a filter to prevent showing a checked-out book in the first place, you can set a form's .Filter property, which acts like a supplement to a WHERE clause. See this link:

Form.Filter property (Access)

Office VBA reference topic

learn.microsoft.com

thank you I will try that

 

[Pat Hartman]

The checkin/out form should not be updateable. It should contain an option group with two buttons. In and out.
You shouldn't even have a checked out flag. You should have a checked out date and you should have a checkedOutBy field. The click event ot the option group controls what happens. if the out button is pressed, the code looks at the checkout date. If it is not null, the date is set to today's date and the by field is set to the person who is logged in. Otherwise, you get an error saying the book is already checked out. if the In button is pressed, the code sets the checkout date to null

Then, your form can show all books whether they are in or out which I believe is correct. The library does not remove a book from the card catalog when it is checked out and so neither should your form. If the book is destroyed or otherwise removed from the library, it should have a status field that lets you hide it from view but still keep it in the table if you want to.

 

[Eyeswideopen11]

The checkin/out form should not be updateable. It should contain an option group with two buttons. In and out.
You shouldn't even have a checked out flag. You should have a checked out date and you should have a checkedOutBy field. The click event ot the option group controls what happens. if the out button is pressed, the code looks at the checkout date. If it is not null, the date is set to today's date and the by field is set to the person who is logged in. Otherwise, you get an error saying the book is already checked out. if the In button is pressed, the code sets the checkout date to null

Then, your form can show all books whether they are in or out which I believe is correct. The library does not remove a book from the card catalog when it is checked out and so neither should your form. If the book is destroyed or otherwise removed from the library, it should have a status field that lets you hide it from view but still keep it in the table if you want to.

I really appreciate all suggestions and they are all valid unfortunately I am restricted to what the instructors instructions. I need to do things laid out in the final project instructions. The instructor specifically for a validation rule to restrict borrowing if the book is not available. As for the check in and out form the instructions ask for a form to add new borrowing events so I would assume it has to be updatable.

 

[Gasman]

Blind leading the blind.

You could check the checkout date. If populated, book is out, if not, book is available?

 

[plog]

Blind leading the blind who refuses to be lead on the sidewalk and wants to be lead into traffic.

 

[Eyeswideopen11]

Blind leading the blind who refuses to be lead on the sidewalk and wants to be lead into traffic.

You know the smart ass comments are really not helpful. I understand what each of you are saying about the best way to do, but I have to following the instructions given to me. Now if you want to express your opinion about the instructors comments feel free. I am not the one who wrote it.

 

[ebs17]

a validation rule that will prohibit a new member from checking out a book if the book status is out

Why is there a restriction on new members? When a book is out, existing members can't actively borrow either (in my world).

 

[Eyeswideopen11]

That's correct. Here is the instruction

A form for adding borrowing events. Add two command buttons to your form that will allow you to open the other two forms. Use proper validation rules to make sure the return date cannot be earlier than the borrow date (but it can be the same date). Make sure that a book can’t be borrowed that is already being borrowed.

 

[plog]

You've misread the instructions.

The instructor specifically for a validation rule to restrict borrowing if the book is not available.

Incorrect. he specifically said :

Use proper validation rules to make sure the return date cannot be earlier than the borrow date (but it can be the same date)

In fact, a validation rule for the above is the correct way to implement it. Then in another sentence he gave more criteria, without any mention of a validation rule:

Make sure that a book can’t be borrowed that is already being borrowed.

The method 4 people have put forth is the correct method to implement for the above sentence.

 

[XPS35]

You've misread the instructions.

And also crossposted at https://answers.microsoft.com/en-us...pulating/f7a6a0db-aa9f-49c9-8f66-3161ecb269b9

 

[Gasman]

And also crossposted at https://answers.microsoft.com/en-us...pulating/f7a6a0db-aa9f-49c9-8f66-3161ecb269b9

Seems to have received the same advice over there.