Search Form (1 Viewer)

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
Hello, I am unable to upload my company database so I replicate something similar (with lesser fields). My company asked me to do a search form for the database. I have done the basic search form but I need some help with the followings which I have stuck for weeks.

1) When user open the search form, I do now want to display all results in the subform before user do any search
2) Before search, user click on search button and all result will be displayed. How do I disable it (The one circle in red, in the image file)
3) I tried figuring how to allow user to search with one field and when no result found, it will prompt user that no result found.
4) Any ways to disable user from copying out the record from the search

Sorry if I ask too many questions as I am totally new to MS Access and with limited knowledge.
 

Attachments

  • Search Form.accdb
    560 KB · Views: 325
  • image.PNG
    image.PNG
    5.4 KB · Views: 299

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
I presume you mean 'not want to display...' not 'now want to display...'.

1. change your form recordsource to include WHERE False (or as you have in your clear button code )
2a. in your if statements in the search code, change strText = strText & " AND Address Like '" & Me.Address & "'" to strText = strText & " AND Address = '" & Me.Address & "'" (otherwise a use could just enter '*' to get all the records
2b. at the bottom of your search code use

Code:
If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        Me.SearchSubForm.Form.RecordSource = strSQL
else
    btnClear_Click
End If

3. only way to do this is some code to check the number of records found before changing the recordsource - but isn't it obvious since no records will be displayed?

4. Not easily - it is a windows functionality, you would need to temporarily disable.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:48
Joined
Jul 9, 2003
Messages
16,244
I have a product "Nifty Search Form" which I think is suitable to answer your question. For more information about the "Nifty Search Form" see my blog here:-

 
Last edited:

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
I presume you mean 'not want to display...' not 'now want to display...'.

1. change your form recordsource to include WHERE False (or as you have in your clear button code )
2a. in your if statements in the search code, change strText = strText & " AND Address Like '" & Me.Address & "'" to strText = strText & " AND Address = '" & Me.Address & "'" (otherwise a use could just enter '*' to get all the records
2b. at the bottom of your search code use

Code:
If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        Me.SearchSubForm.Form.RecordSource = strSQL
else
    btnClear_Click
End If

3. only way to do this is some code to check the number of records found before changing the recordsource - but isn't it obvious since no records will be displayed?

4. Not easily - it is a windows functionality, you would need to temporarily disable.
Thank You, yes, is my typo error.

For point 3, my concern is when my other department not aware then they will think that there is some error with the database.
For point 4, noted on that.

Just wondering, if it is possible to do an audit trail for search? Like what the user search? I have an edit log which captured what the user edited.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
for point 3 - declare a recordset, assign the sql to that then check the recordcount before assigning to the form recordset -- something like

Code:
dim rst as dao.recordset
...
...
...
If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        set rst=currentdb.openrecordset(strSQL)
        if not rst.EOF then
              set SearchSubForm.Form.Recordset=rst
        else
          msgbox "no records found"
        end if
else
    btnClear_Click
End If


re audit trail - yes possible. To your audit log post userID, datetime and the strText value and perhaps other information such as the form name or the table searched
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
for point 3 - declare a recordset, assign the sql to that then check the recordcount before assigning to the form recordset -- something like

Code:
dim rst as dao.recordset
...
...
...
If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        set rst=currentdb.openrecordset(strSQL)
        if not rst.EOF then
              set SearchSubForm.Form.Recordset=rst
        else
          msgbox "no records found"
        end if
else
    btnClear_Click
End If


re audit trail - yes possible. To your audit log post userID, datetime and the strText value and perhaps other information such as the form name or the table searched
Code:
DoCmd.RunSQL "INSERT into searchlog ([staffname], [datetime], [search_input]) VALUES ('" & [TempVars]![tvarStaffName].[value] & "', '" & Now() & "', '" & strText & "')"

I keep having error "run-time error 3075". I already have a tempVar for the staff name. My search log table have staffname, datetime and search input.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:48
Joined
Sep 21, 2011
Messages
14,044
I keep having error "run-time error 3075".
And that error description is.......?

Dates need to be surrounded by # and in either mm/dd/yyyy or yyyy-mm-dd format.
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
And that error description is.......?
Code:
DoCmd.RunSQL "INSERT into searchlog (staffname, [datetime], [search input]) VALUES ('" & [TempVars]![tvarStaffName].[value] & "', Now() , ' strText ' )"

I changed to this sql and there is no error. But the searchinput, the value is capture as strText instead of the text that I searched
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
you need to be clear about what you actually want - provide an example scenario of what 'text that I searched' means' and why strText does not provide that.

Edit - think I understand, you need to separate out the string to bring through the value of strText

"INSERT into tbl_search_log (username, [datetime], [search input]) VALUES ('" & [TempVars]![tvarUserName].[value] & "', Now() , '" & strText & "' )"
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
I tried to do a search on studentID (input value: 1001) and it did show the result in the subform.
For the search log, it saved strText instead of 1001 (image attached)
 

Attachments

  • result.PNG
    result.PNG
    7.6 KB · Views: 286

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
I updated my post. Be aware if you only look at what you receive by email, it often does not include the full response. Always better to return to the thread which also means you will see other responses
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
Earlier i tried that insert statement and it have error
 

Attachments

  • error.PNG
    error.PNG
    20.8 KB · Views: 231

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
OK, it's because you are using single quotes around 1002, which is a number so shouldn't have them anyway (unless the studentID field is text?). But you would have the problem with studentname anyway so you need a solution

try replacing the single quotes with two double quotes

""" & strText & """)"
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
OK, it's because you are using single quotes around 1002, which is a number so shouldn't have them anyway (unless the studentID field is text?). But you would have the problem with studentname anyway so you need a solution

try replacing the single quotes with two double quotes

""" & strText & """)"
I changed to two double quotes and it saved the search input (input value: amy) as

1 AND [Student Name] = 'amy'

For my actual database, the student ID will be something like "MAG1001"
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
Yes, in a way, but I does not want to display this "1 AND [Student Name] ="

Edit: I managed to resolve it temporarily by creating table fields for individual search textbox.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 19, 2013
Messages
16,553
Use the replace function or build another string like strtext but just the values
 

poh99

Member
Local time
Today, 22:48
Joined
Sep 16, 2021
Messages
33
Thanks for the tips.. will give it a try on that function.
 

Users who are viewing this thread

Top Bottom