Rabu, 30 Agustus 2017

Re: [MS_AccessPros] Report to show specific Client Name

 

Khalid-


We agreed a long time ago that the Primary Key would be:

ConsignmentNo
ClientCIN
CartonNo
CartonSuffix

That means you CANNOT insert a row that duplicates a combination of values that already exist in another row.  That's what your code validates.  Why would you want to create a duplicate CartonNo / CartonSuffix on a ConsignmentNo for the same ClientCIN even though the name has changed?  If it's actually a new client, then that new client should have its own ClientCIN.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 30, 2017, at 5:34 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

So now in this new situation, when we have added field "NameOfClient" in table "CollectionVoucher" and resolved the requirement of user to get "NameOfClient" on the report, with all that work on Dialog form and slight change in a text box on report to get "NameOfClient".

How can we now resolve this issue in data entry of form ? I'm sure with your deep consideration we will be able to resolve it also.

Thanks in advance.
Regards,
Khalid


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

As I suspected, your code is correct.  Even if you didn't make the check, the table's Primary Key would disallow storing another row with the same ConsignmentNo, ClientCIN, CartonNo, and CartonSuffix.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 30, 2017, at 12:00 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

I replied last night, i don't know why it is sent.

Yes table "CollectionVoucher" has PrimaryKey and it was set by you 2-3 years before. I am already feeding duplicate CartonNo in the same ConsignmentNo for same ClientCIN but with different "CartonSuffix"...also other ClientCIN can have same CartonNo in same ConsignmentNo

This all was managed with your help & support, now it is difficult for me to explain all that, perhaps it will make you more confused.

PrimaryKey ConsignmentNo
ClientCIN
CartonNo
CartonSuffix

Primary Yes
Unique Yes
Ignore Nulls No

Regards,
Khalid




---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

That doesn't make sense.  What is the Primary Key of the underlying table?  If ConsignmentNo / CartonNo aren't the Primary Key, do you at least have a Unique index defined?  There should not be a duplicate carton number in a given consignment.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 29, 2017, at 9:54 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

This was old criteria for the previous settings when we have not added field "NameOfClient" and were looking for duplicate CatonNo for ClientCIN and ConsignmentNo

Now i am saying that "ConsignmentNo" is same and "ClientCIN" is also same, but "ClientName" has been changed. In this scenario criteria should also be changed.

For e.g. if there is one entry in 
ConsignmentNo  ClientCIN ClientName CartonNo
2017-B-01 29 ABC 1

We are entering data in same ConsignmentNo, same ClientCIN, but ClientName has been changed and his first entry is CartonNo 1

2017-B-01 29 XYZ 1

Now No message for duplicate CartonNo should pop up, but if CartonNo 1 is entered again for:
2017-B-01 29 XYZ 
We should get Msg for duplicate CartonNo

This was my question. Please look into it.

Regards,
Khalid



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Your criteria are:

    stLinkCriteria = "[CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo] =" & [CartonNo] & _

    " and [ClientCIN]=" & [cmbClientCIN] & " and [ConsignmentNo]='" & [ConsignmentNo] & "'"

.. and then you look to see if there's a row that matches.  As long as you have entered a different (new) carton number for this row, there should be no match.  You're saying that this is a new carton on a given consignment for a client whose name has changed.  You should not duplicate a previous carton number.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 29, 2017, at 9:23 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Yes of course, two Clients cannot have the same ClientCIN.

What we have worked up to now and is OK for report is as below:

There was a Client with "ClientCIN"= 29 having "ClientName" = "ABC"

Then
This ClientCIN 29, ClientName is changed to "XYZ"

And we need to enter new cargo collection entries for new/changed/re-named ClientName "XYZ" with the same ClientCIN 29, although there have been entries in this particular Consignment with ClientCIN 29 and ClientName = "ABC" for CartonNo 1 to 10, now we should be able to enter any CartonNo between 1 to 10 in the same ConsignmentNo for ClientCIN 29 and ClientName "XYZ"

Regards,
Khalid


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

How can two clients have the same ClientCIN?  That shouldn't be possible.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 29, 2017, at 6:04 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Wow! that is marvelous.

It is DONE.

One thing i did, i removed "NameOfClient" from cmbConsignmentNo. If i put "NameOfCient" also, then list was showing all "NameOfClient" with same ConsignmentNo.

The report is now OK by removing "NameOfClient" from cmbConsignmentNo.
-------------------------------------------------------------------------

John, one more favor is required, in data entry of form "NewCargoCollectionInputsubform" for new "New/ changed ClientName it is displaying Msgbox for CartonNo previously entered for other ClientName having same ClientCIN. I have tried with different ways but could not mange it. Below is the code i had before.

Private Sub CartonNo_BeforeUpdate(Cancel As Integer)

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    ' Don't check if not on a new row

    If Not Me.NewRecord Then Exit Sub

    stLinkCriteria = "[CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo] =" & [CartonNo] & _

    " and [ClientCIN]=" & [cmbClientCIN] & " and [ConsignmentNo]='" & [ConsignmentNo] & "'"

                     Debug.Print stLinkCriteria

    

   'Check CollectionVoucher table for duplicate CartonNo

    If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then

 

        'Message box warning of duplication

            MsgBox " Carton Number: " & [CartonNo] & "-" & [CartonSuffix] & " has already been punched vide Contract No. " & [cmbDeliveryVr] & "." & vbCrLf & _

            "for Consignor: " & [cmbClientCIN] & "-" & ClientSelected & ", Consingee: " & ClientConsignee & "." & vbCrLf & _

            "In Consignment No. " & [ConsignmentNo] & "." _

            , vbInformation, "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


Thank you very much for your support and help.
Best regards,
Khalid


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I told you to ADD NameOfClient to that code. - The SELECT DISTINCT.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)





__._,_.___

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 (21)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar