BroncoMarc
Registered User.
- Local time
- Today, 15:25
- Joined
- Apr 4, 2002
- Messages
- 43
I need my database to create a custom report number for sumbitting samples. The standard that they use is YY-MM-## where ## is a sequential number based on how many samples have been submitted that month.
For example, The first sample submitted this month is 03-09-01, next is 03-09-02, and so on. They don't exceed 99 samples per month.
I've got the first part of the number created.. That was easy. (see code below) That creates "03-09-"
How do I make it search thru existing numbers and pick the next one? Note that not every record in the database will have a number and they are not necessarily in order.
Should I make some kind of loop from 99 down to 00 checking if the number exists and if it does use the next one? If so, how do I code that? Or is there a better way?
What I have so far:
Private Sub btnFindARNum_Click()
Dim MyString
MyString = Mid(Year(Date), 3, 2) & "-" & Mid(Date$, 1, 3)
txtARNumber.Value = MyString
End Sub
Thanks,
- Marc
For example, The first sample submitted this month is 03-09-01, next is 03-09-02, and so on. They don't exceed 99 samples per month.
I've got the first part of the number created.. That was easy. (see code below) That creates "03-09-"
How do I make it search thru existing numbers and pick the next one? Note that not every record in the database will have a number and they are not necessarily in order.
Should I make some kind of loop from 99 down to 00 checking if the number exists and if it does use the next one? If so, how do I code that? Or is there a better way?
What I have so far:
Private Sub btnFindARNum_Click()
Dim MyString
MyString = Mid(Year(Date), 3, 2) & "-" & Mid(Date$, 1, 3)
txtARNumber.Value = MyString
End Sub
Thanks,
- Marc
