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/insights/590551-example-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 !