[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

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.
  • 9
  • 6
1 Solution
FYI All - here is a link to the referenced question:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26886434.html
Jeffrey CoachmanMIS LiasonCommented:
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"
Is your question: "Using my custom system and logic, can you guys figure out how to make it work?"

PipMicAuthor Commented:
Hi knight,

thanks....your reference is actually my initial question. However I am trying to build on that.

Thanks again.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

PipMicAuthor Commented:
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.


PipMicAuthor Commented:

Sorry Knight,

Did not read your message properly. Hadn't realised you were referencing my initial question for all to see.

Jeffrey CoachmanMIS LiasonCommented:
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
PipMicAuthor Commented:

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
Jeffrey CoachmanMIS LiasonCommented:
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.

PipMicAuthor Commented:

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.
Jeffrey CoachmanMIS LiasonCommented:
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 ______"

PipMicAuthor Commented:
 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.
Jeffrey CoachmanMIS LiasonCommented:
 Yes I know that the tables are not related. Is this necessary at this point?>

Please remember that this is your question, I am simply trying to help.
I was not privy to the design.

My concern, as I stated above, is that perhaps this might be part of the issue.
*Normally*, (At least for what you are doing), you don't have two tables with the exact same fields and not have them related.
So I can't really say if this is "necessary at this point", because I don't know why they are not related...

Now, moving on to the issue at hand...
Your request:
<"I would prefer that on entering the date (i.e the second entry) the system would stop ...>
What you are trying to do here is normally done on the "before update event" of the form, not "On-the-fly", (while you are entering the data), as you are trying to do.

Doing this as you are trying is why you are having all the issues.
As cool as it may seem to do this "as soon as a duplicate is detected", this approach has problems.
This is why you are having to post multiple questions on this.

Think about a web page order form.
You enter *All* of your data, then click "Next"
*Then* you are alerted of any error (Duplicates, missing data, invalid data)
The web page system will not stop you immediately after you enter your credit card number and tell you if it is valid or not.
It will not stop you if you did not select a shipper
It will not stop you did not enter a quantity.
It will do all the checks *After* all the data is entered, not "During".

Your approach will also somehow  have to *Force* the user to do complete the form in your predefined tab order, else your validation could be bypassed.

If you did this in the standard way, you won't need matched Indexes or special error detection.
Also you can Cancel the record and undo it.
Again, this is the standard way that this is done.

But don't take my word for it, post a new separate question and ask what event duplicates are typically detected on...

In any event here is a sample illustrating the standard method.
In my sample, a Rental date of: 1/1/2011
...and a BookID of: 10
...are used as the reference field combination.

All this being said, if you really want to do this in the way you requested, I am sure you can find an Expert to help you do this.
But, I try not to create Non-standard functionality, because of all the machinations required to get it "just right".
Again, don't take my word on this, do your own research on this subject, or post a new question here on this subject.
Feel free to reference my name full, real name: Jeffrey Coachman (Or my login here:  boag2000)
...And this question: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26905995.html


PipMicAuthor Commented:
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.
PipMicAuthor Commented:
Satisfied with answer
Jeffrey CoachmanMIS LiasonCommented:
<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?


PipMicAuthor Commented:
Point taken....

I will keep that in mind.

Hope to hear from you soon with answers from my other questions...   :)


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now