How can I create a user-friendly search

[Frederic74]

Hi, I am pretty new to Access, so forgive me if this is a simple problem.:

Here is what I want to do:

I have a equipment database with:

EquipmentTbl – pkeyEquipmentID; pkeyCategoriesID; strCategories; pkeySubcategories; strSubcategories; strItemDescription; strSerialNo; strModelNo; strCurrency; strQtyIssued; strPurchasePrice; strPurchaseDate; strSupplier; strProjectOffice; strLocation; strPurchaser; strStatus;

CategoriesTbl – pkeyCategoriesID; strCategories; pkeySubcategories; strSubcategories

SupplierTbl – pkeySupplierID; strsupplier

ProjectOfficeTbl – pkeyProjectOfficeID; strProjectOffice

LocationTbl – pkeyLocationID; strLocation

PurchaserTbl – pkeyPurchaserID; strPurchaser

StatusTbl – pkeyStatusID; strStatus

I would like to create a form where Access would search for records that match criteria from comboboxes/listboxes I have based on the last 6 Tables from above. I would like that the search can be done from only one ComboBox/ListBox or some or all. However I would also synchronise them in order to limit results. I.E SerialNo being limited by Subcategory, which is limited by Category. I would like the results to be shown in a subform, which is part of the form.

I have tried to do it based on the example by Microsoft Access Database Solutions – Searching Records using a Microsoft Access List Box. However Access doesn’t accept …….. Me!stSearch.Column(0) & “’”

I would really appreciate if anybody can help. Thanks

 

[scottian]

set the criteria of your query to that of the field on your form. then on the click of a button refresh the subform

"My God! It's full of stars...

 

[Frederic74]

I am sorry, but as I said I am pretty new to Access. I Tried to do pretty much what the following Microsoft example tried to do:

Microsoft Access Database Solutions - Searching Records using a Microsoft Access List Box
In an earlier article, we discussed Using a Microsoft Access Listbox to pass criteria to a query and also discussed the reasons and benefits of using List boxes. The obvious benefit is that you can allow the user to pick multiple records to then do something with.
In this article, we demonstrate how we can use a list box to pass criteria through to a search, allowing us to open a form (in this case) and return a specified result. This is similar to using a combo box to perform the same action.
Create the List Box Search Form:
Initially we need to create the form, this will include the unbound list box which will hold the results that we will base the search on. The form should also include a search button and a cancel button.
Below shows how the form should appear:

The form containing the unbound listbox, show record and cancel button
The listbox gathers it's data using a row source type of Table/Query and is based upon the following statement:
SELECT tblSalespersonContact.strSalespersonID, tblSalespersonContact.strSalespersonFirstName, tblSalespersonContact.strSalespersonLastName, tblSalespersonContact.strRegion FROM tblSalespersonContact ORDER BY tblSalespersonContact.strSalespersonID;
This populates the listbox with the values that you can see. Although the listbox only shows three columns rather than the four defined in the SQL statement, this is due to the column width property of the listbox being produced as follows:
0cm;3.995cm;3.995cm;3.995cm - this hides the first column (strSalespersonID) as we do not need to see this.
You will also see the Show Record command button. This is what can be used to run the search (as well as being able to double click on the record in the list), and you will note that this is disabled whilst a record is not selected. To set this property, the button has it's Enabled property set to NO. It is a good idea to disable this button until a user selects a value from the list to search on.
To enable this button, the following code is added to the List Boxes (lstSearch) After Update event:
Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub
Once a value is selected in the list, the button then becomes active:

Show Record button enabled after selection of value from the list
Once the user has selected a value from the list, they can either click on the Show Record button or Double Click on the record in the list. This action will run the following code:
Show Record Button:
Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box

DoCmd.OpenForm "frmSalespersonContact", , , _
"[tblSalespersonContact.strSalespersonID]=" & "'" & Me.lstSearch.Column(0) & "'"

'Close the dialog box
DoCmd.Close acForm, "frmListBoxSearch"

End Sub
This routine will open the new form (frmSalespersonContact) where the SalespersonID (strSalespersonID) mathces the ID of the value chosen in the list box (Me.lstSearch.Column(0)). This column is the hidden column as detailed above.
Once the new form is displayed the Search form will close: DoCmd.Close acForm, "frmListBoxSeach"
Double-Click Method:
Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub
This routine checks to make sure that a value is selected in the list box (If Not IsNull (lstSearch)). Obviously there must be a record selected to allow the user to double click, then it will run the ShowRecord_Click event procedure detailed above.
To see this List Box Search in action please download the Microsoft Access 2000 example file from either the Forms page or Downloads menu.


Need further information...why not visit the Microsoft Access Database Solutions OnLine Shop



I have based my frm on this example the list boxes made out of my tables to then show specific records from my EquipmentTbl. However it access doesn't allow me to do: & Me.lstSearch.Column(0) & "'"

I hope that is clearer. Thanks

 

[scottian]

ok forget that.
lets start friom scratch

create a new form, unbound. that is to say it is not based on a table. put a drop down box on your form which draws its source from one of your tables.
now create a query based on the same table select all of the fields. in the field which your drop down box is based right click in the criteria section and choose build. use the build wizard to navigate to your form and set the criteria to the drop down box. now create a new form with a control source of the new query youve created. save the form.

on the original form create a button with the wizard to open the second form youve created.
now select something from the drop down box and click on the button.

badabing

"My God! It's full of stars...