VCount / VLookup! HELP!

[Textatello]

Hello guys,

I have a relatively simple question but I am an amateur when it comes to VBA. I'll try my best to make this as simple and straight to the point as possible.

I have two tables. One is tblMain and the other is tblFinanceReport. Both tables contain a field Customer Name. When the Customer Name field is updated in my form, I would like a message to pop up notifying that there is a successful match.

I have a piece of code working but it's only working if the Customer Name is written identically to the Customer Name in the tblFinanceReport table. Whereas, I'm guessing, I need to use a wildcard ( * ) for it not to be needed to be written in the same way.

 Private Sub txtAccountName_AfterUpdate()
 If DCount("*", "tblFinanceReport", "CustomerName='" & Me.txtAccountName & "'") > 0 Then
      If MsgBox("Match successful", vbQuestion + vbYesNo, "Finance Report") = vbYes Then
           Cancel = True
     End If
 End If
 End Sub

Now, it's working how I want it to but if lets say there are two Customers. One is called Keyboard Limited and another is called Keyboard Ltd, and someone inputs Keyboard Limited, it will not pop up because Keyboard Ltd needs to be written.

For a query, all I need to do is use this....
Like "*" & [Enter Customer Name] & "*"

The above works fine for a query, but I don't want to use a query. I just want the code to execute after that field has been updated.

All help is appreciate and thank you in advance.

Thanks
Chris

 

[CJ_London]

you can do the same in your dcount

DCount("*", "tblFinanceReport", "CustomerName Like '*" & Me.txtAccountName & "*'")

 

[Textatello]

EDIT: It's working now! Thanks for that!

 

[isladogs]

A much easier solution is to use a combobox or listbox which lists the existing customers in the main table. Then when using the finance form, users select from the available list. That way there's no chance of error or spelling mistakes etc

 

[Orthodox Dave]

Colin beat me to it - I was about to suggest a combo box. You could allow the user to enter a new Account Name by setting the "limit to list" option to No.

I don't think the solution as it stands will work anyway with the example you give - i.e. "*Keyboard Ltd*" will not produce a Like match to "Keyboard Limited" because of the literal string. (The wildcards are either side of the literal string).

 

[Textatello]

Is it possible to tell the database that "Ltd" and "Limited" are the same thing? That would solve me a lot of hassle!

 

[Orthodox Dave]

Not a good road to go down. Then you'll need Ltd. (with the dot), Inc, Incorporated, Inc., Partners, Ptnrs - the list could be endless.

 

[Textatello]

The only company type that we have in the finance reports table is Ltd or Limited. So in a hypothetical sense, is it possible?

 

[CJ_London]

only by having the user not typing in Ltd or Limited/have code to remove them (see below) or by having multiple dcounts using the replace function for all the different permutations.

You are making a lot of work for yourself, and users being users, they will find something that doesn't work. A combo would be a better solution

An example of using the replace function

DCount("*", "tblFinanceReport", "CustomerName Like '*" & replace(replace(Me.txtAccountName," Ltd","")," Limited","") & "*'")

 

[CJ_London]

just noticed you have been cross posting - you will not find favour with responders if you persist.

for the benefit of other responders, cross posted here

http://www.accessforums.net/showthread.php?t=68903

OP has been given the usual advice

 

[Solo712]

Is it possible to tell the database that "Ltd" and "Limited" are the same thing? That would solve me a lot of hassle!

You may add a field called "corporate suffix" after the company name to the table, and put "Ltd", "Limited", "Inc" or whatever in it. Then you can look up the company name by the name alone. In references to the firm as a corporate entity, in correspondence, etc. you would concatenate Corporate Name & CorporateSuffix.

Best,
Jiri

 

[isladogs]

You may add a field called "corporate suffix" after the company name to the table, and put "Ltd", "Limited", "Inc" or whatever in it. Then you can look up the company name by the name alone. In references to the firm as a corporate entity, in correspondence, etc. you would concatenate Corporate Name & CorporateSuffix.

Best,
Jiri

True ... but it would still be far easier to use a combobox or listbox as I suggested in post #4