I have been using the code below that was so kindly provided to me by a superior UA user:

Private Sub dcheck_num_BeforeUpdate(Cancel As Integer)

' dimension varibles
Dim stCheck As String
Dim stAccount As String

' check there's an account number, else exit
If IsNull(Me.acc_num) Then
Exit Sub
' check there's a check number, else exit
ElseIf IsNull(Me.dcheck_num) Then
Exit Sub
End If

' assign values to our variables
stCheck = Me.dcheck_num.Value
stAccount = Me.acc_num.Value
' perform actual verification
If stCheck = DLookup("[dcheck_num]", "checks", "acc_num='" & stAccount & "'") Then
' notify user check is a dupe
MsgBox "Check number already exist in the system"
' clear all changes
Me.Undo
End If
End Sub


The code is used in a form that contains the following elements:

tied to table "checks"
fields are "acc_num" and "dcheck_num"

The code is used to make sure that two check numbers are not entered for the same account number. The code was working fine, but I realized (after a week) that it was only working on some account numbers. This is crazy to me and I have no explanation. If anyone sees a problem with this code or has alternate suggestions, please help me out, as I have no idea what to do. Thanks in advance for any help!!!!

Have you got a query on the form? When Ive had this type of problem where a piece of code is working on some records but not others, it's all come down to a slightly wrong syntax for my query.

This is probably not it, but just to be sure. . .even though you seem to be treating the acc_num as a text field, if it's actually numeric, then the account number 5 is the same thing as 05 as all leading 0's will be dropped for a numeric field.

Can you give examples of specific account numbers and check numbers where the code does not behave as you expect? Have you used the debugging features to trace through the code? You may also consider posting your DB for others to try; however, since I only have A2K, I don't believe I can look at an Access 2003 file.

Ultimately, though, why not create a unique index to prevent duplicate entries so you don't need any code at all?

Are the check numbers and account numbers Text or Number data types? No need to use variables in your code but use the control names directly. Add Cancel = True to your code before the Me.Undo.

I would put a stop in your code and step through the code as it executes and see if it is doing what you want it to do.

hth,
Jack

Jack,

I hate to post to something so far after the fact, but I got frustrated with this and let it be for a while. I actually thought the glitch had corrected itself, but it did not. I still have the same problem, my code works fine for most account numbers, but for a select few, it does not. Both the check_num and acc_num are text strings. I tried adding Cancel = True, but it didn't seem to change anything. Also, an example of an account number that might let me enter duplicate entries is: 0ck999999. An example of an account number that does work and doesn't let me enter duplicate entries is: 0ck888888. My point is, they are identical records and can both even contain the same exact information, but one will work and the other wont. I have no clue and I, as always, would be greatly appreciative of any help you can provide!!!!!!!!!!!

usf89 -

I am not sure what is going on but try the modifications in this code:

' perform actual verification
If Not IsNull(DLookup("[dcheck_num]", "checks", "[acc_num] = '" & Me![acc_num] & "'")) Then
' notify user check is a dupe
MsgBox "Check number already exist in the system"
' clear all changes
Cancel = True
Me.Undo
End If
End Sub


Note square brackets around acc_num and other code changes. A hint... Instead of using the underscore in object and field names use text like this: AccNum or CheckNum. No spaces or other characters in names as you are less likely to make mistakes if you leave out those odd characters and especially spaces. You can also put a Stop in your code (at the very beginning) and then use the Step-Into icon on the tool bar to step through your code as it executes. Placing the cursor on variables like acc_num will show the value in the variable.

If you cannot get this to work then work up a demo db and post it here and I will take a look. I will be gone most of tomorrow but I will be around on Saturday...

Jack

I think I figured out what is going on, but I don't know how to correct it. I have attached a demo db so you can see exactly what is happening. The reason it is working some times and not others is because it is not comparing the new check number with all check numbers that exist for a certain account. For example (and you can try this in the attached db). If you enter a check number of 817 to account 555555 and then try and enter another check with check number 817, it will probably catch it. What happens is if you have three or four check numbers for one account, the code might compare your newly entered check number with the first record for that account number and it will go through. It should compare the newly entered check number against all existing records for that account (check to make sure check_num doesn't equal record 1, record2, record3, ......).

Just a note on using the database, only three account numbers exist right now (0ck051161, 0ck072464, and NWA777390). Enter the account number in the text box and click on "Check Deposits." When the next screen comes up, it loads to a new entry, so go backwards to see other entries. Thanks so much for your help and drop me a note if you are having trouble with the attachment. Thanks.

-Casey

Thank you for the demo as it let me see what you are up to... Try this in your code:



You can get rid of your variables as you do not need them.

You can used this code to verify data entry into your two controls:



I strongly suggest that you use standard naming conventions for your objects, etc.

I hope this solves your problem for you....

Jack

Jack,

I'm sorry it has taken me so long to thank you for all of the help. The code seems to work great now and is not doing what it was doing before. Thanks a million for all of your help, I greatly appreciate it!!

-Casey

Casey -

No problem! I am glad you have the problem solved and that things are working as they should. Continued success with your project...

Jack