Solved Envelope Number Assignment in unbound form field (1 Viewer)

Fahad987

New member
Local time
Today, 12:16
Joined
Dec 15, 2021
Messages
11
Hi all,

I have created an unbound form in Access wherein I enter customer names, cities and invoice numbers being sent to them. i also have current date field and customerID fields on the form. Every thing on my form works as per my requirement and without any errors, except for one - the EnvelopeID generation. In the underlying table, EnvelopeID has a data type of "number".

When i open the form today for the very first time, it should automatically assign a new EnvelopeID after checking the largest EnvelopeID number already present in the table. From hereon, there can be multiple invoices being sent to a single customer today, resulting in multiple line entries for that customer in the form for each unique invoice number. However, i want the EnvelopeID to remain the same every time the same customer is entered, but to increment by 1 when i enter a new customers' name.

I am so far thinking about inserting code for this in the "OnLostFocus" event of customer names combo box, so that as soon as i select customer's name and move on to the next entry field, the EnvelopeID code runs and populates the EnvelopeID textbox field. But i am stuck with writing the code for it. I have attached an example Excel file for this purpose.

I'd be grateful for all the help offered.
 

Attachments

  • EnvelopeID Example.pdf
    196.3 KB · Views: 329

oleronesoftwares

Passionate Learner
Local time
Today, 00:16
Joined
Sep 22, 2014
Messages
1,159
Hi @Fahad987 , you can try the following.

first to make the envelope id increment by one,if its the beginning of a new day you can use dmax function to compare the current date e.g

If date()>DMax("[SampleDate]","TableA") // this is to confirm if todays date is greater than/ after the last date in your records

second you need another condition in the if statement to check if the record for the customer is the first record for today

to do this, you can have a combo box that selects the customer name and the last date attached to the customer e.g
SELECT TableA.Customer, TableA.SampleDate
FROM TableA
ORDER BY TableA.[SampleDate] DESC;

You will bind to column 1, but show the two columns, i.e column count property will be set to 2.

Next you can now reference the second column(which is the last date) and place it as the second condition in the if statement.


The if statement will run after update event of the customer name.

Note// a group by clause might be necessary in the select statement above , that is group by TableA.Customer


hope this helps.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:16
Joined
May 7, 2009
Messages
19,169
see this demo.
see the code on the CustID, AfterUpdate event.
 

Attachments

  • envelop.accdb
    496 KB · Views: 307

Fahad987

New member
Local time
Today, 12:16
Joined
Dec 15, 2021
Messages
11
see this demo.
see the code on the CustID, AfterUpdate event.
Thank you indeed. However, I would like to have better understanding of this code. Can you explain its working to me please, specially the inner DMax function line? Also, what is the benefit of using DBEngine.Idle here?

I am also facing an unpleasant situation regarding a totally different issue in MS Access. should i post it here within this thread or open a new thread entry? It is related to group policy permissions i.e. the .mdw file.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:16
Joined
May 7, 2009
Messages
19,169
create new thread for it (.mdw).
this is for older database (2003 and below).

you can delete the dbEngine.Idle (that was just a test).

1. the first Dmax() is the usual, get the Last EnvelopID (then increment by 1).
2. next DMax() is to get the EnvelopID for customer on the Current date.
if there already exists an EnvelopID, use it.
otherwise use the EnvelopID we get on step 1.
 

Users who are viewing this thread

Top Bottom