Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Dlookup -

Posted on 2011-03-23
Medium Priority
Last Modified: 2012-05-11
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.
Question by:PipMic
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
LVL 33

Expert Comment

ID: 35198791
FYI All - here is a link to the referenced question:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26886434.html
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199079
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?"


Author Comment

ID: 35199731
Hi knight,

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

Thanks again.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 35199817
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.



Author Comment

ID: 35199890

Sorry Knight,

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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200457
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

Author Comment

ID: 35202202

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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35204125
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.


Author Comment

ID: 35205476

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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35206777
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 ______"


Author Comment

ID: 35208045
 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.
LVL 74

Accepted Solution

Jeffrey Coachman earned 600 total points
ID: 35209722
 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



Author Comment

ID: 35210359
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.

Author Closing Comment

ID: 35210491
Satisfied with answer
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35211968
<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?



Author Comment

ID: 35214700
Point taken....

I will keep that in mind.

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


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question