Viewing duplicate entry

[puppydogbuddy]

change line 4 of your code to this:

Dim lngFilter As Integer


If the above does not work, change line 4 to this:

Dim lngFilter As Variant

[ashenton]

I tried the first change and got the same type mismatch error as before.

When I tried the second change I got another 2498 error in

DoCmd.OpenForm Me, , , lngFilter

:(

[puppydogbuddy]

Ok, I did some research to get the proper syntax. This should work.

Private Sub DAMS_Number_AfterUpdate()  
  
Dim tmpID As Long  
Dim strFilter As String  
  
tmpID = Nz(DLookup("[DAMS Number]", "DAMS Stock", "[DAMS Number]=" & Me!DAMS_Number), 0)  
If tmpID <> 0 Then  
MsgBox "This DAMS asset already exists in the database", vbOKOnly  
strFilter = "[DAMS Number] = " & tmpID  
DoCmd.OpenForm Me, , , strFilter  
End If  
  
End Sub

_______________ _______________ _______________ _____________

If you still get a type mismatch and the data type for [DAMS NUMBER] is integer, change this line:

Dim tmpID As Long

To this:

Dim tmpID As Integer

[ashenton]

Hi again,

Sorry for the late reply, I been off sick.

I tried the code. With 'tmpID As Long' I get the same 2498 error. When I change the line to Integer I get a nic error!

Run-time error '6'

Overflow

Any ideas? lol

[ashenton]

Hi again,

Sorry for the late reply, I been off sick.

I tried the code. With 'tmpID As Long' I get the same 2498 error. When I change the line to Integer I get a nic error!

Run-time error '6'

Overflow

VB shows the error in line

tmpID = Nz(DLookup("[DAMS Number]", "DAMS Stock", "[DAMS Number]=" & Me!DAMS_Number), 0)

Any ideas? lol

[ashenton]

By the way, the DAMS Number field in the table is set to text.

[puppydogbuddy]

By the way, the DAMS Number field in the table is set to text.

Hope you are feeling better. If the DAMS number is text, that changes everything.

Private Sub DAMS_Number_AfterUpdate()   
  
Dim tmpID As Long
Dim strDams As String   
Dim strFilter As String   

  
tmpID = Nz(DLookup("[DAMS Number]", "DAMS Stock", "[DAMS Number]=" & Me!DAMS_Number), 0)   
If tmpID <> 0 Then   
strDams = Me!DAMS_Number
MsgBox "This DAMS asset already exists in the database", vbOKOnly   
strFilter = "[DAMS Number] = '" & strDams & "'"   
DoCmd.OpenForm Me, , , strFilter   
End If  
End Sub

If the above does not work, change this:
Dim strDams As String

To:
Dim strDams As Variant

[ashenton]

Nope, that doesn't work either. It comes up with another 2498 error which VB attributes to

DoCmd.OpenForm Me, , , strFilter

:(

[puppydogbuddy]

Rather than go back and forth, could you attach a test copy of your database in Access version 2000 format so I can look at it?

[ashenton]

Hi,

I don't seem to be able to attach anything. There is no browse button at the bottom of the message edit box :(

[puppydogbuddy]

Hi,

I don't seem to be able to attach anything. There is no browse button at the bottom of the message edit box :(

Hi,

Immediately after you submit your post, bring it back up on the screen. If you look at the bottom of the screen, click on the word "edit", and you will be in the message editor. Enter "attachment " as the reason for editing your post, then go to the bottom of your screen. There you will see a button for managing attachments. Click it and you will see a browse button and an upload button. Click the browse button to select the file, then click the upload button.

[ashenton]

I've found where you mean now, thanks for that :)

I'll have to do the upload from home as it's been blocked by the corporate network lol

[ashenton]

Here is the attachment, sorry it's taken so long!

[puppydogbuddy]

Got your file. Am out of the office. Will get back to you on or before Monday.

[ashenton]

Thank you very much :)