User Defined Auto generate invoice number (1 Viewer)

Samadkhan · Sep 23, 2018

Hello every on,

I am the beginner of access 2013, stuck with one assignment need your wonderful help please.

My assignment is, when I start to fill the form, it should fetches the information based on data entered in some of its fields to autonumber field called InvoiceNo and result should looks alike in this style.. TSI/KHI/ABL/9999/2018

TSI <<< this data related to user field (from tblLogin)
KHI<<< it would come from field CityCode
ABL<<< it would come from another Field called Company
9999<< it should be Auto increase Number
2018<< and finally this one should be current year

Since I am beginner, its seams very difficult for me to get it done by myself .... Therefore, your wonderful help is needed and I would highly appreciate it if you help me and provide the codes.

Thank you very much in advance...

CJ_London · Sep 23, 2018

rather than create this as a single value - which will be very difficult to search on, store the values in separate fields and then concatenate them together in a query when required. Something like

Code:

InvoiceNo: [TSI] & "/" & [KHI] & "/" & [ABL] & "/" & format([IDNum],"0000") & "/" & [InvYear]

IDNum and InveYear will need to be determined at the time the record is created

InvYear would be

Code:

Year(Date())

IDNum would be something like

Code:

Dmax("IDNum","myTable","ABL='" & me.ABL & "'")+1

In any event, I would still have an autonumber primary key to make it easy to link to the invoice line records

Samadkhan · Sep 23, 2018

Dear CJ_London,

Your response is wonderful, I successfully created the query with concatenate and the result it produced is as under:

KHI/ABL/0001/2018

If feasible, please do let me know how may I apply this new query field into my form where the data could be fetched from others earlier entered fields?

Note: My form is entirely created based on table (tblOrder) and data should be stored in same table.

I will highly appreciate your generous support.

CJ_London · Sep 23, 2018

Year(Date()) should produce 2018

Implication is the code you have actually used is slightly different

If feasible, please do let me know how may I apply this new query field into my form where the data could be fetched from others earlier entered fields?

in a control on your form, in the controlsource put

=[TSI] & "/" & [KHI] & "/" & [ABL] & "/" & format([IDNum],"0000") & "/" & [InvYear]

where [TS1] etc are the names of the relevant controls. Note that while one or more of those controls are blank then you may get an error so you may need something like this

=nz([TSI]) & "/" & nz([KHI]) & "/" & nz([ABL]) & "/" & format(nz([IDNum],0),"0000") & "/" & nz([InvYear])

Samadkhan · Sep 23, 2018

Dear CJ_London,

YOU ARE AWESOME, 100000000 millions stars for you dear... I am very thankful to you and highly appreciate your swift response, you are a STAR for me.

With your wonderful and great help now I am able to submit my assignment on time... Thank you millions much... Thank you again and again...

Profound regards,

SamadKhan

CJ_London said:
Year(Date()) should produce 2018

Implication is the code you have actually used is slightly different

in a control on your form, in the controlsource put

=[TSI] & "/" & [KHI] & "/" & [ABL] & "/" & format([IDNum],"0000") & "/" & [InvYear]

where [TS1] etc are the names of the relevant controls. Note that while one or more of those controls are blank then you may get an error so you may need something like this

=nz([TSI]) & "/" & nz([KHI]) & "/" & nz([ABL]) & "/" & format(nz([IDNum],0),"0000") & "/" & nz([InvYear])

Samadkhan · Sep 23, 2018

Dear CJ_London,

Just little issue is still there, when I placed the codes in ControlSource, the result is fine, however, it's not appearing in database but in form itself only.

Please help me out I will be very thankful to you.

Profound Regards,

SamadKhan

Samadkhan said:
Dear CJ_London,

YOU ARE AWESOME, 100000000 millions stars for you dear... I am very thankful to you and highly appreciate your swift response, you are a STAR for me.

With your wonderful and great help now I am able to submit my assignment on time... Thank you millions much... Thank you again and again...

Profound regards,

SamadKhan

CJ_London · Sep 23, 2018

as explained before it is not a good idea to put this in a table. As previously advised, just create in a query as and when required.

Cronk · Sep 24, 2018

CJ

In your code

Code:

Dmax("IDNum","myTable","ABL='" & me.ABL & "'")+1

you didn't cater for a new year

Code:

nz(Dmax("IDNum","myTable","InvYear = Year() and ABL='" & me.ABL & "'"))+1

arnelgp · Sep 24, 2018

actually the code will work but will keep on generating the sequnce without resetting if the year changes.

CJ_London · Sep 24, 2018

OP didn't ask for it to reset every year

Samadkhan · Sep 24, 2018

The codes are wonderful and working according to my assignment and producing the result I was looking for .... However, the issue which remains is how I could tell the form to place the result into the table tblOrder?

In query its also working perfectly fine but my assignment is to place this new generated Invoice Number into the InvoiceNo Field associated with tblOrder table....

Your help in this regard will be highly appreciated... Thank you millions much ...

CJ_London · Sep 24, 2018

use an update query?

If by 'assignment' you mean this is homework then I really think you need to find out for yourself, getting others to do it for you isn't teaching you anything

Samadkhan · Sep 24, 2018

I understand, I should find the answer by myself but this is also the part of my assignment to use online forums to find answers when get stuck......

I did not try the update query yet but with your help I could learn more quicker then my own...

I will be very thankful in both ways either you help or just let me try to find solutions, as you already helped me a lot I almost to achieve my goal...

Thank you very much dear CJ_London

CJ_London · Sep 24, 2018

OK, so you want to learn - use the forums and find an example of what you want to do and adapt it to your requirements. If it doesn't work as you expect, come back with the code you have adapted and explain the issue.

tip: - google what you question is, it is a good starting point - and also, to avoid links to the same question with other systems, include 'access vba' in your question

Samadkhan · Sep 28, 2018

Dear Sir CJ_London,

I have created AutoNumber field [ReportNo] in same table and created a query and added the code into new expression field you provided to me earlier. Afterword, I created an update query, it is creating and updating the whole records rows.......

Is it possible to generate only one record through a form and update into table instead of many records by using update query (button which run the query)?

Your response always highly appreciated Sir...

Thanks & regards

SamadKhan

CJ_London said:
OK, so you want to learn - use the forums and find an example of what you want to do and adapt it to your requirements. If it doesn't work as you expect, come back with the code you have adapted and explain the issue.

tip: - google what you question is, it is a good starting point - and also, to avoid links to the same question with other systems, include 'access vba' in your question

CJ_London · Sep 28, 2018

Code:

Is it possible to generate only one record through a form and update into table instead of many records by using update query

you would use a criteria

Samadkhan · Sep 29, 2018

could you please let me know how? (Code to place in criteria)

CJ_London said:
Code:

Is it possible to generate only one record through a form and update into table instead of many records by using update query

you would use a criteria

CJ_London · Sep 29, 2018

I haven't seen any code yet so have no point of reference to provide an answer

User Defined Auto generate invoice number (1 Viewer)

Samadkhan

Registered User.

CJ_London

Super Moderator

Samadkhan

Registered User.

CJ_London

Super Moderator

Samadkhan

Registered User.

Samadkhan

Registered User.

CJ_London

Super Moderator

Cronk

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

CJ_London

Super Moderator

Samadkhan

Registered User.

CJ_London

Super Moderator

Samadkhan

Registered User.

CJ_London

Super Moderator

Samadkhan

Registered User.

CJ_London

Super Moderator

Samadkhan

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread