DCount / DLookup Question

[TEXEIRC]

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

[aytee111]

The danger of having data that is repeated. That is a big no-no in a normalized table structure. Remove it. Create a table that contains only CustomerID and CustomerName. Then carry that CustomerID thru to your two other tables. In that way a user cannot change the name of a customer or enter one that is spelled differently.

[CJ_London]

cross posted here with solution

https://www.access-programmers.co.uk...d.php?t=296716

@Texeirc - please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184

[TEXEIRC]

Apologies, I done it out of efficiency. I had no idea I was breaking any rules. I am on a tight deadline and just wanted an answer as fast as possible.

EDIT: I have read that thread and again, apologies for that. I didn't even think about the consequences. I am new to posting on these type of forums and I'll make sure to follow the suggestions.

[CJ_London]

no need to apologise, you wouldn't have known. But now you do