Khalid-
As I recall, ClientCIN is Text (I assume CartonNo is a number), so you need:
stLinkCriteria = "[DeliveryVr]='" & [cmbDeliveryVr] & "' and CartonSuffix]='" & _
[CartonSuffix] & "' and [CartonNo]=" & [CartonNo] & " and [ClientCIN]='" & [cmbClientCIN] & _
"' and [ConsignmentNo]='" & [ConsignmentNo] & "'"
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
(Paris, France)
On Oct 21, 2014, at 1:39 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
How in the world do they identify the Cartons in the warehouse?
Your question is very logical, but we are managing it with hit & trial basis, i give the a report "Detailed Packing list All Clients" from there they check ClientCIN & CartonNo relative to him. Although it is not standard way to do the job.
Perhaps the solution is to use the Suffix for multiple purposes.
Does that work for you?
The PKey of the table would then be ClientCIN, ConsignmentNo, CartonNo, AND CartonSuffix. (Suffix would have to be text for this to work.)
The PKey of the table is now as you suggested. I used your query to check duplicate records with a slight change:
SELECT ConsignmentNo, ClientCIN, CartonNo, CartonSuffix, Count(*) AS NumDuplicates
FROM CollectionVoucher
GROUP BY ConsignmentNo, ClientCIN, CartonNo, CartonSuffix
HAVING Count(*)>1;
and changed CartonSuffix of duplicate records by giving CartonSuffix for duplicte values 1,2,3...
Default Value of CartonSuffix is set to 0 (ZERO), where ever there is no duplication CartonSuffix is 0 and it is Text.
Up to here done.
Now i am testing to enter data in form CollectionVoucher, I changed Before Update event from CartonNo to CartonSuffix and here i am getting now an error
Private Sub CartonSuffix_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
stLinkCriteria = "[DeliveryVr]='" & [cmbDeliveryVr] & "' and CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo]=" & [CartonNo] & " and [ClientCIN]=" & [cmbClientCIN] & _
" and [ConsignmentNo]='" & [ConsignmentNo] & "'"
'Check CollectionVoucher table for duplicate CartonNo
If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then <----- (THIS LINE IS HIGHLIGHTED)
'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"
'Undo duplicate entry
Me.Undo
'Go to record of original CartonNo
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Set rsc = Nothing
End If
End Sub
When i enter CartonNo = 4 and CartonSuffix remains 0 there is no error
When i enter CartonNo = 4 and CartonSuffix = 1 error displays.
What i assume is error in syntax where we write > 0, but i am not sure.
Please move me forward and help, and thanks a lot for being in touch and guiding me throughout.
Khalid
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (18) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar