Adding additional sequential number to avoid duplicate value (1 Viewer)

[leahcorp]

Hello,

I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days. Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.

For example, we take “1791” as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table “tblRDEF”(record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to “1791-1” since “1791” already exists in the database. When we get the next file, the number should change to “1791-2” and then “1791-3” and on.

Any help with this is appreciated.

Thank you

 

[Rx_]

Assuming the 1791 is known to be unique. It is the version number (-1, -2, -3, ...) that you need to find the last one, and increment it by one. Lets call the two fields [Key1], [Key2] while the - is just a formatting character.
If a Query had [Key1] & [key2] (must use & not + in case of null) with a filter of [Key1] - in Descending Order. A Top 1 Query would return the last [Key2]
Then in the AddNew record - take the value of [Key2] and increment it by one.

 

[billmeye]

In the After Update event for RDEFNumber you could run the test and change the value.

Dim GetCon As Integer
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber ],4)='" & Left(Me.RDEFNumber , 4) & "'")
If Me.NewRecord Then
    If GetCon > 0 Then Me.RDEFNumber = Left(Me.RDEFNumber , 4) & "-" & GetCon
Else
'you need to decide what to do if something is changing
End If

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=143776
This is a very basic demo (example) - gets the next available number on different tables.
Just for some ideas.

 

[leahcorp]

In the After Update event for RDEFNumber you could run the test and change the value.

Dim GetCon As Integer
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber ],4)='" & Left(Me.RDEFNumber , 4) & "'")
If Me.NewRecord Then
    If GetCon > 0 Then Me.RDEFNumber = Left(Me.RDEFNumber , 4) & "-" & GetCon
Else
'you need to decide what to do if something is changing
End If

This is exactly what I was looking for. Thank you so much for your help. You are the best!

 

[leahcorp]

Thank you all for the quick responses!!

 

[billmeye]

Glad I could help.

 

[leahcorp]

In the After Update event for RDEFNumber you could run the test and change the value.

Dim GetCon As Integer
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber ],4)='" & Left(Me.RDEFNumber , 4) & "'")
If Me.NewRecord Then
    If GetCon > 0 Then Me.RDEFNumber = Left(Me.RDEFNumber , 4) & "-" & GetCon
Else
'you need to decide what to do if something is changing
End If

Hello,

Thank you again for your help. I have 2 little problems. First, in the actual database the RDEFNumber has 5 digits and the code above automatically chops the last digit with "-". I believe the code above is for 4 digit numbers. Is it possible not to limit this? the second, I am getting error 3085 for "Left" function. I have added VBA.Left to resolve this. It works for the VBA.Left(Me.RDEFNUmber, 4) but not for the VBA.Left[RDEFNumber, 4].

Thank you

 

[billmeye]

You can replace the code with this and there is no issue with the Left function:

Dim GetCon As Integer, DetMain as String, MainLen as Integer
If Instr(Me.RDEFNumber,"-") Then 
DetMain = Mid(Me.RDEFNumber,1,instr(1,Me.RDEFNumber,"-")-1)
MainLen = Len(DetMain)
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber]," & MainLen & ")='" & DetMain & "'")
Else
DetMain = Me.RDEFNumber
MainLen = Len(DetMain)
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber]," & MainLen & ")='" & Me.RDEFNumber & "'")
End If

If Me.NewRecord Then
     If GetCon > 0 Then Me.RDEFNumber = DetMain & "-" & GetCon
Else
'you need to decide what to do if something is changing
End If

This is able to handle any length RDEFNumber.

 

[leahcorp]

Hello,
I am still getting the same Run-time error 3085 with the message "Undefined function 'Left' in expression." in the line below.

GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber]," & MainLen & ")='" & DetMain & "'")
Else
DetMain = Me.RDEFNumber
MainLen = Len(DetMain)
GetCon = DCount("*", "tblYourTable", "Left([RDEFNumber]," & MainLen & ")='" & Me.RDEFNumber & "'")
End If

Just to test the code, I created a quick database and used your code and it works fine without any problem. However, when I move the same thing to my existing database, I get the same error. Is it related to some reference library issue?

Thank you for your help

 

[leahcorp]

I just figured out that a reference Library called "Reflection for IBM 7.00 Object Library" is creating the issue. If I uncheck it, I don't get any error message. However, the other form that works with reflection stops working. So, I need that library checked.

Please let me know if anyone has a work around with the "Left Function".
Much Appreciated!! Thank you!!

 

[billmeye]

That can be a problem with libraries that conflict. You can try Left$ or Vba.Left or Vba.Left$.

 

[leahcorp]

Hello,
I tried Left$, VBA.Left but it did not work. It worked for form field names but not for table field name. I have found a work around by unchecking the reflection library before running your code above and checking it back after execution via vba code. It may not be an ideal way but it works for me.

Thank you again for all your help!

 

[billmeye]

Maybe you can start another thread with that conflict and see if anyone can help.