ACCESS FEED: Re: [MS_AccessPros] Getting selected parts of a String

Minggu, 02 Februari 2014

Re: [MS_AccessPros] Getting selected parts of a String

Khalid-

That looks like the value for [ClientCIN] is Null. Are you sure it has a value? Is ClientCIN referencing a control on your form? In your previous code, you referenced cmbClientCIN - maybe that's what you should be using.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 2, 2014, at 10:33 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi Graham,
Thanks for your detailed reply with explanations.
I did changed as you mentioned:
Msgbox
If DCount
stLinkCriteria

I am checking it for ClientCIN=44, ConsignmentNo=2014-A-01 and DeliveryVr=013, CartonNo=6
Now getting Run time error '3075':
syntax error (missing operator) in query expression '[DeliveryVr]='013'
and [CartonNo]=6 and [ClientCIN]= and
[ConsignmentNo]='2014-A-01".

You can see in error message there is no value against [ClientCIN]=

Regards,
Khalid

On Sunday, February 2, 2014 12:32 AM, Graham Mandeno <graham@mandeno.com> wrote:

Hi Khalid

You are taking a whole lot of knows "pieces" and gluing them together into a string. Then, when you want the individual pieces back, you are trying to "unglue" them from the string.

Better to use the original values:

        MsgBox "This Carton Number: " & [CartonNo] & " has already been allotted" & vbCrLf & _
              "in Consignment No.'" & [ConsignmentNumber] & "'," & vbCrLf & _
             "vide Contract No.'" & [cmbDeliveryVr] & "' for Client CIN: " & [ClientCIN] & "." _
              , vbInformation, "PCTL - Duplicate Entry"

You would also be better to do a DCount( "*" ) instead of making DCount do the extra concatenation:

        If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then

Also, your filter string cannot distinguish between Carton 1 for Client 12 and Carton 11 for Client 2 (and similar situations with other field values). It would be better (and much faster) to filter on the individual fields using AND:

        stlinkCriteria = "[DeliveryVr]='" & [cmbDeliveryVr] & "' and [CartonNo]=" & [CartonNo] & _
                    " and [ClientCIN]=" & [ClientCIN] & " and [ConsignmentNo]='" & [ConsignmentNo] & "'"

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Sunday, 2 February 2014 08:04
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] Getting selected parts of a String

Hi all,
I have a subform on its control "CartonNo" i have following code:

Private Sub CartonNo_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = [cmbDeliveryVr] & [CartonNo] & [cmbClientCIN] & [ConsignmentNo]
stLinkCriteria = "[DeliveryVr] & [CartonNo] & [ClientCIN] & [ConsignmentNo]=" & "'" & SID & "'"

'Check CollectionVoucher table for duplicate CartonNo
If DCount("[DeliveryVr] & [CartonNo] & [ClientCIN] & [ConsignmentNo]", "CollectionVoucher", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "This Carton Number has already been allotted" & vbCrLf & _
"in Consignment No.'" & Right(SID, 9) & "', vide Contract No.'" & Left(SID, 3) & "'." _
, vbInformation, "PCTL - Duplicate Entry"

'Go to record of original CartonNo
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
--------------------
My MsgBox is giving correct message. But i want to display message as:

This Carton Number: '000' has already been allotted in Consignment No.'0000-A-00'
vide Contract No.'000' for Client CIN: 000

"CartonNo" is number field and may start from 1 and go to maximum of 3 digits
"ConsignmentNo" Text field, will always be 9 characters
Contract No is Text field "DeliveryVr" 3 characters
"ClientCIN" is number field and may start from 1 and go to maximum of 3 digits

I have tried with Left, Ltrim in different variations but could not exact results may be i am missing some part or not putting correct criteria.

Could anybody help.

Khalid