Dynamic multi search help (1 Viewer)

rchumpitaz · Sep 22, 2018

Hello I'm using John Big Booty code in my db for some multi search but when I tried to apply it in another DB in a field that are not neccesary filled by the user the listbox show me no records but only the ones that were fill. How can I correct that? Thank you

link to dynamic multi search:
//access-programmers.co.uk/forums/showthread.php?t=188663

CJ_London · Sep 22, 2018

do not understand what you are saying - the link has two examples of code and the code is about creating a search string and would no doubt need to be modified to work elsewhere but you your question seems to be about the listbox, not the code

You need to help us to help you. provide full details of what you are actually using

jdraw · Sep 22, 2018

I do not understand your post either. More details and possibly some examples of your needs might help readers.
You can post your database (even though you have less than 10 posts) if you make it a zip file.

rchumpitaz · Sep 22, 2018

Sorry about my little explanation. The code above is the one I'm using for searching records but when the field in the table is empty because is an optional field there is no record show in the even when the textbox is empty.

Private Sub SearchFor_Change()

'Create a string (text) variable
Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus

If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

End Sub

CJ_London · Sep 22, 2018

I/we understand what the search code does, but do not understand what you mean by

but when the field in the table is empty because is an optional field there is no record show in the even when the textbox is empty.

provide some examples of what is in the table and what you are searching for, the expected outcome and the actual outcome. Also post the sql to the rowsource to the listbox and the QRY_SearchAll query

Also, when posting code, please use the code tags (highlight the code and click the # button) to preserve formatting

rchumpitaz · Sep 22, 2018

This is the query (//imgur.com/Y9o0BIK) When I try to make a filter for the field "ACABADOS" and there is no records in it the filter show no results but after some research I think it has to be related with the query criteria (//imgur.com/Y9o0BIK).

CJ_London · Sep 22, 2018

you are not provided what has been asked for

post the sql to the rowsource to the listbox and the QRY_SearchAll query

provide some examples of what is in the table and what you are searching for

otherwise we cannot help

rchumpitaz · Sep 22, 2018

Sorry, here it is (//drive.google.com/open?id=1xB8K6eHuGjd8pIGwiV11qHTcVszNsqPx) when a field that is compromise in the search is empty it is not show in the results. On purpose I put some incomplete records so as you can check in "tlb_data" there is 5 records but in the "qer_data" theres only three. I hope you can help. Thanks

CJ_London · Sep 22, 2018

Sorry, for security reasons I don't go to unknown sites, just copy and paste the sql - and provide some examples as asked twice before.

rchumpitaz · Sep 22, 2018

This is the SQL (SELECT tbl_data.ID_REGISTRO, tbl_data.Fecha_Registro, tbl_data.Proveedor, tbl_data.Codigo_SOLTEG, tbl_data.Familia, tbl_data.Titulo, tbl_data.Composicion, tbl_data.Apariencia, tbl_data.Acabados, tbl_data.Descripcion, tbl_data.Observacion, tbl_data.Estampado, tbl_data.Solido, tbl_data.Ancho_De_Tela, tbl_data.Densidad_De_Tela, tbl_data.Rendimiento, tbl_data.Encogimiento_Largo, tbl_data.Encogimiento_Ancho, tbl_data.Revirado, tbl_data.Precio_Blanco, tbl_data.Precio_Colores, tbl_data.Precio_PPT, tbl_data.Numer

C, tbl_data.Fecha_OC, tbl_data.PD_OC
FROM tbl_data
WHERE (((tbl_data.Proveedor) Like "*" & [forms]![frm_consulta]![provee_txt] & "*") AND ((tbl_data.Familia) Like "*" & [forms]![frm_consulta]![fami_txt] & "*") AND ((tbl_data.Titulo) Like "*" & [forms]![frm_consulta]![titu_txt] & "*") AND ((tbl_data.Composicion) Like "*" & [forms]![frm_consulta]![comp_txt] & "*"))

In the 'frm_consulta' form when the field that is linked to the search are empty there are no results. For example when there's no data in the "proveedor" field in some record is not show in the results the same happens in "titulo", "familia", etc.

CJ_London · Sep 22, 2018

please, please please try to answer my questions. I shan't ask again and am unable to help you if you do not supply the requested information

1. use code tags - I've already explained how to do this

Code:

(SELECT tbl_data.ID_REGISTRO, tbl_data.Fecha_Registro, tbl_data.Proveedor, tbl_data.Codigo_SOLTEG, tbl_data.Familia, tbl_data.Titulo, tbl_data.Composicion, tbl_data.Apariencia, tbl_data.Acabados, tbl_data.Descripcion, tbl_data.Observacion, tbl_data.Estampado, tbl_data.Solido, tbl_data.Ancho_De_Tela, tbl_data.Densidad_De_Tela, tbl_data.Rendimiento, tbl_data.Encogimiento_Largo, tbl_data.Encogimiento_Ancho, tbl_data.Revirado, tbl_data.Precio_Blanco, tbl_data.Precio_Colores, tbl_data.Precio_PPT, tbl_data.Numero_OC, tbl_data.Fecha_OC, tbl_data.PD_OC
FROM tbl_data
WHERE (((tbl_data.Proveedor) Like "*" & [forms]![frm_consulta]![provee_txt] & "*") AND ((tbl_data.Familia) Like "*" & [forms]![frm_consulta]![fami_txt] & "*") AND ((tbl_data.Titulo) Like "*" & [forms]![frm_consulta]![titu_txt] & "*") AND ((tbl_data.Composicion) Like "*" & [forms]![frm_consulta]![comp_txt] & "*"))

2. provide the sql to the listbox

3. provide examples of the data

4. provide examples of what you might enter

5. provide the expected output based on 3 and 4 above

6. explain what you get instead

At the moment, the above sql is looking for data in a number of controls in a form called frm_consulta - none of them are called srchText which is what your vba code is populating

arnelgp · Sep 22, 2018

did you follow as instructed by Big Booty?
you need to put an Unbound Textbox (SrchText) to your form.
on this Textbox Visible property Set it to No.
you then based your query against this Textbox and use OR not AND:

Code:

SELECT tbl_data.ID_REGISTRO, tbl_data.Fecha_Registro, tbl_data.Proveedor, tbl_data.Codigo_SOLTEG, tbl_data.Familia, tbl_data.Titulo, tbl_data.Composicion, tbl_data.Apariencia, tbl_data.Acabados, tbl_data.Descripcion, tbl_data.Observacion, tbl_data.Estampado, tbl_data.Solido, tbl_data.Ancho_De_Tela, tbl_data.Densidad_De_Tela, tbl_data.Rendimiento, tbl_data.Encogimiento_Largo, tbl_data.Encogimiento_Ancho, tbl_data.Revirado, tbl_data.Precio_Blanco, tbl_data.Precio_Colores, tbl_data.Precio_PPT, tbl_data.Numero_OC, tbl_data.Fecha_OC, tbl_data.PD_OC
FROM tbl_data
WHERE (((tbl_data.Proveedor) Like "*" & [forms]![frm_consulta]![SrchText] & "*") OR ((tbl_data.Familia) Like "*" & [forms]![frm_consulta]![SrchText] & "*") OR ((tbl_data.Titulo) Like "*" & [forms]![frm_consulta]![SrchText] & "*") OR ((tbl_data.Composicion) Like "*" & [forms]![frm_consulta]![SrchText] & "*"))

Gasman · Sep 22, 2018

I don't believe that you should have all those ANDs without some ORs?
That search utility looks in all relevant fields

Code:

SELECT TBL_Wine.Wine, TBL_Grape.Grape, TBL_Vintage.Vintage, TBL_Winery.Winery, TBL_Region.Region, TBL_UNCountry.CountryName, TBL_States.State, TBL_Title.Title, TBL_WIneMake.FName, TBL_WIneMake.SName, TBL_Wine.WineID, TBL_Winery.WURL, TBL_Wine.Colour, TBL_Wine.Flat, TBL_Wine.Closure
FROM TBL_States 
INNER JOIN (((((((TBL_Wine 
INNER JOIN TBL_Grape ON TBL_Wine.GrapeID = TBL_Grape.GrapeID) 
INNER JOIN TBL_Vintage ON TBL_Wine.VintageID = TBL_Vintage.VintageID) 
INNER JOIN TBL_Winery ON TBL_Wine.WineryID = TBL_Winery.WineryID) 
INNER JOIN TBL_Region ON TBL_Wine.RegionID = TBL_Region.RegionID) 
INNER JOIN TBL_UNCountry ON TBL_Winery.CountryID = TBL_UNCountry.CountryID) 
INNER JOIN TBL_WIneMake ON TBL_Wine.WineMakeID = TBL_WIneMake.WineMakeID) INNER JOIN TBL_Title ON TBL_WIneMake.TitleID = TBL_Title.TitleID) ON TBL_States.StateID = TBL_Region.StateID
WHERE (((TBL_Wine.Wine) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_Grape.Grape) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*"))
OR (((TBL_Vintage.Vintage) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*"))
OR (((TBL_Winery.Winery) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_Region.Region) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_UNCountry.CountryName) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_States.State) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_WIneMake.FName) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*")) 
OR (((TBL_WIneMake.SName) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND ((TBL_Wine.Colour) Like "*" & [forms]![FRM_SearchMulti]![frame50] & "*") AND ((TBL_Wine.Flat) Like "*" & [forms]![FRM_SearchMulti]![frame43] & "*") AND ((TBL_Wine.Closure) Like "*" & [forms]![FRM_SearchMulti]![frame36] & "*"));

A pic of the query QBE is attached.

Dynamic multi search help (1 Viewer)

rchumpitaz

New member

CJ_London

Super Moderator

jdraw

Super Moderator

rchumpitaz

New member

CJ_London

Super Moderator

rchumpitaz

New member

CJ_London

Super Moderator

rchumpitaz

New member

CJ_London

Super Moderator

rchumpitaz

New member

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Gasman

Enthusiastic Amateur

Attachments

Similar threads

Users who are viewing this thread