RE: [MS_AccessPros] Getting selected parts of a String

 

Hi Khalid

I think the problem is that you are performing the Me.Undo (which is presumably erasing the values from [CartonNo] and [ConsignmentNo]) before displaying the MsgBox.  Try moving that line so it is after the MsgBox.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Monday, 3 February 2014 18:10
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Getting selected parts of a String

 
Hi Graham,
Well i put Option Explicit in the beginning of module.
Now a situation has arose like this that in message box result we are not getting value of CartonNo and Consignment and getting values for cmbDeliveryVr and cmbClientCIN only.

BTW before receiving your this reply i did some hit and trials and it did worked perfectly with some changes on my original code which i posted initially. By defining two variables RID and CID as string Just view it and please suggest me that is this a right approach and method OR we should rectify the current code you gave.

This is the code below which we are working now:

   Dim SID As String
   Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = [cmbDeliveryVr] & [CartonNo] & [cmbClientCIN] & [ConsignmentNo]
    stLinkCriteria = "[DeliveryVr]='" & [cmbDeliveryVr] & "' and [CartonNo]=" & [CartonNo] & _
    " and [ClientCIN]=" & [cmbClientCIN] & " and [ConsignmentNo]='" & [ConsignmentNo] & "'"
    'Check CollectionVoucher table for duplicate CartonNo
    If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then
    'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "This Carton Number: " & [CartonNo] & " has already been allotted" & vbCrLf & _
              "in Consignment No.'" & [ConsignmentNo] & "'," & vbCrLf & _
              "vide Contract No.'" & [cmbDeliveryVr] & "' for Client CIN: " & [cmbClientCIN] & "." _
              , vbInformation, "PCTL - Duplicate Entry"
        'Go to record of original CartonNo
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing
-------------------------

And this is the code which i was just playing and got all output on message box correctly with no errors.

    Dim SID As String
    Dim RID As String
    Dim CID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = [cmbDeliveryVr] & [CartonNo] & [cmbClientCIN] & [ConsignmentNo]
    RID = [CartonNo]
    CID = [cmbClientCIN]
    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 "Carton Number '" & RID & "' has already been entered for Client CIN '" & CID & "'" & 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

Best regards,
Khalid


On Sunday, February 2, 2014 3:26 PM, Graham Mandeno <graham@mandeno.com> wrote:
 
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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (8)

Recent Activity:

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___