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

Sabtu, 01 Februari 2014

RE: [MS_AccessPros] Getting selected parts of a String

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