Repost: Coding a Search Button

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello I have been advised to write a new post stating what
I have done and what I need to do with the above named
subject.

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
 
Assuming the fields are the same:


Select Title,"CD" as MediaType,"tblCDs" as BaseTable from
tblCDs
UNION
Select Title,"DVD" as MediaType,"tblDVD" as BaseTable from
tblDVD
UNION
Select Title,"VCD" as MediaType,"tblVCD" as BaseTable from
tblVCD
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware



Then you can search that query. I'm curious as to why you
didn't set up the table like that query in the first place.



Chris Nebinger
 
Well because I thought it may rationalise the database as
I am learning at the moment off my own back...

Was that wrong to do??

Thanks for your input... So would the code stay the same
then or would it have to be changed?

Many Thanks

James
-----Original Message-----
Assuming the fields are the same:


Select Title,"CD" as MediaType,"tblCDs" as BaseTable from
tblCDs
UNION
Select Title,"DVD" as MediaType,"tblDVD" as BaseTable from
tblDVD
UNION
Select Title,"VCD" as MediaType,"tblVCD" as BaseTable from
tblVCD
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware



Then you can search that query. I'm curious as to why you
didn't set up the table like that query in the first place.



Chris Nebinger


-----Original Message-----
Hello I have been advised to write a new post stating what
I have done and what I need to do with the above named
subject.

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:

---------------------------------------------------------
Click to expand...
-
-
Private Sub cmdSearchButton_Click()
Dim strCriteria As String

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

End Sub

---------------------------------------------------------
Click to expand...
-
-

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

.
Click to expand...
.
Click to expand...
 
Hi I have modified the code you have given me to the
following:

-----------------------------------------------------------
Select Title,"CD" as MediaType,"tblCD's" as BaseTable from
tblCD's
UNION
Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's
UNION
Select Title,"VCD" as MediaType,"tblVCD's" as BaseTable
from
tblVCD's
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware
UNION
Select Title,"MP3Title" as MediaType,"tblMP3Title" as
BaseTable from tblMP3Title
UNION
Select Title,"MP3's on CD" as MediaType,"tblMP3List" as
BaseTable from tblMP3List

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

I am getting an Error on the line of:

Select Title,"DVD Title" as MediaType,"tblDVD's" as
BaseTable from
tblDVD's

It says "Invalid Brackiting of Name "s
Select Title,"DVD Title" as MediaType,"tblDVD"

What does this mean?

Many Thanks

James

-----Original Message-----
Well because I thought it may rationalise the database as
I am learning at the moment off my own back...

Was that wrong to do??

Thanks for your input... So would the code stay the same
then or would it have to be changed?

Many Thanks

James
-----Original Message-----
Assuming the fields are the same:


Select Title,"CD" as MediaType,"tblCDs" as BaseTable from
tblCDs
UNION
Select Title,"DVD" as MediaType,"tblDVD" as BaseTable from
tblDVD
UNION
Select Title,"VCD" as MediaType,"tblVCD" as BaseTable from
tblVCD
UNION
Select Title,"Software" as MediaType,"tblSoftware" as
BaseTable from tblSoftware



Then you can search that query. I'm curious as to why you
didn't set up the table like that query in the first place.



Chris Nebinger
Click to expand...
-
-
-
Private Sub cmdSearchButton_Click()
Dim strCriteria As String

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

End Sub

--------------------------------------------------------
Click to expand...
Click to expand...
-
-
.
Click to expand...
.
Click to expand...
 
Back
Top