Search forms on MS Access database

[wmaiwand]

Hi all,

I have a database that I've created a search form. Everything works great the only problem is the first time when the form is opened the search doesn't work, but after that it works. I've looked over and over at the code, there is nothing to trigger this problem. Can you guys think of something that I'm missing.

Thanks

Wana

[Rabbit]

What do you mean it doesn't work? What is it supposed to do that it's not doing?

[wmaiwand]

What do you mean it doesn't work? What is it supposed to do that it's not doing?

Hi,

No matter what I select on my search screen the whole data shows up. So the search doesn't work. but if I use the same search criteria the second time it work right. For example I have 342 total records the first time with my search all 342 records shows up but second time with the same search criteria the correct amount of records 88 displays.

thanks for your help

Wana

[Rabbit]

Did you put your code in the On Load event of the form?

[wmaiwand]

Did you put your code in the On Load event of the form?

No I have a search button on the form. The form has several list boxes and text box searches. Once the user selects info they will click on the search button to find it which doesn't work the first time. But I do have this code on the my form load event

Me.txtEnd.Value = Date
strEndDate = Format(CDate(Me.txtEnd.Value), "\#m/d/yyyy\#")

this is just to have the end date automated to today's date.

Thanks

Wana

[Rabbit]

So the first time they click the button it doesn't work but it works the second time they click the button? Did you use a Me.Refresh or a control.Requery at the end of the code?

If it still doesn't work, post your code so we can take a look at it.

[wmaiwand]

So the first time they click the button it doesn't work but it works the second time they click the button? Did you use a Me.Refresh or a control.Requery at the end of the code?

If it still doesn't work, post your code so we can take a look at it.

I don't use any of these codes, here is the code:

Option Compare Database
Option Explicit
 
Dim db As DAO.Database
Dim qryDef As DAO.QueryDef
Dim rst As Recordset
Dim SQL As String
Dim strStartDate As String
Dim strEndDate As String
 
Private Sub cmdSearch1_Click()
On Error GoTo Err_cmdSearch1_Click
 
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim strAut As String
    Dim strChe As String
    Dim strDoc As String
    Dim strLan As String
    Dim strOCR As String
    Dim varItem As Variant
 
 
    Set db = CurrentDb()
 
    strSQL = "SELECT * FROM FUELS"
 
    'Build the IN string by looping through the listbox
    For i = 0 To lstCompany.ListCount - 1
        If lstCompany.Selected(i) Then
            strIN = strIN & "'" & lstCompany.Column(0, i) & "',"
        End If
     Next i
    For i = 0 To lstDocType.ListCount - 1
        If lstDocType.Selected(i) Then
            strDoc = strDoc & "'" & lstDocType.Column(0, i) & "',"
        End If
     Next i
     For i = 0 To lstLanguage.ListCount - 1
        If lstLanguage.Selected(i) Then
            strLan = strLan & "'" & lstLanguage.Column(0, i) & "',"
        End If
     Next i
    'Create the WHERE string, and strip off the last comma of the IN string
 
    'Company - End Date
    If lstCompany.ItemsSelected.Count <> 0 And Me.txtAuthor = "" And Me.txtChemical = "" And lstDocType.ItemsSelected.Count = 0 And lstLanguage.ItemsSelected.Count = 0 And Me.txtEnd <> "" Then
        strIN = Left(strIN, Len(strIN) - 1)
        'strEndDate = Format(CDate(Me.txtEnd.Value), "\#m/d/yyyy\#")
        strWhere = " WHERE [Company] in (" & strIN & ") and [DOC_DATE] <= " & strEndDate & " "
    'Company - OCR text - Start date - End date
    ElseIf lstCompany.ItemsSelected.Count <> 0 And Me.txtAuthor = "" And Me.txtChemical = "" And lstDocType.ItemsSelected.Count = 0 And lstLanguage.ItemsSelected.Count = 0 And Me.txtOCR <> "" And Me.txtStart <> "" And Me.txtEnd <> "" Then
        strIN = Left(strIN, Len(strIN) - 1)
        'strEndDate = Format(CDate(Me.txtEnd.Value), "\#m/d/yyyy\#")
        strStartDate = Format(CDate(Me.txtStart.Value), "\#m/d/yyyy\#")
        strOCR = Me.txtOCR.Value
        strWhere = " WHERE [Company] in (" & strIN & ") and [OCR_TEXT] Like '*" & strOCR & "*' AND [DOC_DATE]>=  " & strStartDate & " and [DOC_DATE] <= " & strEndDate & " "
ElseIf ....
    End If
 
    strSQL = strSQL & strWhere
 
 
    db.QueryDefs.Delete "qryListSubForm"
    Set qryDef = db.CreateQueryDef("qryListSubForm", strSQL)
 
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenForm "ListSubForm"
 
    'Clear listbox selection after running query
    'Me.FilterOn = False
    Me.lstCompany = ""
    Me.txtAuthor = ""
    Me.txtChemical = ""
    Me.lstDocType = ""
    Me.lstLanguage = ""
    Me.txtStart = ""
    Me.txtEnd = Date
    Me.txtOCR = ""
 
 
Exit_cmdSearch1_Click:
    Exit Sub
 
Err_cmdSearch1_Click:
 
   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
        Resume Exit_cmdSearch1_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdSearch1_Click
    End If
End Sub
 
Private Sub Form_Load()
Me.txtEnd.Value = Date
strEndDate = Format(CDate(Me.txtEnd.Value), "\#m/d/yyyy\#")
End Sub

[Rabbit]

Put a Me.Refresh right after you run the search.

[wmaiwand]

Put a Me.Refresh right after you run the search.

I tried putting after the code, before the code every where but no same problem...

[grabit]

Hi all,

I have a database that I've created a search form. Everything works great the only problem is the first time when the form is opened the search doesn't work, but after that it works. I've looked over and over at the code, there is nothing to trigger this problem. Can you guys think of something that I'm missing.

Thanks

Wana

I dont know what your using php or asp but i have just built a search function in coldfusion and this is the WHERE statement from my query

WHERE topic LIKE '%#form.subject#%' OR topic LIKE '% #form.subject#_' OR topic LIKE '_#form.subject# %' OR topic LIKE '% _#form.subject#_ %'
OR username LIKE '#form.subject#'
OR posttext LIKE '%#form.subject#%' OR posttext LIKE '% #form.subject#_' OR posttext LIKE '_#form.subject# %' OR posttext LIKE '% _#form.subject#_ %'

Topic, username and posttext are the columns im searching:
'%#form.subject#%' looks for all strings entered
'% #form.subject#_' looks for words with a punctustion mark after them and a space in front of them
'_#form.subject# %' looks for the punct mark in front of the word with a space behind it
'% _#form.subject#_ %' looks for words with punct marks at either end.

then out put in your language as normal.

Need any more help giz a yell

cheers
Grabit

[Rabbit]

I noticed you didn't answer my first question "So the first time they click the button it doesn't work but it works the second time they click the button?"

[wmaiwand]

I noticed you didn't answer my first question "So the first time they click the button it doesn't work but it works the second time they click the button?"

yes,
When they click search button it opens an other form with the results. for the first time the results are not right it brings everything from the database, the second time the results are correct.

Thanks

Wana

[Rabbit]

Is there any code in the ListSubForm?

[wmaiwand]

Is there any code in the ListSubForm?

No I don't have any code in my Subform, it just displays the records from the query.

Thanks

Wana

[Rabbit]

Hmm.. I'm pretty stumped. Before I toss this out to the other mods and see if they can figure this out, try moving the "Clear listbox selection after running query" stuff before the OpenForm.

[wmaiwand]

Hmm.. I'm pretty stumped. Before I toss this out to the other mods and see if they can figure this out, try moving the "Clear listbox selection after running query" stuff before the OpenForm.

I don't have any answer to this, but yes this worked. As you said I moved the clear box selection to Form Open, and now it's working.

Thanks so much for your help.

Wana

[Rabbit]

Not a problem. I've noticed that when you have code after you open a new object things tend to get a little screwy. So as a rule of thumb have the opening of an object be the last thing you do in a sub/function.

[wmaiwand]

Not a problem. I've noticed that when you have code after you open a new object things tend to get a little screwy. So as a rule of thumb have the opening of an object be the last thing you do in a sub/function.

good idea thanks

[Denburt]

If you don't mind I just stumbled across this thread and wanted to point out a couple of things. I read the following two lines and it threw me. :)

db.QueryDefs.Delete "qryListSubForm"
 Set qryDef = db.CreateQueryDef("qryListSubForm", strSQL)
 

You should consider the following:

Set qryDef = db.QueryDefs("qryListSubForm")
qryDef.SQL = strsql
qryDef.close
set qryDef = nothing
 

You want to make sure you always close these connections or else you will leave the program consuming unnecessary resources, and when you close the DB you will notice a significant amount of ballooning.

Now on your issues with refreshing your control... If the following query "qryListSubForm" is the query that is in the subforms recordsource there is an occasion that you need to reset the recordsource of the subform.
However if it is work congrats if ya need anything else let us know.
me!Subform.form.recordsource = "qryListSubForm"
me!Subform.form.requery