Limits for maximum values of each field

[alfaomegas]

Hi everybody I looking for a solution to my problem. I have a access database and I have to put some limits for maximum values of each field. Let’s say there is lots of different customers and they have some limit numbers of orders, which they can order. For each customer is this limit different. I want to keep adding new orders till the limit is reached and then can’t be add any more numbers for new orders for each customer. Is there any way how I can set in access database this limits? Thank you for any help in advance.

[friendlyfriend]

Hi Alfaomegas,

Sure. Lots of 'em.

This might work...

Add a MaxOrderCount integer field to your customers table and enter values for all the customers.

Put OrderCount into your form's RecordSource as a dependent field that is computed by counting orders. Do something like this for every limited field. For OrderCount, it'll be =DCount("OrderID", "tblOrders","CustomerID=" & [CustomerID])


Put =EnforceLimit("OrderCount") into the cboCustomerID AfterUpdate event. You put it here because you want to alert your users as soon as possible so they don't have to fill out the whole order before being told the customer can't order anything.

Add this code

Function EnforceLimit(FieldName as String)
If Me("Max" & FieldName) = Me(FieldName) then  
    MsgBox "Sorry.  This value for " & FieldName & " has reached its limit."
    Docmd.Cancelevent or ActiveControl.Undo or ActiveControl= ActiveControl.OldValue.  
'  I always tear out hair getting Access to undo things the way I want.  One of these should work.
End if
End Function

Then do the same for the other controls.

[alfaomegas]

Dear friendlyfriend,

Thank you so much for your response. And thanks as well for great explanation...I have been going trough and traing to make it work yeasterday.

Im not to profesional and even I try...there is always something what doesnt work

is there any easier option?

What do you mean by cboCustomerID?


"Then do the same for the other controls. " so i have to add the code for all customers?

[friendlyfriend]

No problem. Everyone's level of Access fluency is different. So answers won't always be given with the right level of detail.

cboCustomerID is a name that you might be using for the combobox for selecting the customer of the order. By convention, "cbo" indicates that something is a Combobox. "txt" is used for Textboxes, etc.

You don't add the code for every customer. You add it for every control (text box, combobox, etc) that needs to do a check. The example I gave was a check on OrderCount that must run whenever the combobox for CustomerID was changed. Alternatively, you could have a "Save" button that does all of the checks at once, but this isn't so good, because a user will have gone to the trouble of filling out a whole order for a customer before being told that the customer can't place any more orders. It's all very dependent on the nature of your limits. Maybe a customer is only allowed to order 3 widgets if he has also ordered on gizmo. Stuff like that.

Unfortunately, I don't know enough about your user interface or data structure to get much more specific. You might have an unbound popup form for choosing the customer. That'd be a good idea. You might have Order records on a main form and OrderDetail records on a subform that appears upon validation of the Order fields. I don't know what all the other fields are or which ones need limits or how the limits are defined.

Access provides a simple ValidationRule attribute where you can put something like this, but I think the rule has to be static and can't accommodate validations that differ by customer.

[alfaomegas]

Hi there, finnaly I move little in front and get to next problem haha...still even before I will be able to use the VBA code. I have a form now with all entry data, now I add to this form two new fields. One of them is max order count. and the second on is suma of actual orders(which I add by the combobox, because is alredy part of query). So I have now all this data in one form, but now new problem show up. Im not able to add new record. Is give me this: you can't go to the specified record.

I try to deal it by all ways...but I dont know why i cant put in new record. So I cant even try if the code you send me before will work ((

do you have any idea? thank you so much in advance

[friendlyfriend]

This is tricky to explain without seeing your RecordSource query. You can't have any group by's or counts or sums in the query if you want it to be editable and allow additions. I think that's probably what you did. Instead, leave the RecordSource as it had been in the query editor (so click the sigma or Sum button so the Total line goes away) and add put this into the Field box in the empty column to the right of the others...

OrderCount: DCount("OrderID", "tblOrders","CustomerID=" & [CustomerID])

Now what you've done is put an additional dependent or computed field into the query. It is computed by a function, DCount(), that depends on the other field, CustomerID. It counts up all the records in tblOrders where the CustomerID equals the CustomerID of the given Order record. This will probably give you an error when you're on the new record. So change it to

OrderCount: DCount("OrderID", "tblOrders","CustomerID=" & nz([CustomerID],0))

which substitutes CustomerID 0 (one that should not exist) whenever CustomerID is null (on the new record)

So...

This is all well and good, but please be thinking long term about using a tool that's better than Access. The world is about the internet and the time you spend learning ins and outs of a non internet tool like Access is tragically lost.

[alfaomegas]

Hi Hi, firstly thank you again for all your valuable information, its really helps me out...Today I change the form and make a little diffrent structure of it. So I have information for adding in the form and that I put the max limit in the same form by using subform. Same thing I done for suma of orders...I just add it to the form by using other subform. So now I have one form, with all information and I can add new record. So the only thing now is to make work the VBA code, which you send me, I try to apply that already, but unfortunatly doesnt work...maybe is it because the data are in difrent subforms...not really sure. But even I go over limit is still let me save the new orders...uff any advise for that?

[friendlyfriend]

Hey buddy,

I really wish you the best, but this is way too complicated to explain by back and forth message. You have multiple subforms? Oye! You're going to need to do requerys here and there and validations and maybe some undos of one sort or another and on and on. I'm going to have to bail out on this one.

I am semi willing to help out a beginner with a really quick question or even help someone with experience and who has sadly invested too much of his soul into Access to adopt a real technology, but I have a moral objection to helping a beginner with Access to become an expert with Access. Access is not a good tool and its mastery should never ever be pursued.

My advice is to start learning a proper development platform, not Access. Here are some links for Ruby on Rails stuff
http://pragprog.com/book/rails32/agi...with-rails-3-2
http://railscasts.com/
http://guides.rubyonrails.org/getting_started.html
http://railsinstaller.org/en


Good luck!!!!


Unfollowing,
friendlyfriend

[alfaomegas]

thanks for your great advises...is gives me some good dirrections