How to better handle duplicate entry error

[krowe]

Hi

I have a number of indexed (No Duplicate) fields in my table as I want to enforce a 1-1 relationship in the data. They all lookup values in other tables.

When I user tries to put a duplicate entry in the database I'd like a message to pop up saying 'This entry is already associated with Ref xxx, did you want to move it to the current ref' Yes , No

Then if they click yes I want to remove the existing value from the table and insert it into the current record. If no, I want it to revert back to the original value.

Is this possible? If so how can I achieve this?

Thanks

Kev

 

[namliam]

They all lookup values in other tables.

I hope you are not actually doing lookups inside your tables?

Is this possible? If so how can I achieve this?

Yes possible but not with the default things that access offers you.
Instead you have to create your own check for duplicate entries (Maybe using a DLookup) and handle it the way you want it handled.

 

[krowe]

Hi

I have done lookups in my table, I know this is frowned upon, although I must admit I don't know why...

I have come up with this code, looks a bit tatty, but seems to be doing the trick, wonder is there is a way to simplify it

Private Sub Device_BeforeUpdate(Cancel As Integer)
Dim ExistingDevice As String
Dim CountOfDevice As String
Dim ExistingJoin As String
Dim ExistingUser As String
Dim ExistingUserName As String
 
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
 'check if duplicate value
 CountOfDevice = DCount("[Device]", "tblJoin", "[Device] = " & Me!Device)
 If CountOfDevice > 0 Then
     'retrive existing device in tblJoin
    ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & Me!JoinID)
      
    'check if chosen device is current device
    If ExistingDevice <> Me!Device Then
    
        
        'retrieve the UserID who has device already
        ExistingUser = DLookup("[User]", "tblJoin", "[Device] = " & Me!Device)
        'retrieve the existing username
        ExistingUserName = DLookup("[UserName]", "tblUser", "[UserID] = " & ExistingUser)
        'retrive current username
        CurrentUsername = DLookup("[UserName]", "tblUser", "[UserID] = " & Me!User)
       
        Msg = "This device currently belongs to " & ExistingUserName & ". Do you wish to transfer the device to " & CurrentUsername
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Title = "Duplicate warning"
        Help = "DEMO.HLP"
        Ctxt = 1000
         Response = MsgBox(Msg, Style, Title, Help, Ctxt)
         If Response = vbYes Then
        
            'retrieve the DeviceID for duplicate value
            ExistingJoin = DLookup("[JoinID]", "tblJoin", "[Device] = " & Me!Device)
            
            'delete Device from existing User
            strUpdateQuery = "UPDATE tblJoin SET Device = Null WHERE [JoinID] = ExistingJoin"
        
            'set device for current user
            strUpdateQuery = "UPDATE tblJoin SET Device = Me!Device WHERE [JoinID] = Me!JoinID"
        
            Else
            
            Cancel = True
        
        End If
         
     Else
     
     strUpdateQuery = "UPDATE tblJoin SET Device = Me!Device WHERE [JoinID] = Me!JoinID"
     End If
     
     
Else
 strUpdateQuery = "UPDATE tblJoin SET Device = Me!Device WHERE [JoinID] = Me!JoinID"
 End If

Thanks

Kev

 

[krowe]

Just realised my code doesn't work if I delete the device.. think I may need another if to account for this....

The Set Device = Null isn't working either

 

[namliam]

Lookups belong on forms and reports not in tables...
More here: http://access.mvps.org/access/lookupfields.htm

You cannot execute queries like this:
strUpdateQuery = "UPDATE tblJoin SET Device = Me!Device WHERE [JoinID] = Me!JoinID"

You need to substitute in the variable fields like you do in your msg
Msg = "This device currently belongs to " & ExistingUserName & ". Do you wish to transfer the device to " & CurrentUsername

 

[krowe]

Hi

Thanks for your help so far, I think I have got a little further, but getting errors with this:

Private Sub Device_BeforeUpdate(Cancel As Integer)
Dim ExistingDevice As String
Dim CountOfDevice As String
Dim ExistingJoin As String
Dim ExistingUser As String
Dim ExistingUserName As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
 'check for deletion
If IsNull(Me![Device]) Then
    
    UpdateSql = "UPDATE tblJoin"
    SetSql = "Set Device = Null"
    WhereSql = "Where [JoinID] = Me!JoinID"
      
    strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
    DoCmd.RunSQL (strsql)
    
    Else
    
    'check if duplicate value
     CountOfDevice = DCount("[Device]", "tblJoin", "[Device] = " & Me!Device)
     If CountOfDevice > 0 Then
            
        'retrive existing device in tblJoin
        ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & Me!JoinID)
      
        'check if chosen device is current device
        If ExistingDevice <> Me!Device Then
    
        
            'retrieve the UserID who has device already
            ExistingUser = DLookup("[User]", "tblJoin", "[Device] = " & Me!Device)
            'retrieve the existing username
            ExistingUserName = DLookup("[UserName]", "tblUser", "[UserID] = " & ExistingUser)
            'retrive current username
            CurrentUsername = DLookup("[UserName]", "tblUser", "[UserID] = " & Me!User)
       
            Msg = "This device currently belongs to " & ExistingUserName & ". Do you wish to transfer the device to " & CurrentUsername
            Style = vbYesNo + vbCritical + vbDefaultButton2
            Title = "Duplicate warning"
            Help = "DEMO.HLP"
            Ctxt = 1000
             Response = MsgBox(Msg, Style, Title, Help, Ctxt)
             If Response = vbYes Then
        
                'retrieve the DeviceID for duplicate value
                ExistingJoin = DLookup("[JoinID]", "tblJoin", "[Device] = " & Me!Device)
            
                'delete Device from existing User
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = Null"
                WhereSql = "Where [JoinID] = ExistingJoin"
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
                      
                'check new value in table
                ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & ExistingJoin)
                                
                'set device for current user
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = Me!Device"
                WhereSql = "Where [JoinID] = Me!JoinID"
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
            
                'audit here
                Else
            
                Cancel = True
        
            End If
         
        Else
        UpdateSql = "UPDATE tblJoin"
        SetSql = "Set Device = Me!Device"
        WhereSql = "Where [JoinID] = Me!JoinID"
      
        strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
        DoCmd.RunSQL (strsql)
        
        End If
     
     
    Else
         UpdateSql = "UPDATE tblJoin"
        SetSql = "Set Device = Me!Device"
        WhereSql = "Where [JoinID] = Me!JoinID"
      
        strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
        DoCmd.RunSQL (strsql)
    'audit here
    End If
 End If
     
 End Sub

it askes for parameters for me!device and me!JoinID

They are both controls on the form the code runs on.

Please can you advise?

Thanks

Kev

 

[krowe]

have just tried this too, but getting same errors:

Private Sub Device_BeforeUpdate(Cancel As Integer)
Dim ExistingDevice As String
Dim CountOfDevice As String
Dim ExistingJoin As String
Dim ExistingUser As String
Dim ExistingUserName As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
 'check for deletion
If IsNull(Me![Device]) Then
    
    UpdateSql = "UPDATE tblJoin"
    SetSql = "Set Device = Null"
    WhereSql = "Where [JoinID] = " & Me!JoinID
      
    strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
    DoCmd.RunSQL (strsql)
    
    Else
    
    'check if duplicate value
     CountOfDevice = DCount("[Device]", "tblJoin", "[Device] = " & Me!Device)
     If CountOfDevice > 0 Then
            
        'retrive existing device in tblJoin
        ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & Me!JoinID)
      
        'check if chosen device is current device
        If ExistingDevice <> Me!Device Then
    
        
            'retrieve the UserID who has device already
            ExistingUser = DLookup("[User]", "tblJoin", "[Device] = " & Me!Device)
            'retrieve the existing username
            ExistingUserName = DLookup("[UserName]", "tblUser", "[UserID] = " & ExistingUser)
            'retrive current username
            CurrentUsername = DLookup("[UserName]", "tblUser", "[UserID] = " & Me!User)
       
            Msg = "This device currently belongs to " & ExistingUserName & ". Do you wish to transfer the device to " & CurrentUsername
            Style = vbYesNo + vbCritical + vbDefaultButton2
            Title = "Duplicate warning"
            Help = "DEMO.HLP"
            Ctxt = 1000
             Response = MsgBox(Msg, Style, Title, Help, Ctxt)
             If Response = vbYes Then
        
                'retrieve the DeviceID for duplicate value
                ExistingJoin = DLookup("[JoinID]", "tblJoin", "[Device] = " & Me!Device)
            
                'delete Device from existing User
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = Null"
                WhereSql = "Where " & [JoinID] & "= ExistingJoin"
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
                      
                'check new value in table
                ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & ExistingJoin)
                                
                'set device for current user
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = Me!Device"
                WhereSql = "Where " & [JoinID] & " = Me!JoinID"
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
            
                'audit here
                Else
            
                Cancel = True
        
            End If
         
        Else
        UpdateSql = "UPDATE tblJoin"
        SetSql = "Set Device = Me!Device"
        WhereSql = "Where " & [JoinID] & "= Me!JoinID"
      
        strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
        DoCmd.RunSQL (strsql)
        
        End If
     
     
    Else
         UpdateSql = "UPDATE tblJoin"
        SetSql = "Set Device = Me!Device"
        WhereSql = "Where " & [JoinID] & "= Me!JoinID"
      
        strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
        DoCmd.RunSQL (strsql)
    'audit here
    End If
 End If
     
 End Sub

 

[namliam]

Anything that is a variabele Cannot be inside quotes

Anthony starting with me or existingjoin etc must be The same way as you do it at tha dlookups

 

[krowe]

Hi I think I'm getting closer, just having some difficulties with my if, then elses.

this is where I'm up to but I get a else without if compile error (in red)

Dim ExistingDevice As String
Dim CountOfDevice As String
Dim ExistingJoin As String
Dim ExistingUser As String
Dim ExistingUserName As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim UpdateSql As String
Dim SetSql As String
Dim WhereSql As String
 'if a straight deletion then
    'run update to Null
'else
    'check for duplicate values
    'if duplicate then
        'find duplicate value in table
        'display confirmation msg yes/no
        'if yes button then
            'make existing value null
            'update current record
        'else (no button)
            'cancel=true
        'end if
    'else (not duplicate)
        'run update query
    'end if
'end if
    
    
 
'check for deletion if so update table with Null value
If IsNull(Me![Device]) Then
    
    UpdateSql = "UPDATE tblJoin"
    SetSql = " Set Device = Null"
    WhereSql = "Where " & "[JoinID] = " & Me!JoinID
      
    strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
    DoCmd.RunSQL (strsql)
    
Else
    
    'check if duplicate value
     CountOfDevice = DCount("[Device]", "tblJoin", "[Device] = " & Me!Device)
     If CountOfDevice > 0 Then
    
        'see if existing user is current user
        
        'retrieve userID of existing user
        ExistingUser = DLookup("[User]", "tblJoin", "[Device] = " & Me!Device)
        
        If ExistingUser = Me!User Then
        
            'run update sql
            
            UpdateSql = "UPDATE tblJoin"
            SetSql = "Set Device = " & Me!Device
            WhereSql = "Where " & "[JoinID] = " & Me!JoinID
      
            strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
            DoCmd.RunSQL (strsql)
            
        Else
        
            'delete device from existing user and move to current user
            'retrieve the UserID who has device already
            ExistingUser = DLookup("[User]", "tblJoin", "[Device] = " & Me!Device)
            'retrieve the existing username
            ExistingUserName = DLookup("[UserName]", "tblUser", "[UserID] = " & ExistingUser)
            'retrive current username
            currentusername = DLookup("[UserName]", "tblUser", "[UserID] = " & Me!User)
       
            Msg = "This device currently belongs to " & ExistingUserName & ". Do you wish to transfer the device to " & currentusername
            Style = vbYesNo + vbCritical + vbDefaultButton2
            Title = "Duplicate warning"
            Help = "DEMO.HLP"
            Ctxt = 1000
             Response = MsgBox(Msg, Style, Title, Help, Ctxt)
             If Response = vbYes Then
        
                'retrieve the DeviceID for duplicate value
                ExistingJoin = DLookup("[JoinID]", "tblJoin", "[Device] = " & Me!Device)
            
                'delete Device from existing User
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = " & Null
                WhereSql = "Where " & "[JoinID] = " & ExistingJoin
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
                      
                'check new value in table
                ExistingDevice = DLookup("[Device]", "tblJoin", "[JoinID] = " & ExistingJoin)
                                
                'set device for current user
                UpdateSql = "UPDATE tblJoin"
                SetSql = "Set Device = " & Me!Device
                WhereSql = "Where " & "[JoinID] = " & Me!JoinID
      
                strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
                DoCmd.RunSQL (strsql)
            
                'audit here
            Else
            
                Cancel = True
        
            End If
            
  [COLOR=red]  Else
[/COLOR]              
        UpdateSql = "UPDATE tblJoin"
        SetSql = "Set Device = " & Me!Device
        WhereSql = "Where " & "[JoinID] = " & Me!JoinID
      
        strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
        DoCmd.RunSQL (strsql)
        
    End If
        
    'UpdateSql = "UPDATE tblJoin"
    'SetSql = "Set Device = " & Me!Device
    'WhereSql = "Where " & "[JoinID] = " & Me!JoinID
      
    'strsql = UpdateSql & vbCrLf & SetSql & vbCrLf & WhereSql
    'DoCmd.RunSQL (strsql)
    'audit here
End If

Please can anyone see where i've gone wrong?

Thanks

Kev

 

[vbaInet]

Anthony starting with me...

Is Anthony one of your pals Mailman?

@Krowe - snippet:

dim intResponse as integer

if not isnull(dlookup(...fill this in krowe...)) then
    intResponse = msgbox("...your message here krowe...")

    if intresponse = vbyes then
        ' do something here
    else
        ' do something here
    end if
end if

 

[krowe]

Hi

That's simplified things a lot, thanks for that steer.

I have changed it into that format, now I'm back to the errors in the query syntax, specifically the one to make the existing device null, before putting the device against the current record.

I have attached the DB, hopefully it will make it easier to diagnose.

I am part way through removing the table lookups too

The Form that is causing me problems is frmMain

Thanks for all your help so far

Kev

 

[vbaInet]

... specifically the one to make the existing device null, before putting the device against the current record.

You need to further explain this part.

And how do I replicate the problem in your db?

 

[krowe]

Sorry

In frmMain, try changing Device2 to Device1 (on the first record), then click yes to move the device to the current user, it gets stuck when it tries to remove the device from the previous user.


Thanks

Kev

 

[vbaInet]

Your form isn't very intuitive so I still don't know what to do. Give me steps on how to change the devices.

 

[krowe]

Sorry,

It does need to tidying up and instruction...

frmMain is just joining User to a Device and a Sim, so if you change the control cboDeviceID from Device 2 to Device 1 you should get the error (the erroneous code is on the before update of this control).

Thanks

Kev

 

[vbaInet]

Here:

SetSql = "Set DeviceID = Null "

By the way, why aren't you doing it in a query?

 

[krowe]

what do you mean?

 

[vbaInet]

Have you made the adjustment and re-tested the code?

What I mean is create the parameters in an UPDATE query and run the query in code instead of building the SQL in code.

 

[krowe]

The fix worked great, thanks!

I will have a look at using a query, I suppose the advantage is I wouldn't have to do the lookups as the tables would all be in the query...

Wish i'd though of that!!!

 

[krowe]

still... I've learned a lot about write a query in vba today at least!