Search Functions

[louisam]

Hi

I need to create a form whereby users can search for information from multiple forms, and then open a particular record within that form. I currently have a 'Main Menu' with buttons that open forms, but they always open on the first record and the only way to find the one you're looking for is by going through each one. So my questions:

1) How do I create a search button on the main menu that will search within each form and open that record/s?

2) How do I create a search that will search for non-exact matches? For example - search for 'Grange' and it will open '2 Grange Place' and 'Grange House'.

Thanks, Louisa

[gnawoncents]

louisam, the second part is easy. If you are using a text box for searching, then having the user press a button to initiate a search, you could simply add wildcards (*) to the beginning and end of the search string. Something like:

dim strSearch as String
strSearch = "*" & me.ctlYourTextBox & "*"
 

You would then be able to use the string (strSearch in the example above) to find matches containing whatever was typed into the search box.

Regarding the first question, the way to do this is to use VBA to cycle through each of the records on each form in turn. If there will ever only be one match, you can simply stop when it is found and run the code to open that form on that record. If there is the possibility of having more than one match, you'll have to decide how you want to handle that.

The Bytes article on Finding a Specific Record Programmatically can get you started. https://bytes.com/topic/access/insig...ogrammatically

There are also lots of other examples on how to create a search to find the record you're looking for.

[PhilOfWalton]

The first part is very vague as to what you want. Are you implying that you have a form on which you select a record (Using a combo box?) and then when you open any one of a number of forms from your Main Menu, you want that form to open at the selected record?

Phil

[louisam]

Hi gnawoncents
Thanks for this but I have no idea how to do that! I'm a very basic computer user and so where do I put that code?! What's a VBA?!
Thanks!

Hi PhilOfWalton
Ok, so I have a form as a main front page that users will automatically see when they open access. On here I was trying to add search boxes to look into each of the forms that I've created for different information (Properties, Contacts, Tenants, Applicants). However the main page form will only allow me to search for exact matches and only use one set of data from a table. I need it to be able to search for non exact and multiple tables.

All and any help much appreciated, thanks!

[PhilOfWalton]

It's really difficult to understand what you want. Are you suggesting a text box in which you type something, and if it approximately matches any field in one of your tables a form will open showing that record? What do you want to do when it partially matches fields from more than one table? Open all the relevant forms?

Surely you know whether you are looking for information from your properties, contacts, tenants or Applicants.
Is there a relationship between these tables?

Gnawoncents has given the correct coding for a search.
VBA is Visual Basic for Applications and is code that is pretty essential for Access applications. Basically they start a chain of events when you do something. For Example having entered entered something in your search box, pressing the Enter Key starts the searching process.
You can also use Macros, but they are not as versatile as VBA and much harder to find out what is going wrong, so I strongly advise against them

Phil

[louisam]

Hi
Yes, a text box in which I can search for a particular property, contact, tenant or applicant. I'm presuming that there will need to be 4 different text boxes in order to search through the 4 different tables. Some of the names of these can be quite long so rather than having to type 'Channel Island Toys' every time it would be handy to just type 'Toys' (as it happens they are the only tenant with 'Toys' in their name.

There are lots of relationships between the tables - the property table will have the name of the tenant (and vice versa), the tenants will have the name of the main contact, the contacts will be the owners of the properties etc.

I am sure that the coding is correct - but how/where do I type it?!

Louisa

[PhilOfWalton]

OK, I don't like it because if you have 4 text boxes, 1 to open each form, you might just as well open the form anyway.

If you insist
Let's call the Text boxes TxtContact, TxtTenant etc
Lets call the corresponding forms FrmContacts, FrmTenants etc.

So here is an example of the code (I'll explain it in a moment

Private Sub TxtContact_AfterUpdate()
 
    Dim Fltr As String
 
    Fltr = "ContactName like " & Chr$(34) & "*" & TxtContact & "*" & Chr$(34)
 
    DoCmd.OpenForm "FrmContacts"
    Forms!FrmContacts.Filter = Fltr
    Forms!FrmContacts.FilterOn = True
 
End Sub
 

The first line builds a filter and if we are looking for a text value, we must surround it with double quotes.
So if you typed Toys in the text box, the Filter would be
ContactName Like "*Toys*"
That would find anything with Toys in the ContactName

The other lines open the contact form and apply the filter

Phil