Solved

Dlookup -

Posted on 2011-03-23
16
507 Views
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.
0
Comment
Question by:PipMic
  • 9
  • 6
16 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35198791
FYI All - here is a link to the referenced question:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26886434.html
0
 
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"
Or
Is your question: "Using my custom system and logic, can you guys figure out how to make it work?"


JeffCoachman
0
 

Author Comment

by:PipMic
ID: 35199731
Hi knight,

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

Thanks again.
0
 

Author Comment

by:PipMic
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.

Thanks

0
 

Author Comment

by:PipMic
ID: 35199890
Hi,

Sorry Knight,

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

Thanks
0
 
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
0
 

Author Comment

by:PipMic
ID: 35202202
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
0
 
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.


JeffCoachman
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:PipMic
ID: 35205476
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
0
 
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 ______"

JeffCoachman
0
 

Author Comment

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

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
ID: 35209722
<Gosh!
 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


;-)

JeffCoachman
Access-EEQ26905995DuplicateRecor.mdb
0
 

Author Comment

by:PipMic
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.
0
 

Author Closing Comment

by:PipMic
ID: 35210491
Satisfied with answer
0
 
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?

;-)

JeffCoachman
0
 

Author Comment

by:PipMic
ID: 35214700
Point taken....

I will keep that in mind.

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

pipmic
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now