Excellent!!! Keep going! And thank you for this video. Very helpful for me!!
@EdcelleJohnGulfan Жыл бұрын
Thanks Don.
@iammai5404 Жыл бұрын
Great tutorial, very helpful and very clever.
@EdcelleJohnGulfan Жыл бұрын
Thanks for watching
@56cridwan6 ай бұрын
thanks for the video, very helpful
@EdcelleJohnGulfan6 ай бұрын
Thanks for watching
@bck45892 жыл бұрын
Thank you sir,❤️ from India
@EdcelleJohnGulfan2 жыл бұрын
Welcome
@boodygarcia58742 жыл бұрын
Thank you you Sir Edcelle!
@EdcelleJohnGulfan2 жыл бұрын
Welcome and thanks for watching Boody
@sheikhobaba11 ай бұрын
Great
@bajront6611 ай бұрын
Greetings. Sir, you explained this really professionally, but I can't do what I need and that's why I need your help. Namely, the Invoice number must be reset annually, but it should contain the first and last letter of the month, then the Invoice number and at the end the year, for example: JY-1/2021, JY-2/2021; FY-3/2021, FY-4/2021, FY-5/2021; MH-6/2021 etc. JY-1/2022, JY-2/2022; FY-3/2022... Can you help me with this please?
@EdcelleJohnGulfan11 ай бұрын
Actually same procedure but you have to add the LEFT and RIGHT FUNCTION. It's a complex process so i will create a video about this soon.
@patriknemec62536 ай бұрын
Hi Mr Gulfan, what is VBA Code when I delete last invoice and then create new with the same number. For example I delete invoice 2023002 and then create new with number 2023002. 🙏
@EdcelleJohnGulfan6 ай бұрын
Why not delete only the records inside that invoice?
@japmanumbali80012 жыл бұрын
How to create invoicenumber and seriesnumber in to 4 digits like 0001. Thanks
@EdcelleJohnGulfan2 жыл бұрын
Try this. kzbin.info/www/bejne/q6mbeqGagtp0bK8
@murealnoauy4 ай бұрын
From the example you did, it was very good. But I would like to add the ID number to 0001, 0002. What should I do? 2023-04-1 to 2023-04-0001 2023-04-2 to 2023-04-0002 2022-01-1 to 2022-01-0001
@TheOrangeGecko2 ай бұрын
Change the line "Me.SeriesNumber = InvNext" to Me.SeriesNumber = Format(InvNext, "0000")
@amrelnaggar4509 Жыл бұрын
I need your help, if I have a table with data entry form. This table contains fields (Order date, Total cost, first payment, second payment, Rest and paid date). Rest= Total cost - first payment - second payment. what I need is to have the paid date (now date when the order is paid) filled automatically under only two conditions, when the Total cost > 0 and Rest =0 (which means that the order is paid). Also how to prevent order modification after it is paid. Would you help me with that , please.
@naughtynaughty6453 Жыл бұрын
Hi Sir, I am currently working on a project similar to this, it's just that I'm using ms excel as my front-end and ms access as the back-end. My challenge is that there are multiple users simultaneously creating record/tickets. Do you have any suggestions how it can be implemented? Thank you.
@EdcelleJohnGulfan Жыл бұрын
Multiple-user at the same time in excel is not doable. Either you work in a Read Only Mode or the one who can save record is the last user. Why not use Access instead of excel?
@naughtynaughty6453 Жыл бұрын
@@EdcelleJohnGulfan I am not used to in ms access forms...all my previous projects are in excel - access set up since it is much more like the old VB6.0 and ms access tandem. I was able to create something but it's a hit and miss. I have 4 users creating tickets simultaneously. We are all trying to click the command button at the same time and after a couple of attempts (5 to 6 entries each users) one would encounter an error with the database connection which I couldn't properly troubleshoot...To give you a background the functions invoked when the command button is clicked: 1.) It will make a query to retrieve the max id (auto_number primary key) and the max series (indexed - no duplicate) and store them in variables: max_id and max_series 2.) It will insert a record just to create an initial entry to the table in the database and to determine the order of the auto_number generated. This is my work-around for the multi-user simultaneously creating record. 3.) It will query the last record created and then compare it to the max id. Ex. the max_id is 10 and the last id stored in step 2 is 14 then it means I'll be needing to add 4 in my step 4 to generate the ticket number based on the max_series. 4.) Update the series by adding the difference of the last id stored and the max id to the max_series... Not sure if I explained it clearly. But like I said it's a hit and miss. I know it's not the best approach and I'm still trying to make a work around and hopefully I sort it out.
@patriknemec625311 ай бұрын
Hi Mr Gulfan, I would like to set my invoice number like 2023001, …and I do not know hot to set three numbers at the end of invoice number. Now I have 20231,…numbering. I have tried your another video. But it does not work. Please help. 🙏🙏 Many thanks.
@EdcelleJohnGulfan11 ай бұрын
Have you seen this video? kzbin.info/www/bejne/q6mbeqGagtp0bK8si=thKrnyQWNLVynxwu Well, if not, watch the video and apply this format. sample: Me.Invoice_Number= Format(Date, "yyyy") & Format(Your_autonumber, "000")
@eyi49110 ай бұрын
hello, I tried this but unfortunately it stops at 10...error after 10 input datas...may I ask why? Can you please check the vba, Thank you
@EdcelleJohnGulfanАй бұрын
I have mistake in my coding. 1:14 series number, the data type must be Number not a short text.
@toutypasse44000 Жыл бұрын
salut je suis content de la solution
@EdcelleJohnGulfan Жыл бұрын
Thanks for watching.
@seshadrisrikant2 жыл бұрын
When i am running the form for the new year its showing invalid use of null (Error 94) . How to solve this
@EdcelleJohnGulfan2 жыл бұрын
If That's the error, you are using or referring the null or empty field. To fixed it. Put NZ FUNCTION to the code that has an error.
@salmaabakil83622 жыл бұрын
sir i have a problem i did the same thing that you did but when the number When it reaches the number 1 0, it will repeat the same number 9/2022 10/2022 10/2022 10/2022 can you please help me
@EdcelleJohnGulfan2 жыл бұрын
Review your criteria in Dmax or Dlast function. If it is null then your number will revert to 1. You can create a temporary variable so that you will know the value of your Criteria.
@EdcelleJohnGulfanАй бұрын
I have mistake in my coding. 1:14 series number, the data type must be Number not a short text.
@admiralrichvi2 жыл бұрын
How to make it reset each fiscal year ?
@EdcelleJohnGulfan2 жыл бұрын
Hi thanks for asking. Same procedure but you need to set up first your fiscal date as your variable (September + Year)
@admiralrichvi11 ай бұрын
If you going to use max ID for the series number, how to rest the numbering for the new fiscal? I have 101-2023, combined series with year.@@EdcelleJohnGulfan
@moyal34552 жыл бұрын
This method does not work when records go above 10. System error that value has been duplicated.
@EdcelleJohnGulfan2 жыл бұрын
Thanks for the info but im using this since 2013 and i don't have problem. You can avoid the duplicate values if you combine the year+month+series number.
@moyal34552 жыл бұрын
@@EdcelleJohnGulfan This actually resolved the error: vlast = DMax("Val([SeriesNumber])", "invoice", "InvYear='" & Me!invyear.Value & "'").
@admiralrichvi2 жыл бұрын
This happening to me as well
@MaraingMuong168 Жыл бұрын
@@moyal3455 Thank you very much thank you
@NoumanAzam01 Жыл бұрын
@@moyal3455 So great of u Moya L
@kidsworld7459 Жыл бұрын
Hi sir, I want to create unique index number for customers. I want to get first letter of Coustomer name with number such as A001, A002, B001, C001, C002 etc. Please help me.
@elvesreis2235 Жыл бұрын
I tried this but it does not increment :Private Sub Form_BeforeInsert(Cancel As Integer) Dim Vlast As Variant Dim InvNext As Integer Me.Invyear = Format(Date, " YYYY ") & "-" & Format(Date, "mm") Vlast = DMax("SeriesNumber", "Invoice ", " InvYear =' " & Me.Invyear.Value & "'") If IsNull(Vlast) Then InvNext = 1 Else InvNext = Vlast + 1 End If Me.SeriesNumber = InvNext End Sub
@EdcelleJohnGulfan Жыл бұрын
Send me your file and I'll to check.
@kiranarana7274 Жыл бұрын
Please help I get aan Syntax error (missing operator) in query expression InvYear=*2023* Private Sub Form_BeforeInsert(Cancel As Integer) Dim vLast As Variant Dim InvNext As Integer Me.InvYear = Format(Date, "YYYY") vLast = DMax("SeriesNumber", "Invoice", "InvYear=*" & Me.InvYear.Value & "*") If IsNull(vLast) Then InvNext = 1 Else InvNext = vLast + 1 End If Me.SeriesNumber = InvNext End Sub