duplicate values

[tarhim47]

Hi,



Im trying to stop users from entering the same client name in two difference records in the same table - lead activity report.

I ran a query to find duplicate values - client name, in the table "lead activity report." Then in the lead activity report table, under the properties for "Client Name" text box, I entered a macro which basically should return a message box if there are duplicate values in the query, but its not working and I cant seem to figure out why.

My dbase is attaced!

Can someone please help me?!!!

THANKS!!!

[NoellaG]

Hi,

the simplest way to avoid doubles in a field is putting an unique index on the field.

greetings
NG

[tarhim47]

Hey NG,

Thanks very much for the reply. What a simple and elegant solution! Its funny how easy things are in hindsight.

Would you know if I can customize the message that appears when a duplicate value is entered?

Thanks

[NoellaG]

The only way I can think of right now to change the error message is trapping the error number in the error handler of VBA code and substituting with your own. I don't think there is a way without using code, but perhaps someone else knows.

greetings
NG

[tarhim47]

Hey NG,

That sounds a little advanced. I think Im just going to leave it as is.

However, would you be able to help me with a union query. Im trying to combine data entered in two different forms. My union query doesnt combine all the result - just shows the results for the lead discussions with COIs.

Here's what I did:

I made a query for lead discussions with COIs
- form: Lead Activity Report
- fields: date of last contact, COI and comments - COI

I then made a query for general discussions with COIs
- form: COI Discussion Form
- fields: date, COI and general comments

Fianlly, I created the union query in SQL view. See below. I basically opened the above two queries in SQL view and copied them into my union query and put "UNION ALL" between the two.

SELECT [Lead Activity Report].[Date of last contact], [Lead Activity Report].COI, [Lead Activity Report].[Comments - COI]
FROM [Lead Activity Report]
UNION ALL SELECT [COI Discussion Form].Date, [COI Discussion Form].COI, [COI Discussion Form].[Discussion points]
FROM [COI Discussion Form];

Any ideas?

Thanks again for your help!

[NoellaG]

Hi,

I tried the union query in your database example, and it works fine: the result shows the 4 records from the first table + the record from the second table.
However, the database design is rather poorly and will get you into troubles. My advise:

• first do some reading on relational database design and normalization, there are a lot of sites on this topic like http://www.deeptraining.com/litwin/d...aseDesign.aspx
• also give all the objects you create meaningful names, you can't name a table "Discussion form" or "Activity Report" , because it's a table and not a form or report. Don't use spaces or other odd signs in your table, field or other object names. An example for a better name would be tblDiscussions.

Once you get your design cleaned up, you'll find that a lot of problems will have disappeared.

Succes
NG

[tarhim47]

Hi NG,

I figured it out as well. Works just fine.

Thanks for the tip. I am very much in the first stage of the dbase design and the dbase I sent you is a rough version.

I will most certainly take your tips into consideration before finalizing anything.

Thanks!

[tarhim47]

Hi NG,

Can you help me with something else as well? Hope Im not buggin you too much!

As you saw in the dbase, I have a form which will be used to gather data from the users. One of the fields is "leads." This is supposed to be potential clients the partners/managers are trying to attract to our firm. So there is no way for me to create a table of leads. Aother field is "COI." These are people who introduced the partner/manager to the lead. Majority of the COIs are new and some will be old who constantly refer others to our practice. In either case, there are not enough old COIs for me to create a table of them.

What I want to be able to do is have the form remember COIs entered on past records. For example, if the COI on record 1 is Murphy then if the same COI referred us to lead 10 (recorded in record 10), I want the user to start typing in the COIs name and have access generate a list of COIs who have similar names and allow the user to select from that list.

I tried convering my COI field to a combox and turned on Auto Expand but it still doesnt remembe past entries or shows a list of them.

Any ideas? Your help is greatly appreciated!