VBA OpenForm with WhereCondition + LIKE on different fields

[Giorgio]

Hello everybody, I created form with a button that will open another form after having done a search action (with LIKE) in a field containing text.

 
Private Sub FindWithName_Click()
DoCmd.OpenForm "ResultsByName", , , WhereCondition:="Name LIKE '*" & Me.Name.Value & "*' "
End Sub

It works well.

The ResultsByName form is of course linked to a table (TBLClients).
TBLClients has many fields, and among them Name and also Brand (also a text field)

Now, I would need to check the condition set in LIKE not only on the Name feld, but also on the Brand field.

Practically speaking I need to add also the condition

 WhereCondition:="Brand LIKE '*" & Me.Name.Value & "*' "

but I do not find how to concatenate the two instructions

I need to get as results ALL the matching fields, so to get all the records if the criteria set in Name is found OR in Name OR in Brand.

I hope it is clear
thanks in advance

[Gicu]

Maybe try:

DoCmd.OpenForm "ResultsByName", , , WhereCondition:="[Name] LIKE '*" & Me.Name & "*' OR [Brand] LIKE '*" & Me.Name & "*' "

Note that I enclosed the field Name in square brackets as it is an Access reserved word and should not be used in object names. Also you don't need .Value as that is the default property of the controls.
Cheers,

[Giorgio]

Thanks Vlad it solved that problem, I appreciate. I was getting mad with ' and "...
Do not worry for the fields' names, the real ones are different, but as they were in Italian, I just tried to simplify them into English.
I may also have some similar problem but concatenating not only one table, but two different ones.
We have the phone numbers which are in TBLClients (landlines) and the mobile phones which are in a different table, TBLPeople;
So, in a form that would deliver the results of a search on phone numbers (both landline and mobile, so coming from two different tables), do I have to set up a query as origin of the form (instead of one only table, TBLClients, as before) or it can be done with VBA only?
Thanks again

[Gicu]

There are a few ways to do that, the easiest would be what you describe (base the form on a query joining the two tables). Be aware that the query (and the form) might not be updatable depending on your tables.

Cheers,

[Giorgio]

Thanks again Vlad, this is how I solved (keeping the original fileds names:

Private Sub CMDTrovaConTelefono_Click()
Dim strSQL As String
   
        strSQL = "SELECT TBLClienti.Telefono1, TBLClienti.Telefono2, TBLClienti.Telefono3, TBLPersone.TelefonoDiretto, TBLPersoneCellulare" & _
        "FROM TBLClienti " & _
             "INNER JOIN TBLPersone ON TBLClienti.CodiceCliente = TBLPersone.CodiceCliente "
If Me.Telefono & "" = "" Then
    MsgBox "Inserire un valore nel campo TELEFONO", vbQuestion, "COSA CERCHI?"
    Me.Telefono.SetFocus
    Exit Sub
End If

DoCmd.OpenForm "MSKRisultatoRicercaXTelefono", , , WhereCondition:="TBLClienti.Telefono1 LIKE ""*" & Me.Telefono & "*"" OR TBLClienti.Telefono2 LIKE ""*" & Me.Telefono & "*"" OR TBLClienti.Telefono3 LIKE ""*" & Me.Telefono & "*"" OR TBLpersone.TelefonoDiretto LIKE ""*" & Me.Telefono & "*"" OR TBLpersone.Cellulare LIKE ""*" & Me.Telefono & "*"""


    
End Sub

It works well, at least for me.

But now I have another porblem. Using a similar query I'd need to search in fields like Website or Email, which are not text fields, but are set as hiperlinks. I imagine that LIKE operator handles these fields in a different way, as just changing the fileds names does not work.
Any suggestion?
Thanks in advance

[accesstos]

Giorgio,

The structure of your table TBLClienti is not normalized. You should have a separate table for the phones with a structure like this:

tblPhones
phoneNum: Text(14) PK,
phoneType: Number (1:Mobile, 2:Landline, 3:Satelite etc),
phoneDesc: Text (Home, FAX, Personal, Internal etc),
ClientIDfk: Number (FK to TBLClienti)

So, at first, you have the possibility to add phones infinitely for each client and then, the criteria for WhereCondition could be “ClientID=” & Me!ClientID with the table tblPhones as RecordSource of the form "MSKRisultatoRicercaXTelefono".

The same for the addresses (Website,Email, Social etc).

Cheers,
John

[Gicu]

Hi Giorgio,
I strongly recommend you following John's suggestion and changing the db structure. It might be a bit of pain now but it will help you a lot for future development. For the hyperlinks I think you would have to use "Like" with the HyperLinkPart but try it without first and see what happens:
https://docs.microsoft.com/en-us/off....hyperlinkpart

Cheers,

[Giorgio]

i thought I had solved but... Here is my problem.

when searching for a phone number (stored in TBLClienti.Telefono1, 2 and in TBLPersoneTelefonoDiretto, etc.)
I use this code:

Private Sub CMDTrovaConTelefono_Click()Dim strSQL As String
   
        strSQL = "SELECT TBLClienti.Telefono1, TBLClienti.Telefono2, TBLClienti.Telefono3, TBLPersone.TelefonoDiretto, TBLPersoneCellulare" & _
        "FROM TBLClienti " & _
             "INNER JOIN TBLPersone ON TBLClienti.CodiceCliente = TBLPersone.CodiceCliente "
If Me.Telefono & "" = "" Then
    MsgBox "Inserire un valore nel campo TELEFONO", vbQuestion, "COSA CERCHI?"
    Me.Telefono.SetFocus
    Exit Sub
End If


DoCmd.OpenForm "MSKRisultatoRicercaXTelefono", , , WhereCondition:="TBLClienti.Telefono1 LIKE ""*" & Me.Telefono & "*"" OR TBLClienti.Telefono2 LIKE ""*" & Me.Telefono & "*"" OR TBLClienti.Telefono3 LIKE ""*" & Me.Telefono & "*"" OR TBLpersone.TelefonoDiretto LIKE ""*" & Me.Telefono & "*"" OR TBLpersone.Cellulare LIKE ""*" & Me.Telefono & "*"""
    
End Sub

The two tables, TBLClienti and TBLPersone are linked in the form MSKRisultatoRicercaXTelefono with this SQL:

SELECT TBLClienti.Telefono1, TBLClienti.Telefono2, TBLClienti.Telefono3, TBLPersone.TelefonoDiretto, TBLPersone.Cellulare, TBLClienti.NomeAzienda, * FROM TBLClienti INNER JOIN TBLPersone ON TBLClienti.CodiceCliente = TBLPersone.CodiceCliente;

It works well as long as the searched number exists and is found. If I input a non existent phone number, like 11111111 then the result form (MSKRisultatoRicercaXTelefono) opens without showing any result but soon after the programme crashes, closes acccess and automatically reopens asking for a backup of the database.

help!

[moke123]

Still looks like you ignored Johns advice which makes a simple task into a complicated one. You should not have repeating fields Like Phone1, Phone2, etc.
If clienti are people and Persone are people then it may be appropriate to combine the two tables and add a field to distinguish the two.
If not similiar then you could add a field to Johns example like PersonIDfk in the phone table. Then you are simply querying one table and one field.

Edit: Just my opinion but I find hyperlink datatype to be a real P.I.T.A. to work with. You can still get the same effect by storing email and websites as plain text and use code to launch the appropriate program.
http://access.mvps.org/access/api/api0018.htm

[Giorgio]

Still looks like you ignored Johns advice which makes a simple task into a complicated one. You should not have repeating fields Like Phone1, Phone2, etc.
If clienti are people and Persone are people then it may be appropriate to combine the two tables and add a field to distinguish the two.
If not similiar then you could add a field to Johns example like PersonIDfk in the phone table. Then you are simply querying one table and one field.

unfortnately I am not allowed to change the structure of the tables even if it would be logical. and this would involve some thousands of numbers anyway
In any case Client are companies (with landlines phone numbers) and Persone are persons with direct phones and mobiles, so, many years ago they were divided in two tables

[moke123]

unfortnately I am not allowed to change the structure of the tables even if it would be logical. and this would involve some thousands of numbers anyway
In any case Client are companies (with landlines phone numbers) and Persone are persons with direct phones and mobiles, so, many years ago they were divided in two tables

Thats too bad. Makes your job more difficult.

Does your form open without issue without using the where clause? Does it show every record?

Another approach may be to loop through the tables seperately and concatenate a string of ID's to use in a where clause like - "Where ClientID in(0,1,4,27,54,66) or PersonID in (0,2,7,55,102,360)"
I use 0 as a default if there are no records so you wind up with a where clause like - "Where ClientID in(0) or PersonID in (0)"

[Gicu]

HI Giorgio,
I had a look at your sample and mine does not crashes if it cannot find the number. So I think you might have some early signs of corruption with that form. You should decompile VBA project then compile it and go through and fix any compile errors you might encounter. In the code you show you do not need the strSQL (in any of the buttons) as you are declaring it, set its value then you do not use it anywhere). And even more important you do not need one form for each type of search (email, phone, etc.), having all those copies clutters the database and one change in one would have to be replicated in all of them.
What should happen if the number is not found? Prompt the user and do nothing (like the message they get if the number is missing) or still open the form and show all records?

Cheers,
Vlad

[Giorgio]

HI Giorgio,
I had a look at your sample and mine does not crashes if it cannot find the number. So I think you might have some early signs of corruption with that form. You should decompile VBA project then compile it and go through and fix any compile errors you might encounter. In the code you show you do not need the strSQL (in any of the buttons) as you are declaring it, set its value then you do not use it anywhere). And even more important you do not need one form for each type of search (email, phone, etc.), having all those copies clutters the database and one change in one would have to be replicated in all of them.
What should happen if the number is not found? Prompt the user and do nothing (like the message they get if the number is missing) or still open the form and show all records?

Cheers,
Vlad

Thanks Vlad, I used your modified DB (the one in which the switchboard is unachanged, I inputted 11111111 as a phone number to be searched and it crashed. I do not understand why it does not happen to you...

[moke123]

I had a look at your sample

What sample?

[Gicu]

Here is a version (unfortunately I picked the other one ) where I tweaked your code (note that I created a query called qrySearch to do the count). I would still suggest you decompile\recompile and see if you get any errors. And you should only have one search results form.

I only changed the code for the phone search; once you tweak the message you can incorporate it for the others.
Cheers,
Vlad