Primary Key

[sajitk]

Hi Friends,

I have a table called Beneficiary where [Solar Lantern No] is the primary key. The user enter the [Solar Lantern No] number. Since the user enters the [SLNO], there is always a possibility that a duplicate value is entered. After entering the data, when the user saves the record, it should check whether the particular SLNo is already entered or not. if yes, then it should display a message box, else record saved.

By default there is a system generated message box.

looking fwd to hear from one of you.

Thanks
SAjit

[mshmyob]

I think I am missing something here. If it is the PK then duplicates are not alowed and you will therfore not be able to save it when you try and a Access message will popup anyways. You do not have to do any checking for duplicate.

You may want (and I would) just create an ON ERROR event to trap the error and display your own message.

cheers,

Hi Friends,

I have a table called Beneficiary where [Solar Lantern No] is the primary key. The user enter the [Solar Lantern No] number. Since the user enters the [SLNO], there is always a possibility that a duplicate value is entered. After entering the data, when the user saves the record, it should check whether the particular SLNo is already entered or not. if yes, then it should display a message box, else record saved.

By default there is a system generated message box.

looking fwd to hear from one of you.

Thanks
SAjit

[sajitk]

Thanks mshmyob,

Would be great if you could help me in writing this On ERROR Event..

Thanks
Sajit

I think I am missing something here. If it is the PK then duplicates are not alowed and you will therfore not be able to save it when you try and a Access message will popup anyways. You do not have to do any checking for duplicate.

You may want (and I would) just create an ON ERROR event to trap the error and display your own message.

cheers,

[mshmyob]

ON ERROR events are pretty straight forward.

In your form in the On Error event you might put some code that looks like this:

'If an error occurs because of missing data in a required field
'display our own custom error message
 
    ' Display error information.
        errCase = DataErr
 
        Select Case errCase
        Case 3314 ' missing required field value
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case 0
            'do nothing all is well
        Case 3401
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
Case 3058 ' index key blank
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case 3022 ' duplicate index key
            LResponse = MsgBox("SSN already exists.", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case Else
            MsgBox "Error number " & Err.Number & ": " & Err.Description
            Response = acdatadisplay
        End Select
 

Any erros you haven't trapped in any code will 'jump' down to here. For instance in your case if a person puts in an existing SSN into the PK the CASE 3022 will be triggered (error # 3022 is duplicate PK) and it will indicate that in a nice error message.

Notice error #3314 and 3401 will display the same message because some error numbers get triggered depending on how your design is.

Obviously you can build on this.

This is only one way. You could also create private onerror trapping. This way works quite nice for errors you don't think of ahead of time.

cheers,

Thanks mshmyob,

Would be great if you could help me in writing this On ERROR Event..

Thanks
Sajit

[sajitk]

thanks mshmyob, 
 
this works. 
 
I was just thinking if there is a way, the moment the user enters the SSN or solar Lantern no and moves for the next entry, the system should check whether that particular SSN or Solar Lantern no is there in the table or not. if yes, it should prompt a msgbox and the focus should shift back to the SSN textbox. 
 
would be great if you can help me in coding this also. 
Thanks
Sajit 

ON ERROR events are pretty straight forward.

In your form in the On Error event you might put some code that looks like this:

'If an error occurs because of missing data in a required field
'display our own custom error message
 
    ' Display error information.
        errCase = DataErr
 
        Select Case errCase
        Case 3314 ' missing required field value
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case 0
            'do nothing all is well
        Case 3401
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
Case 3058 ' index key blank
            LResponse = MsgBox("SSN required", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case 3022 ' duplicate index key
            LResponse = MsgBox("SSN already exists.", vbOKOnly, errCase)
            Response = acDataErrContinue
        Case Else
            MsgBox "Error number " & Err.Number & ": " & Err.Description
            Response = acdatadisplay
        End Select
 

Any erros you haven't trapped in any code will 'jump' down to here. For instance in your case if a person puts in an existing SSN into the PK the CASE 3022 will be triggered (error # 3022 is duplicate PK) and it will indicate that in a nice error message.

Notice error #3314 and 3401 will display the same message because some error numbers get triggered depending on how your design is.

Obviously you can build on this.

This is only one way. You could also create private onerror trapping. This way works quite nice for errors you don't think of ahead of time.

cheers,

[mshmyob]

Yep you can do that also.

Put the following code in the 'After Update' event of the text box you want to check.

' this will receive a TRUE or FALSE depending if the entry is found
Dim vCheckEntry As Variant
 
' use DLOOKUP method to look for your SSN    
vCheckEntry = DLookup("[SSN_ID]", "tblSSN", "[SSN_ID] = '" & Me.txtSSN & "'")
        If vCheckEntry = True Then
            MsgBox ("SSN already exists.  Please change it.")
            Me.txtSSN.SetFocus
        Else
            ' everyhting OK - do nothing
        End If
 

By the way don't put your message text in '[code]' brackets. It is hard to read.

cheers,

thanks mshmyob, 
 
this works. 
 
I was just thinking if there is a way, the moment the user enters the SSN or solar Lantern no and moves for the next entry, the system should check whether that particular SSN or Solar Lantern no is there in the table or not. if yes, it should prompt a msgbox and the focus should shift back to the SSN textbox. 
 
would be great if you can help me in coding this also. 
Thanks
Sajit 

[sajitk]

Thank you...

it works... the only change which i need to do was to change the variable type from Variant to Boolean.

Thanks once again

Sajit

Yep you can do that also.

Put the following code in the 'After Update' event of the text box you want to check.

' this will receive a TRUE or FALSE depending if the entry is found
Dim vCheckEntry As Variant
 
' use DLOOKUP method to look for your SSN    
vCheckEntry = DLookup("[SSN_ID]", "tblSSN", "[SSN_ID] = '" & Me.txtSSN & "'")
        If vCheckEntry = True Then
            MsgBox ("SSN already exists.  Please change it.")
            Me.txtSSN.SetFocus
        Else
            ' everyhting OK - do nothing
        End If
 

By the way don't put your message text in '[code]' brackets. It is hard to read.

cheers,

[sajitk]

Which part of the world are you from.....??? Myself from India....

Sajit

Thank you...

it works... the only change which i need to do was to change the variable type from Variant to Boolean.

Thanks once again

Sajit