Search Function

[Lance55]

Hi
I need to write a search function for my access database.
I have an order form on which I have two text boxes labelled First Name and Surname I would like the user to be able to enter text into these boxes and using a command button be able initiate a search of the Customer Table for the Name, finding only the record which has the first name and surname which the user entered. I need the results to be displayed so that the user can click the correct one and the fields on the order form will be filled out with that customers details.
I'm relatively new to VBA and any help would be much appreciated.
Thank You
Lance

 

[agehoops]

Here ya go, see if you can make sense of this, if not let me know and i'll explain how it's done. It's a search feature i use on all my systems. Easy to use but quite powerful and customisable

View attachment SearchDB.zip

 

[Oldsoftboss]

Good stuff agehoops, here is a similar sort of thing

 

[Lance55]

Agehoops could you explain how it works please? I don't understand it. Its definitely they type of thing i'm trying to do.

 

[agehoops]

sorry if it seems quite confusing. Ok basically you have one query for every type of search you want. For instance, if you want to be able to search for First names, then setup a query which searches the user/client/customer or whatever table holds the data. The criteria of this query is the First name criteria, and is based on the field on the form where the user types in their search. (same thing is done for other types of searches such as surname, dat of birth, anything you like, just create a query, and set the criteria to the control of the form). NOTE. MAKE SURE TO INCLUDE THE ID NUMBER.

Then have either a command button for each field, or as in this example a drop down box with a list of values stating which field to search on. (I'll explain the example used here)

In the OnClick even of the command button, the code checks to see if a selection has been made in the combo box or if something has been entered into the search criteria. If one of them is empty, it shows an error message stating this, and does not continue. If they both have a value, then it checks the value that is in the combo box.

If for instance the combo box's value is First Name, then an IF statement (could be CASE as well) runs.

Now that it is able to check, it needs to know what to do. As earlier, with the queries set up, you need to also create a form for each search. In the form, insert a list box, and base this list box on the query of your search.

Now in the main form, when the button is click, the if statement needs to check the combo box selection, and then simply open the appropriate form.

In the results form that is then opened, you will need to set either an OnClick or OnDoubleClick even on the list box. This should simply take the value chosen in the list box (will be the ID field from the query as default if it is the first one, make this so for ease) open the main form, and goto that ID number as the record.

All of the code for this is in that sample, so hopefully this will make it slightly easier to understand. If you are still unsure about anything then just let me know. Happy to try and explain it a bit more clearly

Aidy

 

[Lance55]

Thank you for explaining I think I get everything apart from the ID part in the first paragraph of your post. I've tried to implement your search for my access database however when I perform a search the record is found but when I click it, it returns the error "Field ID is not in the current record". I've tried to get it working to no avail. I've uploaded my database if you want to take a look at it i'd be very grateful. Thanks
Lance

 

[agehoops]

That's fine, everything is setup properly, the only slight problem there was in the OnClick code of the list box. All that was happening is that you had used the exact code from my example, and told it to go to the control "ID" on the main form, however, in your database, the main form does not have a control called "ID" and is instead called "CustomerID" so the error was simply saying it could not find a control called ID. I've changed this and updated it for you so hopefully this will work

View attachment Company.zip

hope this helps

 

[maurice0822]

I like how you build the search function. I am new to acess and need help. Can you help me?

 

[agehoops]

yea of course i can. What do you need help on in paticular? you can PM me about it if you want

 

[gear]

Thank you very much for posting your sample DB which gives an excellent example for searching multiple fields. This is what I was looking for. I have adopted this format for my database as under


Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "frmSearch"

Set rst = Forms!frmSearch.Recordset.Clone

rst.FindFirst "[DyNo] = " & Me.SearchList
Forms!frmSearch.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Name

Exit_SearchList_DblClick:
Exit Sub

Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick

End Sub

I need a little more from this. On double clicking the identified record list, the Advanced Search form closes and the selected record is displayed in frmSearch (frmSales in the sample given by you). What I want is that the Advanced Search form should stay in the background so that if the data displayed is not the one wanted, one can see other identified records from the list. I tried to modify the event procedure but it is not working. Could you please help me how to modify the above code so that the SearchList (AdvancedSearch) doesn’t disappear. Grateful for help.

 

[agehoops]

Simply remove the line DoCmd.Close acForm me.name

That line is telling it to close the form, so you can either remove it or change it to minimize or hide the form or however you want, but by removing the line it'll just be kept open behind the form you're returning to

 

[gear]

Many Thanks. I got it.

 

[agehoops]

Brilliant! Any time, am here to help

 

[svtguy02]

search functionality!

Hi there, I'm rather new to MS Access and have downloaded a template ticketing system thingy. It comes with some prebuilt in search fields and a nice form, although I added some new fields to the 'Issues' Table.

When I open up the 'Issues' form... I am able to input data into the new fields that I added in the form, and they get added properly to the Table. That part works.

Now I would be able to add search fields to the Search Form to be able to search for data using those fields.

I checked out your sample up above and have created a seperate query for each field. got one query for serial number, 1 query for case number and they work when you double click the query, although I'd like to be able to search for data using the Search form, and not have to double click the query each time I want to search for something.

Currently, I'm able to search for TITLE in my current search form and it pulls up results that are relative to the content in them.

I create the same text box and type in a serial number that is contained in the Issues table, and it just displays ALL entries in that table. Any idea on how to get this working ?

I've attached the DB file i've been working with to show you an example of what I mean.

 

[agehoops]

I assum you're using the Search Issues form?? If so then i don't think you'll need a query setup for each new field. If you look at the code that is behind the search button on the OnClick event, it shows you basically what it is doing when it searches for every other field. All you really need to do is copy the code that is there for the 2 new fields and it should run as it currently is, just searching the 2 extra fields.

 

[svtguy02]

ok ok... I see where to change the function.

It's in the SEARCH BUTTON under build event that I need to change it! I was looking around in properties wondering why the hell it doesnt work or thers nothing to change!

K, I added the following:


' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title & "*'"
End If

' If SerialNumber
If Nz(Me.SerialNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.SerialNumber Like '*" & Me.SerialNumber & "*'"
End If

' If CaseNumber
If Nz(Me.CaseNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.CaseNumber Like '*" & Me.CaseNumber & "*'"
End If



And here is the ENTIRE file contents:


Option Compare Database
Option Explicit

Private Sub Clear_Click()
DoCmd.Close
DoCmd.OpenForm "Search Issues"
End Sub

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Assigned To] = " & Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " & Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category & "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority & "'"
End If


' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " & GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " & GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] <= " & GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title & "*'"
End If

' If SerialNumber
If Nz(Me.SerialNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.SerialNumber Like '*" & Me.SerialNumber & "*'"
End If

' If CaseNumber
If Nz(Me.CaseNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.CaseNumber Like '*" & Me.CaseNumber & "*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer)

End Sub

================================
================================


When I type something in serial number and click SEARCH it pops up a "Enter Parameter Value" box and it says Issues.SerialNumber in with a txt field. Say I enter the serial I'm looking for, it still won't find it.. it just pops up that same box again. Any Ideas? (Sorry, I'm total newb to Visual Basic! )

 

[agehoops]

ok looking at the database and the code you've added, i'd say it's just that you've got the field names wrong for the code.

' If SerialNumber
If Nz(Me.SerialNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.SerialNumber Like '*" & Me.SerialNumber & "*'"
End If

' If CaseNumber
If Nz(Me.CaseNumber) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.CaseNumber Like '*" & Me.CaseNumber & "*'"
End If

In there, you don't have a field on the Issues Search form named SerialNumber or one called CaseNumber, so the system doesn't know what field it is looking for. Either you'll need to rename the two fields to those names (easiest way) or change the code to match the names the 2 fields currently have.

 

[svtguy02]

Cool.... That worked! I changed the names of the fields in the Issues Table, removed the space so that they point to each other. Ran a search and the results came up beautifully!

Now I'm going to be needing something with the authentication system of this application. Dare I go there? Judging by what I've seen, the authentication in Access sucks.

I'd like to be able to give MY Group of guys write, modify access.... basicaly admin access. I'd like to also give another group of guys READ ONLY access.

Have the ability to run searches, view stuff only; not write anything or modify or change anything.

Is that possible at all??

 

[agehoops]

excellent! Glad to hear it's all working

Yea that's all possible. You'll be looking at a lot of coding but it's definitely possible. i've just implemented something almost identical to that myself Been doing this all for a school project we've been given Give this a go, i've removed all the stuff from the system except the entire login script

If you go into the admin form there is a permissions form from there, which is a series of tick boxes which specify the permissions. A lot of those tick boxes won't change much in this example, but some of them will affect what that user can see on the main switchboard. Just fiddle around and test them.

In this example i've got 4 user accounts setup. These are the passwords for each one:

Admin - test
carer - test2
other - test3
RGN - test4

You can get them from the table tblEmployees if they're wrong.

This is basically (i think) what you're looking for, you'd just need to alter it slightly. It does get quite confusing to set this one up (certainly easier with access's built in commands however this is MUCH more robust i personaly think)

But yea, give this a go, see what you think and let me know. I'll give you help with it if you need it

Aidy

View attachment Login Script DB.zip

 

[svtguy02]

very very very very much appreciated! I really like this logon window that pops up, although wen i click EXIT DATABASE.... I'm still in the DB! haha

Also, alot of things I try to do say that mousehook.dll is missing.

Other than that, I checked out your authentication thingy and it looks very nice.... very detailed as well.

I'm going to be needing something that basicaly is only 3 priviliges... Read... Write... Modify.

so you're either Reading ALL or Modifying and Writing to ALL, depending on your username

Usernames I'd want would be:

admin - full access
HSS - full access
RSA - read only access

any thoughts ?

keep in mind I do not know Visual Basic.... if you know of any web resources that can point me to getting a simple logon script going, that would be great!

I want to be able to login using a username, and a password.... rather than just a universal DB Password