REPOST: (No Response) Coding A Search Button

[James]

I have some tables with diffrent data in them. which I
will go into more detail a little further on.

Right I would like to create a search form which I have
done for one of the tables and I have been told that it
would be easier and quicker to make one form for all the
searching to be done in rather than one form for each
search request.

So I would like some assistance in doing the above. Here
is what I have...

2 Forms relevant to searching (frmSearchAllRecords and
frmSearchResults).

frmSearchAllRecords is the main search form based on one
table at the moment (tblCDs) which searches great with the
below code:

-----------------------------------------------------------
Private Sub cmdSearchButton_Click()
Dim strCriteria As String

strCriteria = "[CD Album Title] = '" &
Me.CDTitle & "'"
DoCmd.OpenForm "frmSearchResults", , ,
strCriteria

End Sub

-----------------------------------------------------------

Which then puts the results in frmSearchResults. On
frmSearchAllRecords I have a search button and an exit
button. on frmSearchResults I have a search again button
(which I want to have an msgbox coming up and saying if I
want to search again if yes then it clears the
frmSeachAllRecords ready for a new search where by if they
say no it closes both forms) and a close button.

The tables in my database are:

tblMP3Title (The Titles of CD's with MP3 folders on them)
tblMP3List (Lists the Folders on the CD)

the above tables are linked to each other with a one - to -
many relationship.

tblCDs (Stors the titles of the CD's)
tblDVD (Does the same as the above with DVD Titles)
tblVCD (Same as above but with VCD Titles)
tblSoftware (Same as above with Software Titles)

I would like to know how I can achieve this? Would I need
to do a query to join all the tables as one and base
frmSearchAllRecords on this? if so how do I do this?

What other code would I need to complete the search button
or would it be a simple copy and paste of the above code
for each things to search for?

Many Thanks for you help its greatly appreciated

James

 

[Elaine]

This is how I accomplished different search criteria on
the same form:
My form has a basic sql query. In the header of the
form I put a frame with check boxes for different
criteria; example: Manager, Project, employee. I have an
unbound combo box. When a check box is clicked I have a
Select Case procedure that changes the criteria of the
combo box. It will list the manager, or project or
employee. After the select case I will
say "Combo1.RowSource = SQLstatement." I then have
a "Find" button that when clicked will go to another
Select case routine that changes the Form's sql query of
the form adding the "WHERE

![Field] = '" & Combo1
& "'" clause . Then Code "Me.RecordSource = strsql".
It's not as comlicated as it sounds. You just have to
remember when to use the single quote or double quote.
Hope this will help you some or at least get you going in
the right direction.

-----Original Message-----
I have some tables with diffrent data in them. which I
will go into more detail a little further on.

Right I would like to create a search form which I have
done for one of the tables and I have been told that it
would be easier and quicker to make one form for all the
searching to be done in rather than one form for each
search request.

So I would like some assistance in doing the above. Here
is what I have...

2 Forms relevant to searching (frmSearchAllRecords and
frmSearchResults).

frmSearchAllRecords is the main search form based on one
table at the moment (tblCDs) which searches great with the
below code:

---------------------------------------------------------- -
Private Sub cmdSearchButton_Click()
Dim strCriteria As String

strCriteria = "[CD Album Title] = '" &
Me.CDTitle & "'"
DoCmd.OpenForm "frmSearchResults", , ,
strCriteria

End Sub

---------------------------------------------------------- -

Which then puts the results in frmSearchResults. On
frmSearchAllRecords I have a search button and an exit
button. on frmSearchResults I have a search again button
(which I want to have an msgbox coming up and saying if I
want to search again if yes then it clears the
frmSeachAllRecords ready for a new search where by if they
say no it closes both forms) and a close button.

The tables in my database are:

tblMP3Title (The Titles of CD's with MP3 folders on them)
tblMP3List (Lists the Folders on the CD)

the above tables are linked to each other with a one - to -
many relationship.

tblCDs (Stors the titles of the CD's)
tblDVD (Does the same as the above with DVD Titles)
tblVCD (Same as above but with VCD Titles)
tblSoftware (Same as above with Software Titles)

I would like to know how I can achieve this? Would I need
to do a query to join all the tables as one and base
frmSearchAllRecords on this? if so how do I do this?

What other code would I need to complete the search button
or would it be a simple copy and paste of the above code
for each things to search for?

Many Thanks for you help its greatly appreciated

James

.

 

[James]

Ok and how do I achieve half of this??

What was your SQL statement?

How did you use your ComboBox and checkboxes?? how did you
set them up?

Many Thanks

James

-----Original Message-----
This is how I accomplished different search criteria on
the same form:
My form has a basic sql query. In the header of the
form I put a frame with check boxes for different
criteria; example: Manager, Project, employee. I have an
unbound combo box. When a check box is clicked I have a
Select Case procedure that changes the criteria of the
combo box. It will list the manager, or project or
employee. After the select case I will
say "Combo1.RowSource = SQLstatement." I then have
a "Find" button that when clicked will go to another
Select case routine that changes the Form's sql query of
the form adding the "WHERE

![Field] = '" & Combo1
& "'" clause . Then Code "Me.RecordSource = strsql".
It's not as comlicated as it sounds. You just have to
remember when to use the single quote or double quote.
Hope this will help you some or at least get you going in
the right direction.

-----Original Message-----
I have some tables with diffrent data in them. which I
will go into more detail a little further on.

Right I would like to create a search form which I have
done for one of the tables and I have been told that it
would be easier and quicker to make one form for all the
searching to be done in rather than one form for each
search request.

So I would like some assistance in doing the above. Here
is what I have...

2 Forms relevant to searching (frmSearchAllRecords and
frmSearchResults).

frmSearchAllRecords is the main search form based on one
table at the moment (tblCDs) which searches great with the
below code:

---------------------------------------------------------

-

-
Private Sub cmdSearchButton_Click()
Dim strCriteria As String

strCriteria = "[CD Album Title] = '" &
Me.CDTitle & "'"
DoCmd.OpenForm "frmSearchResults", , ,
strCriteria

End Sub

---------------------------------------------------------

-

-

Which then puts the results in frmSearchResults. On
frmSearchAllRecords I have a search button and an exit
button. on frmSearchResults I have a search again button
(which I want to have an msgbox coming up and saying if I
want to search again if yes then it clears the
frmSeachAllRecords ready for a new search where by if they
say no it closes both forms) and a close button.

The tables in my database are:

tblMP3Title (The Titles of CD's with MP3 folders on them)
tblMP3List (Lists the Folders on the CD)

the above tables are linked to each other with a one - to -
many relationship.

tblCDs (Stors the titles of the CD's)
tblDVD (Does the same as the above with DVD Titles)
tblVCD (Same as above but with VCD Titles)
tblSoftware (Same as above with Software Titles)

I would like to know how I can achieve this? Would I need
to do a query to join all the tables as one and base
frmSearchAllRecords on this? if so how do I do this?

What other code would I need to complete the search button
or would it be a simple copy and paste of the above code
for each things to search for?

Many Thanks for you help its greatly appreciated

James

.

.