Khalid-
Oh, my! OK, CartonNo *should* be unique for Consignment and ClientCIN combination, EXCEPT when you're ordered to move cartons from one consignment to another for the same client, and that client already has cartons with the same numbers in that shipment.
How in the world do they identify the Cartons in the warehouse? How do they know they need TWO or more cartons with the same number for the same client and consignment?
Perhaps the solution is to use the Suffix for multiple purposes. If a carton contains multiple products, use -1, -2, etc. If you have to move a carton from one consignment to another, use letters. So, when you move the cartons from 2014-B-02 to 2014-A-05, the "duplicate" cartons get numbered 0001-A, 0002-A, and so on. A combination of a letter AND a number would indicate a moved carton that also contains multiple products.
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.)
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 10:20 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
One factor for duplicate CartonNo values within the displayed Consignments is that often there are more than one Client sending there consignments, so obviously there will be duplicate CartonNo values, suppose for ConsignmentNo. 2014-A-05 ClientCIN 1 delivered CartonNo 1 to 20 and ClientCIN 2 delivered CartonNo 1 to 30 then CartonNo 1 to 20 would be duplicates.
The other factor which i figured out is critical for that i am been forced by my boss, due to some reasons i am asked to shift all or certain Cartons to another Consignment.
For example: In ConsignmentNo. 2014-B-02, ClientCIN 1 delivered 50 cartons 1 - 50 and then boss says shift them to ConsignmentNo. 2014-A-05, then there will be duplicate CartonNo, , because Cartons shifted also do contain 1 - 20 same as in 2014-A-05 before, this i do manually by opening the table CollectionVoucher and changing these CartonNo 1-50 of ConsignmentNo 2014-B-02 to 2014-A-05.
The reason for editing this manually is that on the form "Collection Voucher" on control CartonNo i have a before Update event for checking of duplicate CartonNo for the same ConsignmentNo., which is:
Private Sub CartonNo_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
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.'" & [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
Hope you got my problems, at this stage i cannot change Cartons numbers which are duplicate. As many reports / documents have already been prepared signed and submitted.
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 (16) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar