Dlookup -
Hi all,
Further to earlier question ID 26886434.
I have set two fields indexed so that no two combinations of data entry can occur within a form.
Subsequent to that I have an custom error message which overrides the Microsoft error message. This is set in the form's "On Error" property.
However my question is/ task is to show up either as part of the form view or as part of the custom error message the original record. In other words:
Record 1:-
Field Book = 1
Field Date = 1/1/2011
Field Person = Bob
The second entry:
Field Book = 1
Field Date = 1/1/2011
Field Person = John
At this point the custom error message will pop up because Field Book and Date for the second entry are the same as entry1, saying this record cannot be saved.
I would like that the message said something like
"Bob is already allocated to this Book on this date"
Grateful for assistance once again.
Further to earlier question ID 26886434.
I have set two fields indexed so that no two combinations of data entry can occur within a form.
Subsequent to that I have an custom error message which overrides the Microsoft error message. This is set in the form's "On Error" property.
However my question is/ task is to show up either as part of the form view or as part of the custom error message the original record. In other words:
Record 1:-
Field Book = 1
Field Date = 1/1/2011
Field Person = Bob
The second entry:
Field Book = 1
Field Date = 1/1/2011
Field Person = John
At this point the custom error message will pop up because Field Book and Date for the second entry are the same as entry1, saying this record cannot be saved.
I would like that the message said something like
"Bob is already allocated to this Book on this date"
Grateful for assistance once again.
knightEknight
FYI All - here is a link to the referenced question: https://www.experts-exchange.com/questions/26886434/Checking-records.html
Are you locked into that logic?
Are you basically looking to avoid duplicates?
Another approach would be to do a "Duplicate check" on the Before update event of the "Form".
The issue you are having is typical of an attempt to do this "On-the-fly"
(Which seems like a like a great idea, but is riddled with pitfalls.)
The "Indexing" setup (Which you did not provide any details on) should manage this. (before the record is committed)
But again, you are trying to circumvent Access' built in way of dealing with this.
Is your ultimate question here: "How do I avoid duplicates?
If so, then this issue has been dealt with hundreds of times here, ...just do a search here or on Google of
"Avoid duplicate records"
Or
Is your question: "Using my custom system and logic, can you guys figure out how to make it work?"
JeffCoachman
Are you basically looking to avoid duplicates?
Another approach would be to do a "Duplicate check" on the Before update event of the "Form".
The issue you are having is typical of an attempt to do this "On-the-fly"
(Which seems like a like a great idea, but is riddled with pitfalls.)
The "Indexing" setup (Which you did not provide any details on) should manage this. (before the record is committed)
But again, you are trying to circumvent Access' built in way of dealing with this.
Is your ultimate question here: "How do I avoid duplicates?
If so, then this issue has been dealt with hundreds of times here, ...just do a search here or on Google of
"Avoid duplicate records"
Or
Is your question: "Using my custom system and logic, can you guys figure out how to make it work?"
JeffCoachman
ASKER
Hi knight,
thanks....your reference is actually my initial question. However I am trying to build on that.
Thanks again.
thanks....your reference is actually my initial question. However I am trying to build on that.
Thanks again.
ASKER
Hi boag,
thanks for your prompt reply.
In the database I am working on I was advised to index the two fields where I dont want duplicates to appear.
Indexing both fields worked a treat.
In other words the form will not allow any further entries until one of the two entries is changed. As you've gathered, if any two new entries are the same as the two entries (in the same two fields) are the same for an already existing record the system will prevent it.
That so far is working fine. My task is whether I can show the existing record.
I hope I am making sense. Please let me know.
Thanks
thanks for your prompt reply.
In the database I am working on I was advised to index the two fields where I dont want duplicates to appear.
Indexing both fields worked a treat.
In other words the form will not allow any further entries until one of the two entries is changed. As you've gathered, if any two new entries are the same as the two entries (in the same two fields) are the same for an already existing record the system will prevent it.
That so far is working fine. My task is whether I can show the existing record.
I hope I am making sense. Please let me know.
Thanks
ASKER
Hi,
Sorry Knight,
Did not read your message properly. Hadn't realised you were referencing my initial question for all to see.
Thanks
Sorry Knight,
Did not read your message properly. Hadn't realised you were referencing my initial question for all to see.
Thanks
So all you want to do is display the Conflicting date?
Try something like this, if you enter these dates into a textbox.
Msgbox "Bob is already allocated to this Book on this date: " & txtFieldDate
Try something like this, if you enter these dates into a textbox.
Msgbox "Bob is already allocated to this Book on this date: " & txtFieldDate
ASKER
yes,
but how do i do that? the error occurs because a second record has the same data on the two indexed fields as an already existent record. This is not a dlookup at all.
thanks for the assistance
but how do i do that? the error occurs because a second record has the same data on the two indexed fields as an already existent record. This is not a dlookup at all.
thanks for the assistance
The you will have to post a sample of your database, so I can see what you have.
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.
JeffCoachman
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.
JeffCoachman
ASKER
Hi,
Thanks...plse see attached...
Also note that I've disabled the save button. It just wasnt working.
I would prefer that on entering the date (i.e the second entry) the system would stop and highlight the fact that that combination of book and date already exists, and that it belongs to whoever. But because the form is filtered I find that is not possible, because until the record is saved into the table the program wont know if there is a duplication.
Should I remove the filter?
Is there another way around this?
Grateful for any advice.
Test4EE7-.mdb
Thanks...plse see attached...
Also note that I've disabled the save button. It just wasnt working.
I would prefer that on entering the date (i.e the second entry) the system would stop and highlight the fact that that combination of book and date already exists, and that it belongs to whoever. But because the form is filtered I find that is not possible, because until the record is saved into the table the program wont know if there is a duplication.
Should I remove the filter?
Is there another way around this?
Grateful for any advice.
Test4EE7-.mdb
And what about step 12 from above?
Also, the tables are not related, this could be part of the issue...?
What is your ultimate goal?
In other words, *Regardless of what you have here*, what is the ultimate goal of this system.
Ex.: please state the ultimate goal in this form:...
"I want the user to open the form and enter____, and____ and ____
If ______ , I want ______ to ______"
JeffCoachman
Also, the tables are not related, this could be part of the issue...?
What is your ultimate goal?
In other words, *Regardless of what you have here*, what is the ultimate goal of this system.
Ex.: please state the ultimate goal in this form:...
"I want the user to open the form and enter____, and____ and ____
If ______ , I want ______ to ______"
JeffCoachman
ASKER
Gosh!
Yes I know that the tables are not related. Is this necessary at this point?
re Step 12....
process =
Step1 : enter file no from combo box
Step 2 : enter the date.
If when i enter the date, the combination of file no and date already exists then at that that point a message box appears which says something like "this file on this date has already been allocated"
the program will stop and will not continue until I undo those two entries (the undo button is not there yet ) or until I either change the file no or the date!
I hope this makes sense. Please let me know if you require further explanation.
Yes I know that the tables are not related. Is this necessary at this point?
re Step 12....
process =
Step1 : enter file no from combo box
Step 2 : enter the date.
If when i enter the date, the combination of file no and date already exists then at that that point a message box appears which says something like "this file on this date has already been allocated"
the program will stop and will not continue until I undo those two entries (the undo button is not there yet ) or until I either change the file no or the date!
I hope this makes sense. Please let me know if you require further explanation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks Boag2000,
Point taken...I suppose you're right.
As you rightly say I've asked multiple questions and that s because I am seeking expert opinions as I go along.
My intention is not being rude come across as unreasonable all I am trying to do is learn ways of doing this.
I can appreciate that some may feel exasperated with my type of reasong and questions but I also feel I am entitled to all my questions given the fact that i am a fully subscribed member paying my monthly fees too.
Thanks anyway, Ive learnt quite a bit...especially the idea of the web page order form.
Point taken...I suppose you're right.
As you rightly say I've asked multiple questions and that s because I am seeking expert opinions as I go along.
My intention is not being rude come across as unreasonable all I am trying to do is learn ways of doing this.
I can appreciate that some may feel exasperated with my type of reasong and questions but I also feel I am entitled to all my questions given the fact that i am a fully subscribed member paying my monthly fees too.
Thanks anyway, Ive learnt quite a bit...especially the idea of the web page order form.
ASKER
Satisfied with answer
<I can appreciate that some may feel exasperated with my type of reasong and questions but I also feel I am entitled to all my questions given the fact that i am a fully subscribed member paying my monthly fees too. >
yes, you are absolutely correct.
Just bear in mind that we are all volunteers here (Not paid), so in order to get the most experts involved in helping you, ...you might want to keep the "exasperative-ness" to a minimum...
;-)
I have learned in my 6 years of answering questions here that sometimes the question should be:
"This is what I want to do, what do you think of my approach?"
...instead of:
"This is the way I want to do it, and it's not working, can you make it work for me?"
Sound fair?
;-)
JeffCoachman
yes, you are absolutely correct.
Just bear in mind that we are all volunteers here (Not paid), so in order to get the most experts involved in helping you, ...you might want to keep the "exasperative-ness" to a minimum...
;-)
I have learned in my 6 years of answering questions here that sometimes the question should be:
"This is what I want to do, what do you think of my approach?"
...instead of:
"This is the way I want to do it, and it's not working, can you make it work for me?"
Sound fair?
;-)
JeffCoachman
ASKER
Point taken....
I will keep that in mind.
Hope to hear from you soon with answers from my other questions... :)
pipmic
I will keep that in mind.
Hope to hear from you soon with answers from my other questions... :)
pipmic