Primary Key

**mshmyob** · Mar 26 '08, 11:34 AM

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,

Originally posted by 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

**sajitk** · Mar 26 '08, 11:40 AM

Thanks mshmyob,

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

Thanks
Sajit

Originally posted by 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,

**mshmyob** · Mar 26 '08, 06:48 PM

ON ERROR events are pretty straight forward.

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

[code=vba]
'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 = acDataErrContin ue
Case 0
'do nothing all is well
Case 3401
LResponse = MsgBox("SSN required", vbOKOnly, errCase)
Response = acDataErrContin ue
Case 3058 ' index key blank
LResponse = MsgBox("SSN required", vbOKOnly, errCase)
Response = acDataErrContin ue
Case 3022 ' duplicate index key
LResponse = MsgBox("SSN already exists.", vbOKOnly, errCase)
Response = acDataErrContin ue
Case Else
MsgBox "Error number " & Err.Number & ": " & Err.Description
Response = acdatadisplay
End Select
[/code]

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,

Originally posted by sajitk

Thanks mshmyob,

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

Thanks
Sajit

**sajitk** · Mar 27 '08, 11:39 AM

Code:

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

Originally posted by 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:

[code=vba]
'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 = acDataErrContin ue
Case 0
'do nothing all is well
Case 3401
LResponse = MsgBox("SSN required", vbOKOnly, errCase)
Response = acDataErrContin ue
Case 3058 ' index key blank
LResponse = MsgBox("SSN required", vbOKOnly, errCase)
Response = acDataErrContin ue
Case 3022 ' duplicate index key
LResponse = MsgBox("SSN already exists.", vbOKOnly, errCase)
Response = acDataErrContin ue
Case Else
MsgBox "Error number " & Err.Number & ": " & Err.Description
Response = acdatadisplay
End Select
[/code]

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** · Mar 27 '08, 05:24 PM

Yep you can do that also.

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

[code=vba]
' 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.SetFo cus
Else
' everyhting OK - do nothing
End If
[/code]

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

cheers,

Originally posted by sajitk

Code:

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** · Mar 28 '08, 05:08 AM

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

Originally posted by mshmyob

Yep you can do that also.

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

[code=vba]
' 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.SetFo cus
Else
' everyhting OK - do nothing
End If
[/code]

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

cheers,

**sajitk** · Mar 28 '08, 07:19 AM

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

Sajit

Originally posted by 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

Primary Key

Primary Key

Comment

Comment

Comment

Comment

Comment

Comment

Comment