Create search form in Access

[ashjones86]

Hi All,

So im looking for some help with access 2007 if at all possible.
I am new to access as work has asked me to create a database, so the database side to it i have sorted i.e inputting data (google and youtube helped me with this).

But now i want to create a search function for inputted information.

In this instance, i want to be able to search 1 search term and get all the results linking to that, for example, i want to search a company car registration number and from that, i want the MOT, TAX, Service information that has been inputted, and from all the searches i have done, i cant find anything to help me with this.

If anyone could provide any assistance i would be truly grateful.

Thank you in advance.

Ash.

[jimatqsi]

Welcome to Bytes.com.

Perhaps you will find this article helpful
http://bytes.com/topic/access/insigh...filtering-form

Jim

[ashjones86]

Hi Jimatqsi,

Thank you for that, in honesty that doesnt make much sense to me, again im very new to access, this is my first time using it, so in terms of where to enter code (vb is it?) i have no idea, if at all possible, if anyone can essentially hold my hand through it to give me the best understand of the steps to take, that would be great, ill continue to read that link, but gone through it a few times and im going in circles, thanks again though.

[twinnyfo]

Mods, forgive me for redirecting to a non-Bytes.com site....

There are other tutorials out there, but here is one: MS Access 2010 Tutorial.

I can do a lot of hand-holding on this forum, and I am willing to help as much as I can, but it looks like you need some basics first.

[ashjones86]

Hi Twinnyfo,

Thanks for that, i will have a look into it.

In terms of basics, i have gone through a lot, already completed the entry end of the data base, and i can filter results using the search facility the button creates, essentially ctrl F, but ive seen some scripts knocking about that pull data from various tables, originally posted on a topic from 2007 where someone had helped an individual out on the same issue, but it was for access 2003 and apparently it didnt cross over to 2007, sadly my post was deleted by mods for posting on a old topic ,hence this post.

So in some respects i have basic understandings, just getting lost with scripts.

[twinnyfo]

ash,

Do you know how to add any VBA to a form? In much older versions of Access, some of the wizards would create some basic code for routine operations, but now everything defaults to embedded macros, which are inherently limited.

If you have your DB open, hit Alt-F11 and this will bring up your VBA editor. It will probably be blank, as I can guess that you probably don't have any code.

To fix this, let's give you a basic introduction to VBA:

Open a form in design view (you can use an existing form or create a new one--it don't matter in this case). In the FOrm Design Tools Menu, on the Design Tab, select "Button" (a rectangle with "xxxx" in it). Click anywhere on your form, and a dialog box should pop up. Rather than using hte wizard, just click cancel. If the "Properties" list is not currently displayed for your button, right-click ont he button to bring it up.

On the Event Tab, in the On Click field, click on the Drop down and select [Event Procedure]. Then click the "..." to the right of the field. This will take you to your VBA editor. This is called a "module", so anytime anyone refers to a "module" you know what they are talking about.

The first thing I want you to do is to make sure that the following words are at the top of your VBA Code:

Option Compare Database
Option Explicit

If they are not, put them there now. Also, you will want to make sure these words are at the top of all of your modules by default, so in order to do this, click on the Tools Menu, then select Options. On the Editor Tab, make sure that all the checkboxes here are checked. Many of these are just useful tools, but hte most important one is "Require Variable Declaration". This means that in order for you to use any variables in your code, you must declare their existence somewhere before you can use them--this is just good programming technique, and I don't know a single coder out there who thinks that MS Access's default of having it turned off makes any sense!

Now, I can guess that the rest of your module looks similar to this:

Private Sub Command0_Click()
 
End Sub

Correct? Let's talk about this.

The word "Private" indicates the beginning of a set of code and also indicates that this code will only work within the confines of this Form's module. If you replaced this word with "Public", you could use the code elsewhere. Typically, we keep things on forms private, but there are cases in which things might need to be public, such as if you want to cal the code on one form from another. Not your concern right now.

The word "Sub" indicates that this is a sub-routine, which is a type of procedure. There is also "Function"--and some of the other big brains on the site may inform me that there are others, but I don't know of any. The big difference between a "Sub" and a "Function" is that a sub just executes code, whereas a Function can actually return a value for you. Other than that, they function identically.

The "Command0_Click" indicates several things. First, the "Command0" is the actual name of the control to which this code belongs. In this case, because it is a default-ly names command button, Access has named it "Command" and sequentially, starting from 0. If we were to do the same thing again, we would create a control called "Command1". Then a Text box and it would be called "Text2" (with an associated Label, called "Label3". A form can have a maximum of 1024 different controls over its lifetime. Believe it or not, I once had a form (or a report) that reached this max! If you don't actually have 1024 controls on a form, but have reached that max because of adding and deleting, you can just create a new form and copy all the controls over to it--more than you need to know right now, but just some more useless information....

The "_Click" indicates that this procedure executes when a user clicks on the control. Since this is a button, this makes sense. When we click a button, we want something to happen, right?

The "End Sub" is merely an indicator that the code should stop running and everything we want to accomplish is done.

Now, add the following into your code in the blank line before the "End Sub":

    MsgBox "It Works!"

Close your form and save it, and then double click to open your form. Click the button you just created, and you should get a pop up!

Here is your introductoin to VBA!

Now, back to your original question. It is nearly impossible for us to guide you through this without knowing something about your DB. It sounds like you know very little about DBs, which means your communication to us may be limited.

Keep in mind How to ask Questions and the Posting Guidelines as we work through this.

We will not just "do it for you" and we expect you to put forth some effort. Obviously you want a solution, and we can help you with that, but you should be doing the majority of troubleshooting. We can guide you to solutions.

Hope this helps, for now. Keep plugging away, and if you have a specific question we can work through, we can address that.

[ashjones86]

Before i continue my way though this, thank you for the effort you are putting in here, it is greatly appreciated, and im not looking for someone to make it for me, im a web developer, and know the frustrations of coding for someone, if i wanted that kind of service, i would pay a dev ;) , that said i want to learn vb and work my way through this, these guides written like this help me a lot and i extend my gratitude to you, shall continue working through this and get back to you, thanks again.

[Slaxer13]

Hi ashjones86.
I think this maybe is what you are looking for...
I found this when i was looking for my own db. Hope it helps you.
Cheers, Slaxer13

[ashjones86]

Hi Slaxer,

Shall take a look once i have worked through twinny's post, thanks very much mate :)

[ashjones86]

Hi Twinny, thanks again for that post, so i did as you said and my code looks like this

Option Compare Database
Option Explicit
 
 
Private Sub Command17_Click()
MsgBox "It Works!"
End Sub
 

Saved, closed and re-opened but no pop up, is it likely im missing something ?

[twinnyfo]

Did you click the button you created? That will fire the popup.

BTW, I just use this as an introduction for you to see that you can code! Start with baby steps!

[Slaxer13]

Happy to help Ash ;)

[ashjones86]

Sorry yes i did click, no pop up.

[twinnyfo]

Well, then! Let's introduce you to VBA troubleshooting! We get two classes in one day!

First, is your DB fully enabled? Are there security settings on your network/machines/applications that prevent Access from running its code? If this is the case, you must get these fixed first! IF this is not the case, let's continue, below.

In your VBA editor, You should see a plain gray margin just to the left of your code. Take your mouse and place it just to the left of "Private Sub Command17_Click()" and single-click.

This should put a large brown dot to the left of the line and highlight the entire line. This is called a break point. If some of the foilks on this forum ask you to "Add a break point to..." now you know what they are talking about. We will demonstrate below.

Add another break point for the line "MsgBox "It Works!""

Now, close your form. Open it again and click the button. One of two things should happen:

1. Nothing - this means that somehow, your button is not fully associated with this code, which can happen, but it is very strange that if you did what I just demonstrated, that it would not work.
2. Your code will now be highlighted yellow at the first break point. Nothing will happen at this point, because it is waiting for you wo watch what happens. You can hit F8 at this point to move to the next line of code or F5 will move to the next break point (in this case, both buttons do the same thing).

If Number 1 happens, then we have to make sure your button really does think it has code assigned to it's OnClick Event. Close the Form and open it again in Design view. Double click your button (Command17) and make sure that "[Event Procedure]" is found in the On Click proerty in the events tab.

If Number 2 happens, then click F5/F8 and it should go to the next line (you must make sure that the VBA editor is the highlighted application). Then, the second line should be highlighted. When you hit F5/F8 again, you should get a message box.

We'll make you an expert coder yet! :-)

[ashjones86]

Twinny,

I like these lessons ! again thanks for the effort and the patience, greatly appreciated !

So it turns out it was something simple, there was a security option to enable the content in the db, once i clicked enable, it then run the code and i go the "It Works" Message.

Is there any steps i should be taking now to further look into my search function, Btw Slaxer13, i looked into that link you posted and that is the kind of thing im looking for , just need to find a way of making something similar work for me without plagiarizing the code.

Again thanks to everyone who has helped, seems a great community this, i will be sticking around...if you'll have me of course !

[Slaxer13]

That code was the base of mine, of course i had it modified because copying full codes its not the best way to go and modifying them is always a good way to learn a few more things for us new to access and vba ;)
Cumps, Slaxer13

[twinnyfo]

ash,

All code is plagiarized to some more or lesser degree. But, slaxer is correct. Review the code posted, look at how it would be incorporated into your code and proceed from there.

In my experience, the MS website provides the absolute minimal basics for their VBA. However, they never (EVER) provide any information on how to actually USE anything. It is very frustrating. On the flip side, this forum has actual users and programmers, some of whom have been doing this for a very long time (I've been involved in DBs in some way for close to 20 years, now). We've done a lot and have, more likely than not, come across your same problem at least once. At the same time, I learn new stuff on this site every day!

[ashjones86]

Good Morning all,

So i have been looking into the code Slaxer13 sent me, and the following line of code.

Private Sub btnSearch_Click()
 
    ' Update the record source
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
 
    ' Requery the subform
    Me.frmsubClients.Requery
End Sub

Would the line

Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

be where the query is called from ? so for example if my query is called Vehicles Query would i change qryClientData to Vehicles Query ?

[aleciagibbons14]

thanks for these allow me also to try these shared idea

[Slaxer13]

Good morning ppl,

If i am not mistaken that line is where it selects all from the query. The following requery makes it only show the records that are filtered with your search criteria. (I have only worked really with access for a few weeks so forgive me for any mistake)

[ashjones86]

Its ok Slaxer, this is my 5th day with Access so i have no idea by comparison, so the table that link you posted shows in design view as a form, im trying to find a means of putting my current table there and change the search options to match that, i have found the search names in the code, so i can change those to match those in the table, but where to add the table instead of the one that is there is baffling me, this is kinda fun though....in a hair pulling kinda way :)

[Slaxer13]

If you want i can see where i change my code to fit my db and put the code here in bold so that you can see where to change.Oor if you prefer you can try it and if you can´t do it i'll post it.

[Slaxer13]

I have created a search button in my main form that opens a search form, in which i have two combo boxes and one text box. One of the combos has the criteria (in my case it has Name, NIF, Address and Phone Number), the other combo has only one field that i put there manually that says "Contains" and the text box is where you input the data to search. Right now i am trying to do the opposite. I am trying to make the "Doesn't Contain" wich will search all records except those that have that field. But it's still a "book not yet ready for release" xP

[Slaxer13]

Ash it seems we can only send three pm a day. My adress is {email redacted}. You can send now if you want.
Cheers, Slaxer13

[ashjones86]

Spot on cheers, will email now.

[NeoPa]

Regretfully, as so much effort went into the post and it's really very helpful, I've had to reset Twinny's post as Best Answer because it doesn't pertain directly to the question. That doesn't stop you appreciating how helpful it was of course.

As for the question - it seems that Slaxer's assistance was the most directly responsible for the solution, but as it was posted as an attachment rather than displayed in code, it cannot be set as Best Answer either. I'll leave this without one - particularly as much of the discussion has been off-topic. Valuable to those involved, but not very directly related to the question. Not a problem. Many threads are like that. Particularly helpful for beginners - but less valuable for those searching for answers later (except for other beginners I suppose).

Now back to the question for me!
I would simply point out that although searching can be implemented in Access, a more practical approach considering the tools available is to apply a filter. This can be done on-the-fly as well as when the form is opened. Basically it provides a very powerful and flexible way to find things via Forms and Reports. The opportunities are varied and various. Some of the potential, and how to achieve it, is discussed in Example Filtering on a Form.

[Slaxer13]

Sorry it went a little offtopic, it wasn't supposed to but we beginners have the problem of only having the possibility if sending 3 pm a day :/ which is why this happened. From my part sorry 'bout that. As for the answer to the question of the topic i think yours is the best, since mine is an attachment like you said.

[NeoPa]

I'm not sorry it went off-topic my friend :-) This OP has benefitted greatly from much that was off-topic in this thread. It may not be a great thread for searching through later but has great value nevertheless to the OP and possibly even yourself.

As for my answer - that wouldn't be a Best Answer without my stripping out most of the original explanation, and I'm not convinced the OP would even recognise the value of it unless they'd tried it and learned from it. In fact, it seems that Jim already made the same suggestion back in post #2 but the OP didn't recognise the value of it then. Understandable, as those new to databases often have to change the way they think to get into it properly.

All in all, your (Slaxer) additions to the thread have been valuable and you have nothing to apologise for :-)

[ashjones86]

Hello everyone, so with the help of Slaxer13 i have been plodding away, but still with no success, so this is the code im using now, kindly linked by Slaxer -

Private Sub Command19_Click()
If IsNull(Text17) = False Then
      Me.Recordset.FindFirst "[Vehicles]=" & Text17
      Me!Text17 = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Text17 = Null
      End If
   End If
End Sub

However, the error im getting is this - Run-time error '3070':
The Microsoft office access database engine does not recognize 'Vehicles' as a valid name or expression.

Would anyone be able to help ?

[ashjones86]

Ok so its working .....kinda, but it doesn't find anything, so here is the code now -

Option Compare Database
Option Explicit
 
Private Sub Command19_Click()
If IsNull(Text17) = False Then
      Me.Recordset.FindFirst "Vehicle Model" = " & Text17"
      Me!Text17 = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Text17 = Null
      End If
   End If
End Sub

So Vehicle Model is one of my fields in my table which is where the registrations are help, but when i type it in, and click search, it says sorry no record found.

[NeoPa]

Try this version :

Option Compare Database
Option Explicit
 
Private Sub Command19_Click()
    With Me
        If .Text17 > "" Then
            Call .Recordset.FindFirst("([Vehicle Model]='" & Text17 & "')")
            If .Recordset.NoMatch Then
                Call MsgBox("No record found" _
                          , vbOKOnly + vbInformation _
                          , "Sorry")
                .Text17 = Null
            End If
        End If
    End With
End Sub

[ashjones86]

Hi NeoPa,

So i tried this and got 2 problems with it, in the vb editor the line

Call .Recordset.FindFirst("([Vehicle Model]='" & Text17 & "')"

is highlighted as red, if i save and ignore it, it brings up a syntax error on this line

Private Sub Command19_Click()

which is highlighted in yellow.

[ashjones86]

Ok, so found a solution to this, thank you to Slaxer13 who had the patience of a saint with me, back and forth over 150 emails.
Here is the final code for anyone else it may help .

Option Compare Database
 
Private Sub cmdAll_Click()
 
    Dim LSQL  As String
 
    'Display all vehicles
    LSQL = "select * from Vehicles"
 
    Form_Vehicles.RecordSource = LSQL
 
    'lblTitle.Caption = "Vehicle Details:  All records"'
 
    MsgBox "All Vehicles are now displayed."
 
End Sub
 
Private Sub cmdClose_Click()
 
    'Close form
    DoCmd.Close
 
End Sub
 
Private Sub cmdSearch_Click()
 
    Dim LSQL  As String
    Dim LSearchString As String
 
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
 
    Else
 
        LSearchString = txtSearchString
 
        'Filter results based on search string
        LSQL = "select * from Vehicles"
        LSQL = LSQL & " where Vehicle_Model LIKE '*" & LSearchString & "*'"
 
        Form_Vehicles.RecordSource = LSQL
 
        Form_Vehicles.Caption = "Vehicle Details:  Filtered by '" & LSearchString & "'"
 
        'Clear search string
        txtSearchString = ""
 
        MsgBox "Results have been filtered.  All Vehicles containing " & LSearchString & "."
 
    End If
 
End Sub
 

[ashjones86]

I Have chosen my own comment as best answer, but ALL credit goes to Slaxer13.

[Slaxer13]

Just a thing you forgot to mention. The code you showed has got Show All Records Button (cmdAll), Close Button (cmdClose) and Search Button (cmdSearch).

Cheers, Slaxer13

[NeoPa]

We don't encourage members to select their own posts as Best Answers, but in this case as you explain where the help from we can make an exception. You have, after all, posted the solution you came up with which is helpful.

As for post #32, there was indeed an error. The updated post #31 has the fix in it. The closing parenthesis was missing at the end.

As a general rule though, it is very helpful for all of us - especially yourself (and I say this understanding that you are learning this now so it's not criticism but helpful instruction for the future.) if you following the instructions in Before Posting (VBA or SQL) Code before posting problem code. Your time and efforts / frustration is increased if we take much longer to help you due to the lack of sensible information about a problem.

Also, whenever you post of an error in code it's very helpful if you include a reference to the line of code (which you did in this case) and include the error description and number (in order of importance) if there is one, or a description of what is not as expected if it fails in any other way.

[ashjones86]

Hi NeoPa,
My apologies for any forum etiquette i failed to follow, will make sure i go through some of the guides and the posts are more efficient to both myself and other users in future posts.

[NeoPa]

Your only culpability is that you're new Ash :-)

We do try to remember that and not hold it against people (as we like to think of ourselves as relativley intelligent and to forget that wouldn't be ;-)) Sometimes though, as many of us are so long in the tooth here, we forget. Luckily in this case not. You're doing a very fine job so far, and after reading and understanding the linked thread will do even better in future I'm sure.