How can i count how many items a customer currently has?

[Miller84]

Hi,

How do I apply a limit to my library database to stop borrowers having anymore than say 7 books at the one time? New loans are recorded via a Loans form which contains a subform allowing the librarian to add new items to the Loan.

I need to make sure that no matter how many Loans take place, the number of items loaned to any borrower over 1 loan or more cannot be more than 7 at the same time.

Initially I had though a Count function would solve this but I'm unsure right now. Any help is appreciated.

Thanks

 

[Ukraine82]

Take a look at the sample file.

hth,

Michael

 

[Miller84]

Thanks, but that doesn't actually count anything. I really need to perform a count based on data that is constantly changing.

 

[trucktime]

Library

I assume the main form contains info about the borrower and the subform has the loans for that borrower. In case the tables are linked properly and on the subform the link child/masterfields have been set correctly you can count the records on the subform and thus get the # of loans for that borrower. You can display a message either on the main- or the subform. If you have a button or your subform for adding new records you can put some code behind it either warning when the max. # of loans has been reached or simple disable the ability to ad a new record.

Trucktime

 

[Miller84]

How would I do this? What sort coding is needed and where would I put it?

 

[trucktime]

Library

You can put an unbound text field in the footer of the subform and put the following in the control source on the Data Tab: =Count(*)

If you want to upload a copy of your mdb we can have a look at it and perhaps be of more help.


Trucktime

 

[Pat Hartman]

Once you have the count field in the subform footer, you would add code to the subform's BeforeUpdate event to block the addition of a new record if the count is > 6.

If Me.NewRecord Then
    If Me.txtCount > 6 Then
        Msgbox Me.txtCount & " Records already on loan.  Please return an item before borrowing any more.", vbOkOnly
        Cancel = True
    End If
End If

 

[Miller84]

Here is a copy of the database. I managed to get the count function to work for each loan. However, borrowers can make more than one loan. So it is still possible to make a loan of 3 books and one of 5 books without triggering an error. Any thoughts would be appreciated.

Many thanks

 

[trucktime]

Limit

Look at the post by Pat, you did not yet put that code in the BeforeUpdate Event of the subform.

 

[Miller84]

I did try it and took it out because it didnt work. Where it says "txtCount" does that refer to the name of the text box where I want to place the total count number?

Is there anything in that code that would need to be changed to match my database or is it a standard code? I'm very much a beginner with this. Sorry!

 

[trucktime]

Change

Yes, if the name of the box where you total the loans is called something like "Totalcount" you will have to change txtCount to Totalcount in Pat's code.
I tested it with your db, and it works.

 

[Miller84]

in the Loans2 form, i have the following code for "bor_currentItemCount" text box:

Private Sub bor_currentItemCount_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Me.bookcount > 3 Then
MsgBox Me.bookcount & " Records already on loan. Please return an item before borrowing any more.", vbOKOnly
Cancel = True
End If
End If

End Sub

(bookcount) is the name of the count textbox in the loan details subform.

Is this right and is it in the right place? Because i don't get any error messages when I try to enter more than 3 books per loan.

 

[Pat Hartman]

I believe I said -

the subform's BeforeUpdate event

The code needs to go in the SUBFORM's BeforeUpdate event. That is the event you need to cancel to prevent the record from being saved.

 

[Miller84]

Success and my apologies

Hi,

You did indeed say "subform beforeupdate" my apologies, i didn't read your post closely enough. The code does now work!! However, when the error message appears all i can do is click Ok, i can't remove the new record without the error message appearing, which in effect creates a problem which can only be solved by closing the form and losing information. Is there anyway around this?


Many many thanks for all your help guys!

 

[Pat Hartman]

On the line following the "Cancel = True", add -
Me.Undo

That will remove all the "typed" data from the record and allow the user to exit with grace.