Duane,
Yes ClientCIN is numeric. Now with the following code i do not get any error, but for duplicate ClientCIN i am not getting Message box warning of duplication and i move to the next field.
Private Sub ClientCIN_BeforeUpdate(Cancel As Integer)
CmdUndoAddRcrdClient.Enabled = False
CmdCloseAddRcrdClient.Enabled = True
CmdAddRcrdClient.Enabled = True
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.ClientCIN.Value
stLinkCriteria = [ClientCIN] = "& SID &"
'Check Clients table for duplicate ClientCIN
If DCount(ClientCIN, "Clients", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Client CIN: '" & SID & "' has already been allotted." & vbCrLf & _
"You will now been taken to the record of Client CIN. '" _
& SID & "'.", vbExclamation _
, "PCTL Accounts - Duplicate Entry"
'Go to record of original ClientCIN
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
Does this matters for numeric:
Dim SID As String <---------------- (Should it be As Integer)
Dim stLinkCriteria As String <-------------(Should it be As Integer)
OR is it OK .
The link you provided is not opening at my end at the moment, may be i can open it aftwards then i would for sure read it.
Khalid
On Tuesday, December 24, 2013 8:59 PM, Duane Hookom <duanehookom@hotmail.com> wrote:
Khalid,
If ClientCIN is numeric then John's suggestion should work exactly as stated. There shouldn't be any issue with the DCount() statement.
When you state "but got errors", you need to tell us which line generates the error and provide the error message. We can't see your screen or your data.
Learn how to debug/troubleshoot be using breakpoints and debug.print statements in your code. Check out http://tek-tips.com/faqs.cfm?fid=7148
Duane Hookom MVP
MS Access
________________________________
> From: khalidtanweerburrah@yahoo.com
>
> John,
>
> I tried it before but got errors, Same now.
> And what about quotes and SID after [ClientCIN]=
> stLinkCriteria = "[ClientCIN]=" & "'" & SID & "'"
>
> What about quotes here:
> 'Check Clients table for duplicate ClientCIN
> If DCount("ClientCIN", "Clients", _
> stLinkCriteria)> 0 Then
> 'Undo duplicate entry
> Me.Undo
>
> Khalid
>
>
> On Tuesday, December 24, 2013 6:19 PM, John Viescas <JohnV@msn.com> wrote:
>
> Kahlid-
>
> I would guess that all you need to do is remove the quotes around this:
>
> stLinkCriteria = "[ClientCIN]=" & SID
>
>
> John Viescas, Author
>
> On Dec 24, 2013, at 11:42 AM, Khalid Tanweer
> <khalidtanweerburrah@yahoo.com<mailto:khalidtanweerburrah@yahoo.com>>
> wrote:
>
> Hi All,
> Could somebody help me to modify the following code for a numeric
> field, the current code is for a text field.
>
> Private Sub ClientCIN_BeforeUpdate(Cancel As Integer)
>
> CmdUndoAddRcrdClient.Enabled = False
> CmdCloseAddRcrdClient.Enabled = True
> CmdAddRcrdClient.Enabled = True
>
> Dim SID As String
> Dim stLinkCriteria As String
> Dim rsc As DAO.Recordset
>
> Set rsc = Me.RecordsetClone
>
> SID = Me.ClientCIN.Value
> stLinkCriteria = "[ClientCIN]=" & "'" & SID & "'"
>
> 'Check Clients table for duplicate ClientCIN
> If DCount("ClientCIN", "Clients", _
> stLinkCriteria)> 0 Then
> 'Undo duplicate entry
> Me.Undo
> 'Message box warning of duplication
> MsgBox "Client CIN: '" & SID & "' has already been allotted." &
> vbCrLf & _
> "You will now been taken to the record of Client CIN. '" _
> & SID & "'.", vbExclamation _
> , "PCTL Accounts - Duplicate Entry"
> 'Go to record of original ClientCIN
> rsc.FindFirst stLinkCriteria
> Me.Bookmark = rsc.Bookmark
> End If
> Set rsc = Nothing
> End Sub
>
> Khalid
> Kh
If ClientCIN is numeric then John's suggestion should work exactly as stated. There shouldn't be any issue with the DCount() statement.
When you state "but got errors", you need to tell us which line generates the error and provide the error message. We can't see your screen or your data.
Learn how to debug/troubleshoot be using breakpoints and debug.print statements in your code. Check out http://tek-tips.com/faqs.cfm?fid=7148
Duane Hookom MVP
MS Access
________________________________
> From: khalidtanweerburrah@yahoo.com
>
> John,
>
> I tried it before but got errors, Same now.
> And what about quotes and SID after [ClientCIN]=
> stLinkCriteria = "[ClientCIN]=" & "'" & SID & "'"
>
> What about quotes here:
> 'Check Clients table for duplicate ClientCIN
> If DCount("ClientCIN", "Clients", _
> stLinkCriteria)> 0 Then
> 'Undo duplicate entry
> Me.Undo
>
> Khalid
>
>
> On Tuesday, December 24, 2013 6:19 PM, John Viescas <JohnV@msn.com> wrote:
>
> Kahlid-
>
> I would guess that all you need to do is remove the quotes around this:
>
> stLinkCriteria = "[ClientCIN]=" & SID
>
>
> John Viescas, Author
>
> On Dec 24, 2013, at 11:42 AM, Khalid Tanweer
> <khalidtanweerburrah@yahoo.com<mailto:khalidtanweerburrah@yahoo.com>>
> wrote:
>
> Hi All,
> Could somebody help me to modify the following code for a numeric
> field, the current code is for a text field.
>
> Private Sub ClientCIN_BeforeUpdate(Cancel As Integer)
>
> CmdUndoAddRcrdClient.Enabled = False
> CmdCloseAddRcrdClient.Enabled = True
> CmdAddRcrdClient.Enabled = True
>
> Dim SID As String
> Dim stLinkCriteria As String
> Dim rsc As DAO.Recordset
>
> Set rsc = Me.RecordsetClone
>
> SID = Me.ClientCIN.Value
> stLinkCriteria = "[ClientCIN]=" & "'" & SID & "'"
>
> 'Check Clients table for duplicate ClientCIN
> If DCount("ClientCIN", "Clients", _
> stLinkCriteria)> 0 Then
> 'Undo duplicate entry
> Me.Undo
> 'Message box warning of duplication
> MsgBox "Client CIN: '" & SID & "' has already been allotted." &
> vbCrLf & _
> "You will now been taken to the record of Client CIN. '" _
> & SID & "'.", vbExclamation _
> , "PCTL Accounts - Duplicate Entry"
> 'Go to record of original ClientCIN
> rsc.FindFirst stLinkCriteria
> Me.Bookmark = rsc.Bookmark
> End If
> Set rsc = Nothing
> End Sub
>
> Khalid
> Kh
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar