Prevent new entry 2

[jsgs]

Problem is about a boat rental system.

I have a main form for customers, frmCustomer. It contains a subform, fSubRental, to record rental transactions of the customer.

To record a rental, I choose the customer from main form, then I enter his rental details (boat, time out, etc.) on the subform.

What I would like to do is to prevent the recording of a rental for a customer who has already loaned a boat and has not yet returned it. I would like a message to be displayed the moment the user tries to enter the boat name for the selected customer.

Thanks in advance for your help.

 

[MajP]

build a query that shows: all customers who have already loaned a boat and has not yet returned it. Then use a dlookup on this query where the criteria is the person who is trying to rent a boat. If the dlookup returns null then that person is not in the query of people with checked out boats. If they are in the query pop up your message. Normally this is done in a before update event, so you can cancel the event.

 

[PHV]

I'd filter the main form for customer not having pending loaned boat ...

 

[smedvid]

Or disable the form Add/Insert functions to be 100% sure... I've done similar stuff to ensure only one entry...
Something like this ...
in On Current; and perhaps On Delete?

Private Sub Form_Current()
If SomeFunctionToCheckRentals = True Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
EndiF



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[jsgs]

Thanks all for your help. I managed to do the dlookup and it does find those customers that already have a pending rental. But now I've got 2 new problems:
1) All my forms are bound so when I enter data it gets automatically saved. Now I've put the Dlookup code in the BeforeUpdate event of my Select Equipment combo box. This means that I have to click to get the code running. But when I click it means it creates a new entry in the Rental table, which of course should not happen. How can I prevent Access from saving this record?
2)When I click on New Rental it says "Cannot go to the specified record." Why is this?