Checking for Limit Reached on a Form

[Trisha]

I am working on a library project and one of the requirements is that a patron is limited to having no more than 5 books checked out at one time. I created a query that returns the number of books checked out based on a patron ID. I want to use the result of this query on the check-out form. For example, when a patron ID is entered the query should run and if the result is more than 5, an error message should be displayed. I cannot get it to work. Any ideas?

 

[DougP]

you can do this with code<br>
---------------------------<br>
Private Sub Command16_Click()<br>
On Error GoTo Err_Command16_Click<br>
Dim db As Database, rst As Recordset, SQL As String<br>
Set db = CurrentDb<br>
' SQL string.<br>
SQL = &quot;SELECT * FROM Books WHERE Patron = '&quot; & Me!Patron & &quot;' and BroughtBack = 0;&quot;<br>
Set rst = db.OpenRecordset(SQL)<br>
rst.MoveLast<br>
Me!CheckedBooks = rst.RecordCount<br>
<br>
rst.Close<br>
db.Close<br>
Exit_Command16_Click:<br>
Exit Sub<br>
<br>
Err_Command16_Click:<br>
MsgBox Err.Description<br>
Resume Exit_Command16_Click<br>
<br>
End Sub<br>
<br>
------------------------<br>
On your form put a box that the partron ID is keyed into<br>
then have a Command button and put that code above in there.<br>
Next have another textbox on your form that is called &quot;CheckedBooks&quot; which return th enumber of books out.<br>
<br>
Now for this to work there needs to be one or more records that are the patrons everytime they have checked out a book<br>
The code counts the number of records in the database that match.<br>
Also you need to have a field that is a Check box so when they bring a book back it does not show as well.<br>
I called it &quot;BroughtBack&quot; in my example above and it is a &quot;Yes/No&quot; type<br>
So when they bring a book back you click this box and next time when they comeitn it won't show.<br>
Now you could delete the record as they came back but then you would not have history.<br>
<br>
OK <br>
I tested it and it works great.<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

Rather than a button, you could use the OnLostFocus event of the PatronID control to trigger the code. Or you could use the button, and make the tab order such that it is the next item, so that the user can hit the enter key instead of clicking a mouse. I guess I'm getting aggravated lately at all the back and forth between keyboard and mouse.

 

[Trisha]

I'm having a problem getting that to work. Is there a way to link a field from a query to a form that it's not related to? I have the fields patronid and datereceived on the form and the countofbooks field in a query. If I can relate them I think I can get it to work.<br>
<br>
Thanks,