How to make an error box appear when a limit has been reached?

[dan munday]

I currently have a form, where the users enter data and loan out books.
I need some way of limiting each user to just 2 books, I have a loan count in a Query called "Bookloans",

when the save record button is clicked, im looking to have an "IF" in the Vba codes that refers to the specific field with the users id, and how many books they have, if this value is equal to 2, then bring up a message error box stating that they have already reached the maximum ammount of books, and close the form, if it is lower, then just save the record

Thanks,
Dan

[NeoPa]

Is the form based on data that includes the number of books loaned to the user? If so, it would make sense to disable the function of the form when a non-qualified user is loaded. If not then a DLookup() or DCount() would be required.

If you feel the answer is very non-specific, then you need to consider how you ask your question. The question has too little detail for a more specific answer.

[dan munday]

im sorry i dont know how to explain it properly.

no, the form is based on a table where each student's loans are not counted, its only in a query where i've counted them,

so when i click the save record button on this form, i want it to perform an 'If' that will look into this query, for a field called Loans, and if that answer is equal to 2, open up a message to stay they cant take out anymore,
but if it is less than two, just to carry on with the save

im guessing if i edited the 'save record' buttons code it would be changed to something like:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
 
    If ((the link to the existing query, and field, and somehow find that student automaticaly)) Then
    MsgBox "You curretly have the maxiumum number of loans"
    DoCmd.Close
    Else
    DoCmd.RunCommand acCmdSaveRecord
    End If
 
Exit_Command20_Click:
    Exit Sub
 
Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
 
End Sub

i hope this makes what im trying to do seem clearer, i cant get the right name of the placing of where the query is, and im currently getting the error "Microsoft Access can't find the field '|1' refered to in your expression.

[NeoPa]

OK. I hear you trying to explain as well as you can. Let's see what we can do to assist.

The simple answer is the one I gave in the first response. You need to use DCount() of whatever recordset you have in your database that lists the existing loans.

That's the fundamentals sorted. I will also throw in some points that will help you get to where you want to get to :

1. Your pseudocode illustration is fundamentally along the right lines. You may want to consider a few amendments though.
2. If a problem is found, closing the form entirely (DoCmd.Close) is probably not the smoothest way to handle it. Further points below will cover alternatives (in context).
3. Ask yourself - Do yo want the operator to find out they cannot add a new loan only after they have spent the time filling in the form? If the answer to that is No, and I imagine it must be, then try putting the code somewhere where it will be run whenever a new record becomes current on the form, and then the code would enable or disable the ability for the operator to fill in any details for that record. The code would have to be placed in a common routine and called from the event procedures for opening the form istelf, as well as for when the current record changes. These events are Form_Open() and Form_Current(). The fundamental structure of such code would be :
   

   Private Sub Form_Open()
       Call CommonRoutine
   End Sub
    
   Private Sub Form_Open()
       Call CommonRoutine
   End Sub
    
   Private Sub CommonRoutine()
       Me.Command20.Enabled = (DCount("*", _
                                      "[BookLoans]", _
                                      {Criteria string}) < 2)
   End Sub

   NB. This is simplified to illustrate the concept. I don't know much about your form's design, so you need to incorporate the idea into your code.
4. The DCount() code requires a knowledge of your data structure to formulate. This is not available to us at this stage.
5. I assume the query you mention (BookLoans) is the one you need to refer to in the DCount() call. It sounds like this may not be working correctly just yet. If you want help with that you will need to ask for that in a separate thread. We don't like, or allow, multiple questions in the same thread here. You may add a link to your new thread in here if you like.

[dan munday]

Thank you NeoPa :) i believe it is my query that is currently wrong.

your information has helped alot, and thank you for editing my previous post, I'm new to this website also and hadn't figured out how do the code text layout :)

[NeoPa]

No worries Dan.

If you'd like me to look at the thread related to the query (if you post one) just pop a link to it in here and I'll take a look.