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@yahoo.com [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-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Your criteria are:
.. 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,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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.
Thank you very much for your support and help.
Best regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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)
On Aug 29, 2017, at 3:10 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
In your code i can not see "NameOfClient" anywhere ?
Any how applying your current code:
Say for Consignment No.2017-B-01, cmbConsignmentNo shows List for NameOfCient
ABC for CientCIN 29
I renamed this "ClientName" to XYZ and made data entry in form.
This NameOfClient is also displaying in List
------------------------------
Now in cmbClientCIN i cannot see NameOfClient "ABC"
-----------------------------------
If i selected "XYZ" from cmbClientCIN, the report shows NameOfClient = XYZ, but shows 30 records i.e:
29 records of ABC and 1 record of XYZ.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
No. I was talking about this bit of code:
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName, Consignee.ConsigneeName " & _
"FROM Consignee INNER JOIN (Clients INNER JOIN CollectionVoucher " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN) " & _
"ON Consignee.ConsigneeName = Clients.CosigneeName " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY Clients.ClientCIN"
Elsewhere in code, you that will allow you to fetch the related NameOfClient from the combo box.
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
__._,_.___
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 (19) |
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