Checking of a Record Exits at Data Entry Time

[txaccess]

I have developed a sales force automation app for my company. The latest feature is a lead tracking mechanism. When a lead comes in, I would like the user to type the lead company name and then the d/b will look to see if the lead company already exists in the db. If yes, then a msg will display stating who is the rep responsible for the company and assign the lead to that rep.

Every company is input into the d/b as an Account with Contacts and Opportunities. In the example where a new lead is registered and the lead does not already exist as an Account, the lead is simply registered.

In the event where the lead is an existing Account, then the d/b needs to pull across the Account Ref, Name, Address and Owner into the Lead Table.

The Company field and Account Fields are separate fields on the form and table. Therefore, when the user enters the Company field, the d/b needs to check whether this name exists on the Account field.

Sorry about the convoluted asking of this question. I think I managed to confuse myself!!!! I hope someone out there can understand what on earth I am going on about and offer help.

Thanks

I haven't failed, I have just found 10,000 ways that it won't work!

 

[Trendsetter]

It is possible to have the same "company" name and it still be OK... say a franchise operation in different locations or a public house with the same name eg: The Black Bull!
You need to extend the range of validation to include maybe the PostCode or Zip.
I have done this by contatenating the 2 and searching on that string to find and part and list the results.

Postcode alone will list of the companies in the database with that postcode - name alone will do the same for the named company and all of the postcodes found.
This way you can be sure that you are matching the geographical data with the name..

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[txaccess]

Frank - it is slightly more convoluted I am afraid. An Account can have a Master Account, so where there is a large company with many susidiaries, or Franchises etc, each can be listed, assigned to a rep and linked back to the Master Account.

What I dont have is the code to check whether the Account Name exists. I can use a combo box on the text field to autopopulate the field is the name has been typed in before, but this will not fire of the events that I need.

Fields:

CompanyName
AccountName
AccountRef

Type in CompanyName - BeforeUpdate Event looks to see if the CompanyName is a duplicate of AccountName - I need the code to loop thru the records to perform this action. If duplicate is true, then I can fire the Events/Actions I need.

Make sense?

Thanks

I haven't failed, I have just found 10,000 ways that it won't work!

 

[Trendsetter]

I use the After_update event and use:
Me.lstClients.RowSource = "SELECT * FROM qryNewClSearch WHERE Show LIKE '*" & Me.txtContactSearch & "*'"
This populates a listbox with the search results from qryNewClSearch which is the contacentation of Name - Town - Postcode - Tel Number.
You can any any part of the above and obtain a list to choose from.
This is used in a Telephone Sales database and dramatically reduces the incidence of company duplications.



Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[txaccess]

As soon as I read your response I kicked myself for nbeing so dumb!!!! Duh!!!! Thanks a lot, that makes a whole heap of sense.

I owe you a beer.

I haven't failed, I have just found 10,000 ways that it won't work!