ACCESS FEED: RE: [MS_AccessPros] How to modify this code for numeric field

Selasa, 24 Desember 2013

RE: [MS_AccessPros] How to modify this code for numeric field

Khalid,

I'm not sure how/why you changed the line of code from:

This: stLinkCriteria = "[ClientCIN]=" & SID
To: stLinkCriteria = [ClientCIN] = "& SID &"

Change it back to:
stLinkCriteria = "[ClientCIN]=" & SID

You should also change:
Dim SID As String
to:
Dim SID As Long

Also, what were the results when you set a breakpoint in the code and stepped through lines using the F8 key?

Duane Hookom MVP
MS Access

________________________________
> From: khalidtanweerburrah@yahoo.com
>
> 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