Hi,
I have a table name ExpenseClaim where it has a field named RefNo to store the Primary ID for each record.
In my form, I has a combo box named cboExpenseCode: -
1) If user select "Company Reimbursed", then the prefix for the Primary ID is P-xxx
2) If user select "Project Reimbursed", then the prefix for the Primary ID is OF-xxx
xxx- represent the incremental id (e.g OF-001 , OF-002 / P-001, P-002) for the different expense code selected.
My code goes here : -
Private Sub cboExpenseCode_AfterUpdate()
-
If Me.NewRecord And Me.cboExpenseCode.Value = "Project Reimbursed" Then
-
Dim NextPRefNo As Long
-
NextPRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
-
Me![RefNo] = "P-" & Format(CStr(NextPRefNo), "000")
-
ElseIf Me.NewRecord And Me.cboExpenseCode.Value = "Company Reimbursed" Then
-
Dim NextOFRefNo As Long
-
NextOFRefNo = Nz(DMax("Right ([RefNo],3)", "ProjectExpenses"), 0) + 1
-
Me![RefNo] = "OF-" & Format(CStr(NextOFRefNo), "000")
-
End If
-
End Sub
-
But I get a problem here, the if let say the table has OF-001, the first ID value for Project Reimbursed will start with P-002. I want it to begin with P-001.
The DMax will only recognize the maximum value for the number. Can anyone please guide me how to get the output that I want? This should be easy, but I just cannot figure out the solution :(
Thank you very much.
9 3088
I found the solution at last. I created two queries (MaxPRefNo & MaxOFRefNo) to store the Max value for the Primary ID starting with prefix -> OF- and P-.
Next, I edit to the DMax function as below:
For PRefNo: -
NextPRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxPRefNo"), 0) + 1
-
For OFRefNo: -
NextOFRefNo = Nz(DMax("Right ([MaxOfID],3)", "MaxOFRefNo"), 0) + 1
-
NeoPa 32,556
Expert Mod 16PB
This has never been an easy question (You're not the first to ask it).
There is a solution however, which involves using WHERE criteria in the DMax() function : -
Private Sub cboExpenseCode_AfterUpdate()
-
Dim strExpenseCode As string, strWhere As String
-
Dim lngNextRef As Long
-
-
If Me.NewRecord Then
-
strExpenseCode = ""
-
Select Case Me.cboExpenseCode
-
Case "Project Reimbursed"
-
strExpenseCode = "P-"
-
Case "Company Reimbursed"
-
strExpenseCode = "OF-"
-
Case '... Other possibilities easily added
-
End Select
-
If strExpenseCode > "" Then
-
strWhere = "[RefNo] Like '" & strExpenseCode & "*'"
-
lngNextRef = Val(Right(Nz(DMax("[RefNo]", _
-
"ProjectExpenses", _
-
strWhere), "000"), 3)) + 1
-
Me.RefNo = strExpenseCode & Format(lngNextRef, "000")
-
End If
-
End If
-
...
-
End Sub
NeoPa 32,556
Expert Mod 16PB
NB. This concept doesn't work well in a multi-user environment, as it's possible for another user to get exactly the same value if they select the expense code after someone else does, but before they have saved it away. You may need to consider this.
A solution is to determine the value only at the time of saving the new record. This seems strange but is actually more correct than showing a value which is currently not valid. Until the record has already been saved, the value shown is only a best guess - a logical estimate of what it's likely to end up being. It can only be known after the record's saved, so it should only be shown for an existing record.
@beemomo
It could actually be done with no Queries at all: - Dim strLastP As String
-
Dim strLastOF As String
-
Dim strNextP As String
-
Dim strNextOF As String
-
-
strLastP = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'P*'")
-
strLastOF = DLast("[RefNo]", "ProjectExpenses", "[RefNo] Like 'OF*'")
-
-
strNextP = Left$(strLastP, 2) & Format$(Val(Right$(strLastP, 3)) + 1, "000")
-
strNextOF = Left$(strLastOF, 3) & Format$(Val(Right$(strLastOF, 3)) + 1, "000")
NeoPa 32,556
Expert Mod 16PB
While DLast() may work in a number of circumstances, it should not be relied upon to produce the highest number. At best it relies on the data being entered in a logical order. I would recommend using DMax().
@NeoPa
Wouldn't the Last Record be the highest ID Value (P-*/OF-*) assuming the logic is sound? If the data is not being entered in a logical order, the entries are essentially useless, correct?
NeoPa 32,556
Expert Mod 16PB
Not at all. That assumes things which are not supportable.
It assumes that : - All the data was entered via this form after it's working perfectly.
- You know the order the records are stored in the table.
- No records are added after any previous ones are deleted and the space re-used.
- There is no point where the data is moved about by processes you neither know about nor control (EG. Export to another database; do some work; import it back in unspecified, or even different order).
Mainly though, it's just the wrong function. You're not after the Last assuming it's the Max - You're after the Max.
Sorry to sound critical ADezii. I'm simply answering the question as clearly as I can. I certainly don't see it as an ambiguous point. You can often get away with DLast() (for reasons you understand well), but it's not advisable for the reasons stated.
NeoPa 32,556
Expert Mod 16PB
I was also hoping to draw the OP's attention to the style of the code in my post.
It's clear from the code which scenario is covered at any point.
It doesn't repeat code unnecessarily.
It's flexible and easily extensible.
This is not to advertise how wonderful my code is, but hopefully to get the OP to try it out and see how few extra problems occur later due to the tidier code.
Thanks to everyone for your clear explanations!
I am learning new things again. :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: amywolfie |
last post by:
Hi All:
I would like to run a report based on criteria from 3 unbound combo
boxes located on a parameter form (combo boxes are: cboCuisine,
cboLocation, and cboRestaurant)
The present code...
|
by: neptune |
last post by:
I have a query where each customer has an or . Sometimes
both fields for a customer are populated, but if is null, then
will be populated and vice versa. I have a form,
, where I select a...
|
by: Sebastien |
last post by:
Hi,
I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.
The sales stock code is a combination of letters and numbers...
|
by: misscrf |
last post by:
Ok I have 2 issues.
1) I have a main candidate form with 2 subforms on a tab control:
http://www.geocities.com/misscrf/images/contactcontinouscheckbox.jpg
I have been encouraged to add these...
|
by: anna |
last post by:
Map, generate, and maintain 50% of your .NET application code, namely
your business and data objects. Use these objects in ASP.NET, Windows
Forms, console or services applications.
Business and...
|
by: davidbennett |
last post by:
Hello,
I am new to MS Access. I have created a series of forms that users
access to perform data entry. I now need to build in reporting
functionality. I would like to create a form that has a...
|
by: AA Arens |
last post by:
Hi,
I have a database with 2 main forms. Contacts and companies. I share
the base with two others via LAN. On the companies form I have buttons
to navigate throught the records
(>400). We are...
|
by: sonic |
last post by:
I am working with a 3rd party control that does not follow the asp.net
event model. I would like to make it post to current page, and fire a
server side event.
I would like to mimic the behavior...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |