Search Form

[Guest]

Hi,

I want to create a form that allows a user to search a table.

Right now I have table tblCompanyInformation (name, address, etc..) and
tblProductData (information on their products). I also have a form called
frmCompanyInformation that shows certain information from both of these
tables.

My desire is this: For a user to open a form and be able to input a company
name and have that company's record appear from the frmCompanyInformation.

I hope this all makes sense

Thanks!

 

[Albert D. Kallal]

have you tried using the combo box wizard?

Try the above idea....when you type in, or select the company, the form will
move to that record.....

 

[Guest]

I like the idea of the combo box, but I'm confused on how to actually tell
Access to move to the record of choice. I want an independant form that is
only for searching. When the user enters information (or looks up
information via combo box), they will need to hit a "search" button and that
button will locate the correct record and open it. What is the correct code
to make this button do that?

 

[Albert D. Kallal]

I like the idea of the combo box, but I'm confused on how to actually tell
Access to move to the record of choice.

The combo box wizard will build in the code to "move" to the selected
reocrd.

Choose the 3rd option when building a combo box

eg:
Find a record based on the value I seleteced in my combo box

So, ms-access will "move" the form to the reocrd of choice...
Give the combo box wizard thing a try..it not too bad...

I want an independant form that is
only for searching.

Yes, I tend to like the above aprpoach, and I useally do make a seperate
seach form.

here is some screen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

There is also a seach form example here:
http://www.datastrat.com/DataStrat2.html
and
http://www.mvps.org/access/forms/frm0045.htm

If you want to make your own, then:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.

dim strSql as string

strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"

me.MySubFormname.Form.RecordSource = strSql.

It is only a few lines of code at most....