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

Minggu, 02 Februari 2014

RE: [MS_AccessPros] Getting selected parts of a String

Hi Khalid

This is called DEBUGGING J

We set the value of stLinkCriteria like this:

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

If you were to stop the code by setting a breakpoint after that statement, and then examine the value of stlinkCriteria in the Immediate Window, you would see this:

?stLinkCriteria
[DeliveryVr]='013' and [CartonNo]=6 and [ClientCIN]= and [ConsignmentNo]='2014-A-01'

Clearly a value is missing after the third "=", so [ClientCIN] has no value.

Looking back at your original post, I see that the name of the control is actually [cmbClientCIN], so we have made a mistake in the assignment statement for stLinkCriteria. It should be:

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

With a bit more confidence and practice, you will be comfortable doing this kind of debugging yourself.

The worrying thing is that your code did not complain about an undeclared variable ClientCIN. This probably indicates that you do not have Option Explicit at the top of your module. This option should be at the top of every module because it prohibits you from using undeclared variables, and means that many problems will be discovered and fixed immediately, instead of waiting for strange results to occur at run-time.

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 22:33
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Getting selected parts of a String

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