Hi Graham,
Now the present shape of code is as follows, and i am getting this error:
Run-time error '2465':
PCTL can't find the field'|' referred to in your expression.
and while on clicking 'Debug' button, i see in VBA code
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"
with Yellow back color highlighted
And when i changed & [ClientCIN] & to & [cmbClientCIN] &
This line is highlighting with Yellow back color
If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then
------------------------------------------
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
'Message box warning of duplication
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"
'Undo duplicate entry
Me.Undo
'Go to record of original CartonNo
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
On Monday, February 3, 2014 2:39 PM, Graham Mandeno <graham@mandeno.com> wrote:
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
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 (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar