Help!! Search Form

[lee_gti]

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]

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]

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]

Oh I thought that was your form, hehe!! Right, how far have you gotten with designing your form?

 

[lee_gti]

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]

Have you read up on subforms? Or do you know what they are?

 

[lee_gti]

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]

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]

hi, i did that but it came up with an error:-

Lee

 

[vbaInet]

Oops... slight adjustment to code.

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

 

[lee_gti]

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]

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]

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

 

[vbaInet]

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

Command3 is the name of your button!

 

[lee_gti]

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

 

[olxx]

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]

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]

thanks that worked for me.

How can i make the search less specific?

thanks

 

[lee_gti]

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

 

[lee_gti]

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