Over Credit Limit
By Richard Rost
35 days ago
How to Check if Customer Exceeds Their Credit Limit
In this Microsoft Access tutorial, I will show you how to check if a customer is over their credit limit without using any VBA code. You'll learn how to use the DSum and NZ functions to calculate unpaid orders for each customer, set up calculated fields on your customer form, and display the remaining credit. We will also use conditional formatting to highlight when a customer is over their limit. This lesson builds on the TechHelp free template and covers creating aggregate queries, handling null values, and useful form design tips.
Miriam from Scottsdale, Arizona (a Gold Member) asks: In your blank template database series, you showed us how to set a credit limit for each customer. I run a small shop and many of my regular customers have unpaid invoices at the same time. Before I add a new order, I want to see on the customer form how much credit a customer has left, or if they are over their limit. I am not ready to use VBA yet, but I want a simple way to display the available credit before creating the next invoice. What is the best way to set this up?
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, check if customer over credit limit, credit limit field, customer form, unpaid invoices, DSum function, NZ function, conditional formatting, order summary query, aggregate query, calculated field, TechHelp free template, invoicing system, order totals, customer ID criteria, refresh record
Transcript
Today we are going to talk about how to check if a customer is over their credit limit in your Microsoft Access database.
We have one today for the expert students, and I consider expert to be that intermediate level, that middle ground between beginner and developer. We will need to use a couple of functions from our Access Library, but we do not need any VBA code to do this.
Today's question comes from Miriam in Scottsdale, Arizona, one of my Gold members. Miriam says in your blank template database series, you showed us how to set a credit limit for each customer. I run a small shop and many of my regular customers have unpaid invoices at the same time. Before I add a new order, I want to see on the customer form how much credit a customer has left or if they are over their limit. I am not ready to use VBA just yet, but I want a simple way to display the available credit before creating the next invoice. What is the best way to set this up?
Well yes, Miriam, you are right. On our customer form, we did set up this credit limit, but we did not do anything with it. We have orders in the system, but these do not really go anywhere. The point of this was just so I could show beginners how to set up and use a currency field.
Today I am going to show you how to do this, how to look at the unpaid orders in the system. If you go to orders, for example, you can see he has got one unpaid order for 360, and then we will say how much credit he has left, and if it is over the credit limit, then we will make it look like that. We can do all of this without any programming, but first a couple of prerequisites.
I am going to be using my TechHelp free template that is a free download off my website. If you want, you can grab a copy off my website. Also watch this video that explains how I built that invoicing system. Go watch this. It starts up my blank template video, which is this guy. And there are a couple of videos involved that will teach you how to build that invoicing database.
You should know how to create calculated fields on a form. We are going to use the DSum function to add up all the unpaid invoices. Make sure you are familiar with this guy. It is a relative of DLookup. We are going to use the NZ function to convert null values to a zero. And we are going to finish it off with a little conditional formatting.
These are all free videos. They are on my website. They are on my YouTube channel. Go watch all of those and then come on back.
Here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. If you watch the invoicing video, you already know that there might be a newer version available on my website on the downloads page. This is version 1.98, but it is not that big of a deal. All these versions should work fine.
On our customer form, we have this credit limit, but it is just a number. It is just sitting there not doing anything. Customers can have orders. Now this order total is calculated based on all of the line items.
We have a query called the order summary Q that takes each order and groups all the items together, so we have one total over here for all of the orders. So now all we have to do is say, I want to add up all of the order totals for the current customer where is paid is false, and that will give you their unpaid order total.
In case you skipped the lesson where I made this order summary Q, it is an aggregate query. We bring together the order table and the order details. We group by the order and then we sum up the order total for each line item, and that gives us this result. Here is a video if you want to learn more about aggregate queries.
What I like to do with this query open so I can see my results is open up my notepad. I like to write the DSum or the DLookup or whatever function it is while I am looking at those fields.
So it is going to be DSum. What field am I adding up? That is order totals right there. And where am I getting it from? That is order summary Q. Now what is the criteria? I have two criteria. The customer ID has to be the currently open customer. I am going to be on the customer form when I write this so I can reference the customer ID. So it is going to be customer ID equals, now I have to close the quotes and put an ampersand there for some string concatenation, and I am going to put the customer ID at that spot.
It is going to end up looking like this: customer ID equals 1234. Normally, if that is the only thing I wanted, I would close the quotes and close that up. But I also want to add: and is paid equals false. Now we have to replace that 1234 again. So right here we are going to close the quotes and customer ID and open the quotes back up again. See how that works? This whole thing right here is going to get replaced with 1234 or whatever the current customer ID is.
For those of you who are bad little children and have not followed my rules from the beginning, if you have spaces in your field names, you have to remember to put brackets around this guy. But we do not because we know how to properly name our fields, we do not need to do that. Access is going to add it for us, but we do not have to.
Keep this thing in your notepad for now. Slide it off to the side.
Let us go over to the customer form now. In design view, we are just going to copy the credit limit field, copy and paste, and slide it up there. This is going to be their unpaid orders. Open up this guy's properties. Go to All. Now we are going to name the field. Name this text box unpaid orders.
Its control source is going to be that DSum that we wrote earlier. I am just going to cut it (Control X), and I am going to paste it right in here. Paste just like that.
I am going to click on it and zoom in just so I can get a better look at it. Shift-F2 zooms us in with the zoom box. Looks good. Access puts those brackets. That is fine.
Save it. Close it. Open it. There we go, unpaid orders 370. Let us double check, make sure it is correct. Yes, 370 is right. Let us go to somebody else. That is showing blank. Why is it showing blank? Let us take a look: orders. He has got an order, but it is paid, so the DSum is coming back with a null value because there are no records. That is what null means. I got nothing.
We want to handle that null condition. That is what NZ is for. So let us open this back up again. Back in here, Shift-F2 to zoom in. Now we are going to wrap this whole thing inside the NZ function. So NZ, open parenthesis, and then come down here. Normally we put a close parenthesis. But we have to put something else inside here, that second value. What do you want to make this result if DSum returns a null? In this case, zero. You can put anything in there you want. I am going to put a zero there.
Save it. Close it. Open it. There is that one. Go to the next guy. Now there is a zero there. That is better.
Now that we have the zero, we can do a little math. We can do some math. Let us copy this. Actually, first thing I am going to do is make this guy gray. I like to make fields that are calculated values gray. That way the user knows that is calculated; they cannot type anything in there.
We are going to copy this guy, copy, paste, and slide it up. Now we are going to put in here, the caption is going to be what we put in here: credit left or credit remaining or whatever you want to call it. I do not like how these borders look, so we are going to select all of these. We are going to right-click and go size to grid. I know it is off the screen, but size to grid is in there.
Open this guy up. Let us come up here. We are going to name this guy credit left or remaining or whatever you want to call it. Now this one is going to be easy. It is going to be equals the credit limit minus the unpaid orders. You get a little IntelliSense there if you see that. Credit limit minus unpaid orders. We will bring this back up like that.
Save it. Close it. Open it. There we go. There is our math. Now, let us say this guy is over his limit. Let us go into his orders and make this unpaid. He is going to be big time over his credit limit. Notice this does not automatically refresh itself. I am going to show you how to do that probably in tomorrow's video, and that does require one line of VBA code. For now, you can either hit F5 on the keyboard, or you can come up to Home and there is a Refresh option here. Refresh All refreshes all the records and will start you back on record one; Refresh just refreshes the current record. Now you can see there it is.
But someone who is not terribly familiar with your database might not realize that negative number means he is over his credit limit. So let us throw some conditional formatting in here so we can definitely tell he is over his credit limit. Let us go into Design View. I am going to click on this guy, go to Format, and then Conditional Formatting.
We are going to use three rules.
First rule: if the field value is, let us say, greater than 100, in other words, he has got at least $100 of credit left, we are going to make it green background with a white foreground and bold it.
We are going to have a warning zone. If he is over zero but less than 100, we will make it yellow. So, new rule: it is greater than zero, and we are going to make this yellow. Remember, these rules work from top to bottom and once it hits one that works, it exits out. So this one will hit, it will make it green, and then conditional formatting is done.
Lastly, if the value is less than or equal to zero, we will make it red. Now there is no mistaking it; this guy is over his credit limit. Looks good. Hit Apply. Hit OK. Save it. Close it. Open it. He is good. He is definitely not good.
Let us add or modify this guy here. Let us set the credit limit to 5000, and let us add an order in here: today's date and then stuff, and then I will put a thing in here and it is going to be $4,999. I will just test that yellow. Home, and then Refresh. There we go. Yellow, $1. Perfect.
If you like this kind of stuff, this is the kind of lesson that you will find in my expert series. Beginner is all the beginner stuff. How to make a table, how to make a simple query. The expert lessons, we go into relationships, SQL, normalization and things like this, all these extra functions. I go through all of the functions - well, I am going to say 99% of the functions in Access. There are some functions even I never use. I go through all the ones you are practically going to use. If, NZ, DLookup, DCount, all the date/time functions, you name it. I have a whole series of expert lessons just on all the functions. As a Gold member, like Miriam here, you get a free expert lesson every month.
Check it out if you are interested. You will find links for all the stuff down below.
In tomorrow's video, I am going to show you how if they come over here and make a change, let us say this guy gets paid and you close this and come back to here, this will update automatically. We will cover that in tomorrow's video.
I will also show you how if they are over their credit limit and the user clicks on the Orders button, it will say, hey, wait a minute, this guy is over his credit limit. Are you sure you want to do this? All these will require a little tiny bit of VBA programming, but not much. I call it beginner VBA - like the first couple developer lessons. I will walk you through it step by step so no one is lost. That is what I am good at.
That is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you tomorrow.
Quiz
Q1. What is the main goal described in the video regarding the customer form in Microsoft Access?
A. To display customer contact information in a user-friendly way
B. To show customers how to pay their invoices online
C. To check and display if a customer is over their credit limit before creating a new invoice
D. To automatically send overdue notices to customers
Q2. According to the tutorial, what function in Access is primarily used to calculate the total of all unpaid orders for a customer?
A. DCount
B. DMin
C. DSum
D. Avg
Q3. Which function is used to ensure that if DSum returns a null (because there are no unpaid orders), a zero is displayed instead?
A. IsNull
B. DLookup
C. Format
D. NZ
Q4. In the credit limit example, what does a negative value for "credit left" indicate?
A. The customer has enough credit for more purchases
B. The customer is under their credit limit
C. The customer has exactly met their credit limit
D. The customer is over their credit limit
Q5. What is the purpose of using conditional formatting in the "credit left" field?
A. To hide the value from users
B. To color-code the field based on how much credit is available
C. To automatically refresh the form
D. To prevent users from editing the credit limit
Q6. Which of the following is NOT required to accomplish the steps described in the video?
A. Writing custom VBA code
B. Creating an aggregate query
C. Using calculated fields on a form
D. Applying the NZ function
Q7. What is an "aggregate query" used for in the context of this tutorial?
A. To combine multiple tables into one
B. To summarize line items and group them by each order to get order totals
C. To filter out paid orders from display
D. To backup all customer data
Q8. When referencing the current customer in a DSum function on a form, what is typically used as part of the criteria?
A. The customer's name
B. The customer's phone number
C. The customer ID from the current form record
D. The credit limit value
Q9. If you have spaces in your field names in Access, what should you do in function expressions?
A. Replace spaces with underscores manually
B. Always use single quotes
C. Put brackets [ ] around the field names
D. Do nothing; it will work automatically
Q10. What must a user do to immediately refresh the displayed values on the customer form when order status changes, based on the current video?
A. Nothing, it is fully automatic
B. Use the F5 key or the Refresh option in the menu
C. Close and reopen Microsoft Access
D. Rebuild the entire form
Q11. In the video, what is a recommended visual cue for calculated fields like "unpaid orders"?
A. Making the text box background gray
B. Adding a border to the field
C. Hiding the label for clarity
D. Using italic text
Q12. What is the final calculation to determine "credit left" or "credit remaining" for a customer?
A. paid orders plus unpaid orders
B. credit limit minus unpaid orders
C. unpaid orders minus credit limit
D. DSum plus NZ
Q13. What color is recommended in the video for the "credit left" field when the customer has plenty of credit left (over 100)?
A. Red
B. Blue
C. Green
D. Orange
Q14. What will happen when the value for "credit left" is less than or equal to zero, according to conditional formatting applied in the tutorial?
A. The field will be hidden
B. The field will be colored red as a warning
C. Text will appear in bold blue
D. The form will close automatically
Q15. What might you need VBA for, as mentioned in the video, that goes beyond what is covered in this tutorial?
A. To refresh the information automatically when last order status changes
B. To sum values in a query
C. To apply conditional formatting rules
D. To use the NZ function in a control source
Answers: 1-C; 2-C; 3-D; 4-D; 5-B; 6-A; 7-B; 8-C; 9-C; 10-B; 11-A; 12-B; 13-C; 14-B; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will cover how to check if a customer is over their credit limit in a Microsoft Access database, using just built-in functions and no VBA code. This solution is ideal for those who are at that intermediate "expert" level, right between beginner and full-on developer.
The context for today's lesson comes from a common real-world scenario: you might run a small shop where regular customers can have several unpaid invoices at a time. You want to prevent adding new orders for a customer who has already reached or exceeded their credit limit. The goal is to see on the customer form how much credit a customer has remaining before issuing a new invoice, with a clear indication when the credit limit is exceeded.
The system we are using is based on my TechHelp free template, which you can download from my website. If you are unfamiliar with the invoicing example or how the customer's credit limit is set up, I recommend watching the related video tutorials that walk through the database construction process.
For this tutorial, you should already know how to add calculated fields to a form. We are going to use the DSum function to add up all unpaid invoices for the current customer. DSum is similar to DLookup, but instead of fetching a single value, it totals up a field that meets certain criteria. It is important to also know about the NZ function, which will handle situations where a customer has no unpaid invoices by converting a NULL value to a zero. Finally, we will use a bit of conditional formatting to visually distinguish customers who are over, near, or safely under their credit limit.
To recap the database setup briefly: on the customer form, there is a credit limit field, which so far has just been a static number. Customers have related orders, and each order's total is calculated based on line items. An aggregate query, called "order summary Q," is used, which brings together data from the order and order details tables, summarizes all the items per order, and provides a total. If you missed the lesson on creating this aggregate query, you can find a video about it among my resources.
Here is how you set up the available credit calculation:
Open your customer form in design view. Copy the existing credit limit field and paste a new textbox for the unpaid orders. This will show the sum of all unpaid orders for the current customer. Set the control source of this textbox to use a DSum function that adds up the "order total" from the summary query, filtered by the current customer's ID and the condition that "is paid" is false. If you have properly named your fields (without spaces), Access will handle the rest.
Once you have added this, open the form to test. You may notice that for some customers, the unpaid orders field shows blank when there are no unpaid invoices. That is because DSum will return NULL if no records meet the criteria. To address this, wrap the DSum function inside the NZ function, specifying zero as the default value. Now, whenever a customer has no unpaid orders, the field will display zero instead of blank.
Next, you can add a new textbox to calculate how much credit the customer has remaining. Name this textbox something like "Credit Left" and set its control source to subtract unpaid orders from the credit limit. I suggest making calculated fields gray, so users know they cannot edit them directly.
Test your form and check the values. If you update an order's payment status, you may need to refresh the record to see changes. At this point, the calculation works, but users might not notice when a customer is over their limit just by seeing a negative number.
This is where conditional formatting comes in. Open the design view, select the "credit left" textbox, and add three conditional formatting rules: make the background green when the value is above a certain positive threshold, yellow as a warning when close to zero, and red when zero or less. This visual cue makes it crystal clear when a customer is in good standing or over their limit.
This approach takes advantage of what Access already offers and does not require you to write any VBA code. However, if you want automated updates after making a change, or warnings when attempting to add new orders for customers over their limit, you will need to write a bit of VBA. I plan to cover beginner VBA techniques for this purpose in upcoming lessons. These techniques include refreshing calculated values automatically and warning users before adding new orders for customers who are over their credit limit.
If you enjoy this kind of lesson, it's the kind of material found in my Access Expert series. The expert lessons go well beyond beginner content, covering topics like relationships, normalization, more advanced queries, and in-depth coverage of the most useful Access functions. Gold members get access to additional content each month, including these expert lessons.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Setting up a credit limit field on the customer form
Using a query to calculate total order amounts
Creating an aggregate query for order summaries
Building a DSum function to total unpaid orders per customer
Referencing form controls in DSum criteria
Adding an unpaid orders text box to the customer form
Using the NZ function to handle null values from DSum
Calculating available credit by subtracting unpaid orders from the credit limit
Formatting calculated fields as read-only using color
Applying conditional formatting to indicate credit status
Creating rules for conditional formatting based on available credit
Article
If you want to check if a customer is over their credit limit in your Microsoft Access database, you can set up a simple system right inside your forms, without using any VBA programming. This is useful when you need to see at a glance how much credit a customer has left or if they have exceeded their allowed credit, especially before entering a new order.
First, it is important that each customer record has a credit limit field. This is typically a currency field in your Customers table or form. The next step is to calculate the total amount for all unpaid invoices to compare it with the credit limit.
Your Orders table should track whether each individual order has been paid. For this, you usually have a field like IsPaid, which is typically a Yes/No field. To sum up unpaid orders for each customer, you will need to use an aggregate query. If you have not created this yet, do so by building a query that joins your Orders and OrderDetails tables, groups by Order ID, and sums the line item totals. The query will show a single row per order with the total. Let's call this query OrderSummaryQ.
The main goal is to total up all unpaid orders for the current customer, display that amount in the Customer form, and then show how much credit is remaining by subtracting unpaid orders from the credit limit. If a customer is over their limit, you want this to be visible.
To do this without VBA, you use the DSum() function. DSum allows you to total values from another table or query, using certain criteria. You can use this directly as a Control Source for an unbound text box on your Customer form.
Here is an example of a DSum expression for this scenario:
=DSum("OrderTotal", "OrderSummaryQ", "CustomerID=" & [CustomerID] & " AND IsPaid=False")
This formula goes into the Control Source of a text box. It sums the OrderTotal field from your aggregate query OrderSummaryQ for records where the CustomerID matches the current record on your form, and IsPaid is false. Make sure that field names match your database design.
However, if there are no unpaid orders, DSum returns a Null value, which will appear as a blank in your form. To handle that, wrap the DSum expression in the NZ() function, which converts null to zero. The formula becomes:
=NZ(DSum("OrderTotal", "OrderSummaryQ", "CustomerID=" & [CustomerID] & " AND IsPaid=False"), 0)
Add a textbox to the Customer form, set its Name property to something like UnpaidOrders and set its Control Source to the formula above. Now, whenever you view a customer, this field shows their total unpaid orders.
Next, calculate the credit left. Add another text box, set its Name property to something like CreditLeft, and set its Control Source to:
=[CreditLimit] - [UnpaidOrders]
Now you have a live display of how much credit the customer has left. If they have unpaid orders that exceed their credit limit, the result will go negative.
To make this more user-friendly, you can use conditional formatting so the CreditLeft field changes color based on the value. For example, you can set it so that if the credit left is above 100, the background is green; if it is between zero and 100, it is yellow; and if it is zero or less, it turns red. This way, it is obvious when a customer is at risk of overspending their credit.
To set conditional formatting, view the form in Design View, select the CreditLeft text box, go to the Format menu, and choose Conditional Formatting. Add rules such as Value Greater Than 100 for green, Value Between 1 and 100 for yellow, and Value Less Than Or Equal To 0 for red.
This whole setup makes it easy for you or anyone using your database to see a customer's available credit and spot when they are over the limit, all without writing any VBA code. If you need the form to automatically refresh when you change data in the subforms or related tables, pressing F5 or using the Refresh command on the Ribbon will update the calculations. For a more automated solution, you can add a little VBA to refresh the form when certain actions occur, but for basic needs, this method will work just fine.
With this approach, your Access database will clearly display a customer's credit status and help prevent you from taking more orders for someone who is already over their credit limit. This technique uses only native Access functions and queries, making it accessible to anyone comfortable creating forms and calculated controls.
|
