Help!! Search Form (1 Viewer)

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
Hi, i am very new to this and I am trying to create a form that can be used to search a database and then show a list of results.

I have little to no experience with database but it is the software on the computer.

This is the type of database that I have and i am trying to search.


Then i would like to be able to search and show the results using a form like this:-


*Maybe this could have some other items to search for such as job number etc.

If any one could help me i would be very grateful.

Many thanks in advance.

Lee
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
If you want it to filter out the data that matches then you use

Me.Filter = searchBox.value
Me.FilterOn = True

Use wildcards if you want to find values that are Like your search string.
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
If you want it to filter out the data that matches then you use

Me.Filter = searchBox.value
Me.FilterOn = True

Use wildcards if you want to find values that are Like your search string.

Hi, thanks. I still don't really understand as I don't even know how to make a form like the one I have shown in the example.

I really need a lot of help with this if anyone has the time

Lee
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
Oh I thought that was your form, hehe!! Right, how far have you gotten with designing your form?
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
Im ok with putting in buttons, text boxes etc but I dont know how to add in a result type box that will show the results and then how to link them up.

I am currently using a text box and a command but to enter the search (but there is no code to make it search) and that is as far as it has got, i have also created the database.


this is my form so far....very poor as you can see

Thanks
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
Have you read up on subforms? Or do you know what they are?
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
Have you read up on subforms? Or do you know what they are?

I have managed to put a subform in now, but how do i link it to the search and also make it only show what is relevant?


this is how the form looks now

Lee
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
Excellent, for starters do this on the On Click event of the search button:

Me.Filter = "Surname = '" & nameOfSearchBox.value & "'"
Me.FilterOn = True

See if that works.

Would also be uses to have a Remove Filter button/control as well.
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
hi, i did that but it came up with an error:-



Lee
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
Oops... slight adjustment to code.

Me!subFormName.Form.Filter = "Surname = '" & nameOfSearchBox.value & "'"
Me!subFormName.Form.FilterOn = True
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
Oops... slight adjustment to code.

Me!subFormName.Form.Filter = "Surname = '" & nameOfSearchBox.value & "'"
Me!subFormName.Form.FilterOn = True

do i need to add something to this code?

Lee
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
do i need to add something to this code?

Lee

Yes, where it reads "subformName" change it to the name of your subform. Open your main form in design view, click on the subform (once), under the Other tab in properties sheet look for the Name property. Also do the same for the "searchBoxName"
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
hey thanks...feels like im getting somewhere now.

it comes up with compile error and highlights .Value of the code below and then when i pressed the ok and then debug it highlights the top line of the code below:-

Private Sub Command3_Click()

Me!archivesubform.Form.Filter = "Surname = '" & Command3.Value & "'"
Me!archivesubform.Form.FilterOn = True

End Sub


Lee
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
hi, realised what i have done now...putting the name of my button when i should have been putting the name of the text box.

i have replaced that but now i am getting a different error



thanks
 
Last edited:

olxx

Registered User.
Local time
Today, 06:27
Joined
Oct 2, 2009
Messages
52
Its easier to this using query and showing this on subform. Link query criteria to your main form textbox ad there you go. You can even use OnChange event on that textbox instead of using button. Then you´ll have this look like cool live search, which starts filtering as you type in name.
 

vbaInet

AWF VIP
Local time
Today, 13:27
Joined
Jan 22, 2010
Messages
26,374
hi, realised what i have done now...putting the name of my button when i should have been putting the name of the text box.

i have replaced that but now i am getting a different error



thanks

Me.archivesubform.form.Filter =
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
thanks that worked for me.

How can i make the search less specific?

thanks
 

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
hi, i have tried to copy the coding to add different searches in using different command buttons but on some of them i am getting a window come up:-



which doesn't happen on the original version of the code.

Any clue how to stop this?


this is how my code looks now:-



I cannot see why the error is occuring other than I have attempted to change the name to a single word (e.g. Property location --> Propertylocation) as i only trouble with multiple word ones.

what is the best way about changing the name (e.g. property locaiton) as i have been doing it in the properties box of the subform.

Lee
 
Last edited:

lee_gti

Registered User.
Local time
Today, 13:27
Joined
Jan 28, 2010
Messages
20
Hi, i have attempted to fix this problem over the weekend but have still not succeeded and really have no idea what is causing this to happen as i cannot see anything wrong with the code.

if anyone could help me i would be very grateful.

thanks in advance

Lee
 

Users who are viewing this thread

Top Bottom