Thank you so much for uploading such tutorials, it has helped me a lot to learn so much about access and it is increasing my curiosity to learn more and more. Hats off to you sir . You are helping numerous people like me . Falling short of words.
@599CD2 жыл бұрын
Happy to hear that!
@tdp219011 ай бұрын
THANK YOU FOR THIS TUTORIAL.. I not only did the BeforeUpdate I also did the AfterUpdate to fill a number i wanted to follow... THANK YOU
@599CD11 ай бұрын
Welcome
@roodypooh5253 жыл бұрын
Simple and direct. Excellent tutorial. Thank you.
@599CD3 жыл бұрын
You're welcome!
@anandmallikarjunan77755 ай бұрын
Hello Sir! How are you? I got very useful information from this video. Have a happy life!. Thanks.
@santdnyaneshwar99204 жыл бұрын
Sir, best solution you have given, 👍👍👍👍👍👍👍👍but in case while entering data in form the next sequential number has generated as user entered data in first field and user wants to cancel current record for any other reason...by pressing ESC key... mostly it's may need while making a invoice... then the generated next number should be cancelled for that particular record and the same sequential number should be used for next invoice. If we not cancelled it by ESC key then sometimes there are chances that incomplete record may be remain saved unnecessarily and the invoice number also be utilized.... you can see this problem in this video.... while you showing demo, you have entered the only customerID and next number has generated but you kept the order amount field as "0" (empty) by skiping the order amount field. even though the whole record saved... in short we can say the FALSE record saved. so sir please give the solution to prevent from this error. Thanks...
@599CD4 жыл бұрын
I'll have to watch it again to see what you mean. Can you give me a time index?
@santdnyaneshwar99204 жыл бұрын
@@599CD Yes of course.... you have entered only customer ID at time index 7:17 and order num 112 was generated but you have not entered order amount and kept it by default as "0" as empty. like this its same happened for order no. 113 and 114 also and empty false data saved in those records. so it should be saved by clicking "save command button" only even though all the fields are filled. to prevent this there is option of data compulsory in all fields, but there may get the problem while creating other queries, forms. I hope you understand the importance of what i am trying to say. Thanks Sir you have looked in this issue.🙏
@599CD4 жыл бұрын
OK, I understand what you're trying to say. The fact that the Order Total is $0 is not the point of the video. The point is to show you how to create the sequential numbers. If you want to force the user to enter a value for that field, set a validation rule. Like I mention in the video, this is a SIMPLIFIED database for the purposes of training. Of course you'll want to make sure there is an order total (or not, a $0 invoice could be an exchange, and you still want it tracked with an Order Number). I show you how to prevent deletions so you don't have any gaps in your numbers. You'll want to add an "IsValid" field or something like that to show canceled items. But, the numbering works as shown. The ONLY problem I could see having with this the way I have it built is if you have multiple users adding records at the exact same time, and I talk about that in the Extended Cut. I certainly appreciate your feedback, and I hope I've addressed your concerns. Remember, again, this is a TEACHING database, not a real working order-entry system. You can learn more about validation rules here: kzbin.info/www/bejne/r2KndI2VrrqokMU
@ssdusd3 ай бұрын
Nice, but how can we make Sequential Number for M to M relationships Not for ID but based on the combination of FKs
@baseer437111 ай бұрын
Thanks it's also helpful like your all videos ... Thanks to come KZbin..
@599CD11 ай бұрын
Welcome
@rudraverma5970 Жыл бұрын
Dear Sir i used this method for my software but now when two front end user open new order same order number comes in both screen .how to solve it.Please suggest..
@moodkn2 жыл бұрын
Thanks..! Excellent ... Helpful
@599CD2 жыл бұрын
Always welcome
@mikea86592 жыл бұрын
Thank you Rick 👍
@599CD2 жыл бұрын
Welcome
@zuhairalmutawa99413 жыл бұрын
Sample and to the Point. Thank you
@599CD3 жыл бұрын
You're welcome!
@mysteriomagik4 жыл бұрын
Nice! This is somewhat similar to what I need.. Is it possible to have this sequential number restart from 001 every month, with the month value inserted into the sequential number as a prefix? E.g. In January it will be-- A/01/001, A/01/002, etc.. In May-- A/05/001, A/05/002, A/05/003, etc
@599CD4 жыл бұрын
Sure. In the Member's Only Extended Cut version of this video, I show how to make sequential numbers or each customer. All you would have to do is add the Month(Date()) to that number. kzbin.info/www/bejne/roqWl3qdmdaEq7c
@Akbar.Ali3002 жыл бұрын
Amazing Sir, You'r Good
@599CD2 жыл бұрын
My pleasure
@fabianvillegas63132 жыл бұрын
Gracias salió Perfect!!!
@599CD2 жыл бұрын
Thanks
@nehasrinivas71229 ай бұрын
Hi, I have 2 questions 1. I cant see the lock field in property sheet. 2. How to prevent auto sorting Eg. If I give ID 1,2,3 and if I close and reopen the table, it gives 2,1,3 Please help
@599CD9 ай бұрын
599cd.com/ask
@doborkulabb26433 жыл бұрын
Great video. I have an issue my customer wants to delete imputa like names of those who are inactive. If I put an inactive box and list all the names in a table or report, can I still have a regular nummber ordering in a sequence? Thx
@599CD3 жыл бұрын
Sure. Just have your numbering ignore inactive customers.
@afrinsultana78213 жыл бұрын
how can i control autonumber manually in Data Sheet View subform
@599CD3 жыл бұрын
You can't. Autonumbers are for internal use. They cannot be controlled. You have to create your own counter, which is what I show in the video.
@TekhXpert3 жыл бұрын
Hi Rick! Thanks for the guidance to generate custom sequential numbers in MS Access. DMAX will return the maximum number from the table, if number exist, but if not exist then at time of first record insert? You did not guide about error handling using exceptions in your VBA Code. I think the use of Nz with DMAX is necessary here or the conditional check by use of If Dmax returns null then sequential number = 1, otherwise returned number + 1
@599CD3 жыл бұрын
Yep. You are 100% right, and I have a whole separate video on NZ - 599cd.com/NZ. I can't cover everything in every video. These are quick SHORT tip videos. That's why I just seeded the numbers at 101 and moved ahead. I cover error handling and all of that fun stuff in my full courses - of which these are just a FREE PREVIEW.
@599CD3 жыл бұрын
In fact, I'm pretty sure I cover that in the Extended Cut.
@manhalalqaisy1448 Жыл бұрын
I want to delete one of the records and reorder the sequence, how can i do this ?
@599CD Жыл бұрын
599cd.com/Ask
@lamrith3 жыл бұрын
Could this same code could be placed in "Before update" event so that it only grabs a number when you go to save/update the form? I would think that could help with multi-user environments? We have a database where shop users will enter incoming work, and they can often get pulled away or not finish and entry for a number of reason. We would like to have it so the entry form does not grab a new number until they finish and save and I have been searching for a good way to do that with Access.
@599CD3 жыл бұрын
Yep. I cover that in the Extended Cut: "I'll show you how to replace NULL with Order Number values for existing records. We'll talk about problems with multi-user databases and see how to save that new number to the table immediately to avoid conflicts, and we'll learn how to make a Custom Order Number sequence for each Customer, so for example XYZ Company's invoices will be numbered: XYZ-001, XYZ-002, XYZ-003, etc. And of course we'll assign Customer Codes that are unique and alphanumeric."
@LHPottery Жыл бұрын
What version of Access are you using? There is no Before Insert in 2016.
@599CD Жыл бұрын
I'm pretty sure it's been around since at LEAST 2003... tinyurl.com/23damteb
@LHPottery Жыл бұрын
@@599CD Found it. At the form level, not control level. Gotta watch out what I'm dealing with...
@diegomarino25 Жыл бұрын
is there a way to replicate an autonumber in an insert query? i mean, adding more than one record with an incremental number?
@599CD Жыл бұрын
Nope.
@599CD Жыл бұрын
599cd.com/QQ
@Only-a-Guess3 жыл бұрын
Thank for this it has really helped me with an equipment inspection database, one question though is if I start with zero records it does not work but if I already have say a single dummy record in with the number 1 it then works fine. Is there a way around this anomaly?
@599CD3 жыл бұрын
Excellent question. Here's my answer: 599cd.com/a?34009
@Only-a-Guess3 жыл бұрын
@@599CD That worked a treat many thanks, do you do a video on filtering a combo box based on another combo box selection, what I have are 3 tables 1 for customer (location) another with customer contact persons (could be many for the same site) and these are then entered into the main table for the records of equipment inspected. What I want to do is select a customer location which then limits the customer contact persons to only people who are on that particular site then all these get entered into the main records table.
@599CD3 жыл бұрын
Yep. Those are called Cascading Combo Boxes. I have an older video on them here: 599cd.com/tips/access/two-combo-boxes - but it's funny you mention that because I'm also going to be covering them in today's TechHelp video on auto maintenance - pick a Make and then only show Models made by that auto manufacturer. Stay tuned.
@Only-a-Guess2 жыл бұрын
Hi, this has worked perfectly and I am now wanting to improve my DB and the question is I have a field in one table (via a form) using this DMax based on another table of records which gives me the next number to use and store but can it also then store this new number back to the table it used in the DMax to obtain the number in the first place. Apologies for my explanation I'm just a QA manager not a DB expert in a small company trying to improve our non conformance reporting systems. Thanks
@12onin_Gypsy3 жыл бұрын
is there a reason this doesnt work when i use order id that is a primary key? its in a form with a subform attached
@599CD3 жыл бұрын
As long as it's not an AutoNumber, it should work fine.
@bawtag3 жыл бұрын
Thank you Richard, how about in multiuser environment where multiple users are entering the data concurrently? If the record is not saved yet but the next sequential number is already assigned, the other users might have the same case in their form.
@599CD3 жыл бұрын
That could definitely be a problem. I would recommend performing a Me.Refresh immediately upon assigning the custom ID. That way the record is written to the table as quickly as possible.
@mikemellor89722 жыл бұрын
Best practice in a multiuser environment is a document master table that allocates the next number to whichever user next enters a data capture form. The problem is when a user backs out without committing the transaction/s to file. That does leave gaps in the document number sequence. There are various ways of getting around this such as each user has their own sequence e.g. JD175503 for Jane Doe, but normally those gaps are allowed to stand. There is only a gap in the transaction table, not a gap in the document master table.
@renzomorishita13302 жыл бұрын
In older versions of Access there was a feature that I could automatically number lines. For example: I started numbering the first cell with the number "1" and the following cells with "2" and "3", and Access realized that it was a pattern, and when I pressed the arrow to the next cell Access would number automatically with the number "4" and when I pressed the arrow for subsequent cells, Access automatically completed the following cells as I pressed the down arrow, and filled sequentially as far as I stopped. In the new versions of Access, 365, does not have this feature. How do I include this feature or command?
@599CD2 жыл бұрын
I don't ever remember this being a feature in Access. Are you sure you're not thinking of Excel?
@renzomorishita13302 жыл бұрын
@@599CD , yes I am sure Access has this feature. No, I'm not thinking about Excel. I have a version of Access older than Access 365 and I did this this week.
@slimanimohamedseghir3783 жыл бұрын
Hi, Richard, how do i create an ID number from information that exist in other field in sequential way? like IDnumeber = year-personalID-zipcode-numberorder
@599CD3 жыл бұрын
If it's in the same table, just concatenate it all together. X=Year(Date())&CustomerID&ZipCode&OrderID
@abdulrahmanalqahash87373 жыл бұрын
Hello there what about if there is a letter before the numbers Like F3S00101. Who to work this out to get F3S00102 automatically. Thanks
@599CD3 жыл бұрын
One of my members just asked a very similar question a few days ago. Video coming soon. Stay tuned.
@johnnyringo7911 ай бұрын
I just cant get this working, I've got the code right but nothing updates on the form when I add data
@599CD11 ай бұрын
Can't tell from here
@hwuser-gk7dt5 ай бұрын
Hi John. I am facing the same issue. Did you get it to work?
@mechtechpotato42493 жыл бұрын
Hi Richard, I'm wondering how to make a custom autonumber that relies on the prefix for the increment increase, like A1 B1 C1 A2 B2 C2 and so on. I couldn't find any videos on this, Is that part of the premium video?
@599CD3 жыл бұрын
I cover something similar in the Extended Cut: we'll learn how to make a Custom Order Number sequence for each Customer, so for example XYZ Company's invoices will be numbered: XYZ-001, XYZ-002, XYZ-003, etc. And of course we'll assign Customer Codes that are unique and alphanumeric. Not exactly what you're looking for, but close. All depends on how you derive that A, B, C, sequence.
@mechtechpotato42493 жыл бұрын
@@599CD The sequence would be based off of if the user was inputting A first then the autonumber would assign 1 to that, A1. Then A agin, the auto number would assign 2, A2. Another user inputs B and the auto number then goes B1.
@599CD3 жыл бұрын
Then yes, the Extended Cut would show how to do something very similar.
@tonychaar67803 жыл бұрын
Hi Rick, how we can solve the first record when the table has no records
@599CD3 жыл бұрын
Wrap the DMAX in an NZ function: 599cd.com/NZ
@govindbehera98973 жыл бұрын
How to count total entries in a table like we do it in query using totals..but in dis i can only count for a single field how many entries..groupby,sum,average n all..but how to count total entries of a table...hope u gt ma doubt...can u plz help me
@599CD3 жыл бұрын
Sounds like you need DCOUNT: 599cd.com/dcount
@govindbehera98973 жыл бұрын
@@599CD can we make it through query design...
@599CD3 жыл бұрын
You could try an aggregate query: 599cd.com/aggregate
@SanjayKumar-yh6il2 жыл бұрын
How to Add Serial Number e.g. 1,2,3... for each Customer ID
@599CD2 жыл бұрын
599cd.com/Counter
@Pakclub12 жыл бұрын
I need same app for android, it's possible?
@599CD2 жыл бұрын
599cd.com/AccessOnline
@mpazisimutanda39063 жыл бұрын
Sir, nice tutorial. i want help on how to increase tuitions fees every after three months for different pupil grades. for example, grade 1-5 pay $50.00 for the first term, and second term should $100.00, and third term &150.00. grade 6 - 7 pays $100.00 per term(Quarter), so second term should be $200.00 and so on. now i want after a period of three months at a given date the system automatically adds the fees for different grade e.g grade 1-5, 6-7. The system contains grades from 1 to 9 with different tuition fees. Grade 8-9 pays $200.00 per term. if there could be a way also to enter manually these fees on form and update the column with different tuition fees that also help. in short, upgrading a column with different tuition fees,
@599CD3 жыл бұрын
Should be able to do this with one or more Update Queries: kzbin.info/www/bejne/pXerdXSFoLiEZrs
@amirhosseinrezaei47233 жыл бұрын
number = DMax("number", "Table1",)+1
@599CD3 жыл бұрын
Don't forget to NZ it.
@paulatkinson90102 жыл бұрын
Thanks for the awesome tutorials Richard! I cant seem to get this to work for a new table with no existing records. It just leaves it blank. If I add a record and fill in the first number myself every number following will work. How can I get this to count right from the beginning?