'Search' function required

[Rohan McCarthy]

I have developed a form, based on a query and set it to datasheet view mode.

What I'd like to do is use the form to search through records and then be
able to select a particular record by double-clicking within it, upon which
the clicked record will open in form view and the search form will close.

(I want to do this so I can deactivate my main form's 'Open in Edit Mode'
from the Switchboard, thereby preventing my users from scrolling through
forms that they don't really need to see and sometimes accidentally making
errors of input into fields of records that are already completed).

Can anyone help with this? I do know a small bit about coding (mostly from
this site!), but am far from an expert.

Rohan

 

[Golfinray]

You need a basic filter function. Whatever the field in the table is that you
would like to filter (search) on in your form, put a combo box on your form
and (using the combo wizard) select that field for your combo to look up. For
example, if you had 5 columns in your table and one of them was Customer ID,
you would want to search on that column. Then go into the combo's afterupdate
event (click on properties/events) and start the code builder (the little on
the end.) Type this is where the cursor is:
Me. Filter = "[thenameofthecolumnyouaresearching] = """ & Me.the#ofyourcombo
(like combo18) & """"
Me.Filteron = True

Now when the user scrolls through the combo and finds the record they want
that record will come up. You may need to do some tweaking, like getting the
form to close after they have edited that record. Do a search on here and you
should find that no problem.

 

[Albert D. Kallal]

If you download my word merge example here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

in the above the "super easy word merge" does have a sample search form that
does what you want.....

If you don't want to build a form, consider placing a search combo box on
the actual form (there is a wizard that will build this searching system for
you).

 

[Rohan McCarthy]

Thanks Golfinray.

I've done this, but it doesn't seem to be filtering the records as detailed.
You meant to add the combo-box to the main form, yes?

This is a cut-and-paste of the code:

'Lead Number' is the Autonumber field.

---
Private Sub Combo236_AfterUpdate()

Me.Filter = "[Lead Number] = """ & Me.Combo236 & """"
Me.FilterOn = False

End Sub
---

You need a basic filter function. Whatever the field in the table is that you
would like to filter (search) on in your form, put a combo box on your form
and (using the combo wizard) select that field for your combo to look up. For
example, if you had 5 columns in your table and one of them was Customer ID,
you would want to search on that column. Then go into the combo's afterupdate
event (click on properties/events) and start the code builder (the little on
the end.) Type this is where the cursor is:
Me. Filter = "[thenameofthecolumnyouaresearching] = """ & Me.the#ofyourcombo
(like combo18) & """"
Me.Filteron = True

Now when the user scrolls through the combo and finds the record they want
that record will come up. You may need to do some tweaking, like getting the
form to close after they have edited that record. Do a search on here and you
should find that no problem.

I have developed a form, based on a query and set it to datasheet view mode.

What I'd like to do is use the form to search through records and then be
able to select a particular record by double-clicking within it, upon which
the clicked record will open in form view and the search form will close.

(I want to do this so I can deactivate my main form's 'Open in Edit Mode'
from the Switchboard, thereby preventing my users from scrolling through
forms that they don't really need to see and sometimes accidentally making
errors of input into fields of records that are already completed).

Can anyone help with this? I do know a small bit about coding (mostly from
this site!), but am far from an expert.

Rohan

 

[Rohan McCarthy]

Thank you Albert!

It took a fair bit of fiddling around, but I adapted it to my needs in the
end.

Much appreciated.

Rohan