Repost (No response last time) command button code

J

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?

I have the form now based on a qry called qrySearch...

It has the follwoing code...

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

Select Title, CD as MediaType, tblCDs 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, tblVCDs 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;

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

How would I encorporate the above code into my search form?

Many Thanks for you help its greatly appreciated

James
 
S

Sandra Daigle

Hi James,

I think you need to re-evaluate you table design before you go much further
with the application design. You have 5 tables which all store media
information, the only obvious distinction between these tables is the Media
type which is represented by the table name. This is a common error in
database normalization. To fix this problem, these tables should probably be
consolidated into a single table with one additional field for "MediaType".
Related to the Media Table, you can have a second table which would be like
your tblMP3List - perhaps renamed tblMediaList or tblMediaDetails. For each
record in tblMedia, you would be able to store details describing the
contents of that piece of media.

Once this is redesigned, your search form becomes very simple - a main form
for media, a subform for media details, and and one or more *unbound* combo
or listboxes that enable you to locate a particular type or piece of media.
This also eliminates the need for complex and inefficient union queries.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

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?

I have the form now based on a qry called qrySearch...

It has the follwoing code...

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

Select Title, CD as MediaType, tblCDs 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, tblVCDs 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;

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

How would I encorporate the above code into my search form?

Many Thanks for you help its greatly appreciated

James
Click to expand...
 
G

gary b

James...

I would agree with Sandra. I have a 2-table database with
LOTS of fields that have a 1-to-many relationship. Here's
what I did to created a drill-down, user-selected search!

1. I created a form (frmSearchcriteria) which lists
virtually all of the fields of both tables using unbound
textboxes. This form has several buttons: ClearForm,
ViewRecords, ExitForm This form provides search/query
criteria values.
2. I created a query based on BOTH tables. I then
specified a criteria for each field that I want to
search. The query criteria references the appropriate
textbox in frmSearchcriteria.
3. The syntax of the criteria basically states that the
query should use the value in the frmSearchcriteria
textboxes -- returning only records that match the textbox
value. However, if the textbox is NULL, then return
any/all records. The beauty of this is:
a) if users enters NO values in textboxes, query will
return EVERY record in database.
b) if user enters values in four textboxes, query will
return all records that have these values in these fields.
c) this process allows the user to define the search to be
as narrow or as broad as he/she wants!

This technique has proven to be very powerful, yet VERY
user-friendly! The ability to 'drilldown' is awesome.
This approach might work well for you. If you want more
info, please email me. (Remove NOSPAM from listed address.)

HTH...

gary b

-----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?

I have the form now based on a qry called qrySearch...

It has the follwoing code...

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

Select Title, CD as MediaType, tblCDs 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, tblVCDs 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;

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

How would I encorporate the above code into my search form?

Many Thanks for you help its greatly appreciated

James


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Problem? 3
REPOST: (No Response) Coding A Search Button 2
Command Button Code 1
Problems with my code??? 8
Coding a button 0
Search Button Problems 1
Repost: Coding a Search Button 3
Coding a Search Button 0

Top