To search record

[puT3]

I want to create a search form for a library which contain 2 option group;1 is for searching according to keywords,exact match,first on field and phrase,another is type of searching which have different field table such as title,publication,subject,author,call number,ISBN, and any. and also a text field to enter searching.When the search button is click,the result will appear in another form line by line.Do i need to create a query?I dont know ho to create the coding,I get confuse.I am very new to this.

[puT3]

There's really very little point in trying a version which is not the same as I've posted.

If there are any changes you need to make then I need to know.

It seems quite clear from this that there are a few changes needed because the information you've provided, even after I have asked and asked for clear reliable information, is still very wrong. I can't imagine what I could have said that would have made it any clearer how important this is.

Let me know if you feel that you cannot provide this information for me, as I will be happy to stop here and now. Frankly I'm feeling more than a little frustrated that you are asking for help, but seem to be unwilling to go to any trouble to provide me with simple information.

Im really sorry... I dont think i change anything but I still need ur help and I will write the complete details of the table,form (includes controls and name) later because i am at my hometown and away from the city i work and i left the notebooks which has all the information about the database there...if u dont mind,can u wait for it?

I really am sorry for all this trouble...

[puT3]

TABLE

Printing Media Library

CallNumber;Text;PK
AccessionNumber;Text
ISBN/ISSN;Text
Title;Text
SubjectID;Number;FK
PublisherID;Number;FK (PK in Publication table as Publication ID)
Physical Description Area;Text
Bibliography Notes;Text
Description;Text
CopyrightYear;Text
ProviderID;Number;FK
CategoryID;Number;FK
Issue/Edition;Text
AuthorID;Number;FK
 

Subject

SubjectID;Number;FK
Topical Headings;Text
 

Publication

Publication ID;Number;PK
Publication Name;Text
Foundation;Text
 

Provider

ProviderID;Number;PK
Provider Name;Text
Quantity;Text
 

Author

AuthorID;Number;PK
Author Name;Text
Background;Text
 

Category

CategoryID;Number;PK
Category Name;Text
 

This is the search form name [Printing Media Library - Search Form]

Search By:
()Keywords - OptKeywords
()Exact Match - OptExact
()First in Field - OptFirst
()Phrase - OptPhrase

Search Type:
() Title - OptTitle
() Author - OptAuthor
() Pulication - OptPublication
() Call Number - OptCall
() Subject - OptSubject
() ISBN/ISSN - OptISBN

Find textbox - TxtFind
Search button - searchrec

this is the result/for filter form named [Printing Media Library]
all are text box control, bound to the [Printing Media Library] table

The query i named as QueryDef....

These is all the table and form of the database...
is there anything i missed??? please let me know and again im really sorry,i hope we can continue this if u are willing to

[NeoPa]

I am willing.

For now, when I am short of time as the Bytes Online meetup on googlechat is starting soon and I'm not ready yet, I will just say that I do understand how difficult most people find communicating in a clear and precise manner.

In technical areas though, especially when trying to communicate via the medium of a forum web site, this becomes so important that it's very hard to express.

Perhaps I could put it into context if I said that if only half of my time were wasted due to poor communication I'd be ecstatic.

If I get this, but no other, point across in the thread, I will consider you to be well served.

Let's see if we can't also help you with your project.

[NeoPa]

The next thing you need to do is to take the SQL I posted in post #44 and put it into a QueryDef.

Post here what happens when you try to run that QueryDef.

[puT3]

OK thank you i wil post it when im back at work 2morrow...

[puT3]

Can I ask u why some of the SQL has the field in bracket and some are not..

[puT3]

The next thing you need to do is to take the SQL I posted in post #44 and put it into a QueryDef.

Post here what happens when you try to run that QueryDef.

This is my code where i add the provider table

SELECT [Printing Media Library].CallNumber,[Printing Media Library].AccessionNumber,[Printing Media Library].ISBN/ISSN,Author.[Author Name],[Printing Media Library].Title,[Printing Media Library].Issue/Edition,Subject.[Topical Headings],Publication.[Publication Name],[Printing Media Library].[Physical Description Area],[Printing Media Library].[Bibliography Notes],[Printing Media Library].CopyrightYear,Provider.ProviderID,[Printing Media Library].CategoryID,[Printing Media Library].Description
FROM (([Printing Media Library] AS tP INNER JOIN [Publication] AS tPu ON tP.PublisherID=tPu.[Publication ID]) INNER JOIN [Author] AS tA ON tP.AuthorID=tA.AuthorID) INNER JOIN [Subject] AS tS ON tP.SubjectID=tS.[Subject ID] INNER JOIN [Provider] AS tPr ON tP.ProviderID=tPr.ProviderID;
 

However there is an error here

tP.SubjectID=tS.[Subject ID] INNER JOIN [Provider] AS tPr ON tP.ProviderID=tPr.ProviderID;
 

it said that it has 'Syntax error (missing operator) in query expression'

does it need a bracket like this one...

(([Printing Media Library] AS tP INNER JOIN [Publication] AS tPu ON tP.PublisherID=tPu.[Publication ID]) INNER JOIN [Author] AS tA ON tP.AuthorID=tA.AuthorID)
 

i already check the table to see if i missed anything but i didnt find one...


if all is done, what is the next step...

[PianoMan64]

Can I ask u why some of the SQL has the field in bracket and some are not..

The reason why some of the field names have brackets and other don't, is when you have a field name that has spaces in it, you have to rap that in a bracket.

for example: [Company ID]

If you had the name in the table as CompanyID, then you wouldn't have to put the brackets around it.

This is the reason.

Hope that helps,

Joe P.

[puT3]

The reason why some of the field names have brackets and other don't, is when you have a field name that has spaces in it, you have to rap that in a bracket.

for example: [Company ID]

If you had the name in the table as CompanyID, then you wouldn't have to put the brackets around it.

This is the reason.

Hope that helps,

Joe P.

Oh ok thank you for ur help...

[puT3]

The next thing you need to do is to take the SQL I posted in post #44 and put it into a QueryDef.

Post here what happens when you try to run that QueryDef.

I already run the SQL as u posted...When I click run,a message box appear to enter parameter value and i click ok,a datasheet view appear with no data in it...the SQL:

SELECT [Printing Media Library].CallNumber,[Printing Media Library].AccessionNumber,[Printing Media Library].ISBN/ISSN,Author.[Author Name],[Printing Media Library].Title,[Printing Media Library].Issue/Edition,Subject.[Topical Headings],Publication.[Publication Name],[Printing Media Library].[Physical Description Area],[Printing Media Library].[Bibliography Notes],[Printing Media Library].CopyrightYear,Provider.ProviderID,[Printing Media Library].CategoryID,[Printing Media Library].Description
FROM (((([Printing Media Library] AS tP INNER JOIN [Publication] AS tPu ON tP.PublisherID=tPu.[Publication ID]) INNER JOIN [Author] AS tA ON tP.AuthorID=tA.AuthorID) INNER JOIN [Subject] AS tS ON tP.SubjectID=tS.[Subject ID]) INNER JOIN [Provider] AS tPr ON tP.ProviderID=tPr.ProviderID);
 

so what happen next?

[NeoPa]

Can I ask u why some of the SQL has the field in bracket and some are not.

You like to keep me busy I see. But that's not a problem, this is an interesting question that others may also need to understand.

In general, using [] to surround object names in SQL is not necessary. However there are situations where, without the [] specifically indicating that the contents refer to an object (Field; Table; Control from a form; Query; Database even sometimes) interpreting the SQL could be ambiguous (Remember we discussed clarity and ambiguity in communication earlier, well it applies even more specifically to computers). Well, if the SQL engine, or interpreter, is unable to understand what is meant, then it will not execute the SQL (at least not correctly).

Examples of possible ambiguity are reserved words (Date; For; SELECT; etc), as well as object names with embedded spaces (SQL uses any white space as a separator). If any of these are used as object names, then the SQL engine would not know whether the word referred to your object or the inbuilt item. Surrounding them in [] makes it clear exactly what is intended.

As far as posting goes, and READABILITY of the SQL, I often use them where they are not strictly required by the SQL engine, to make the context clearer and easier to read. Whenever you see something in the [] you KNOW it's an object. I don't use them so much within my own SQL, but for clarity on the forums I do.

PS. @Joe P.
Sorry, didn't catch your post until after I'd already posted this one. Thanks for jumping in anyway. All contributions appreciated :)

[NeoPa]

I already run the SQL as u posted...When I click run,a message box appear to enter parameter value and i click ok,a datasheet view appear with no data in it...the SQL:

...
Some SQL you've knocked up
...
 

so what happen next?

If you refer back to post #54 (which you've quoted) you will find that the instructions are crystal clear. There is no ambiguity.

Telling me that a message box appeared prompting for a parameter value (without including even the name showing in the box) is NOT following the instruction.

It's not hard to understand why your response leaves me with nothing to work on. I couldn't help further if I wanted to from this.

I am not going to reverse engineer every piece of SQL you post simply because you cannot seem to pass the information across I need. That takes a lot of time and effort, and I have already done that a couple of times. I don't intend to do that every time you fail to post the information correctly, as clearly, from past experience, I would be spending most of my time doing just that.

[puT3]

If you refer back to post #54 (which you've quoted) you will find that the instructions are crystal clear. There is no ambiguity.

Telling me that a message box appeared prompting for a parameter value (without including even the name showing in the box) is NOT following the instruction.

It's not hard to understand why your response leaves me with nothing to work on. I couldn't help further if I wanted to from this.

I am not going to reverse engineer every piece of SQL you post simply because you cannot seem to pass the information across I need. That takes a lot of time and effort, and I have already done that a couple of times. I don't intend to do that every time you fail to post the information correctly, as clearly, from past experience, I would be spending most of my time doing just that.

Im really sorry again...my earlier response wasnt enough for u to work on,really2 sorry,i dont read the instructions well enough,i really sorry,...

the message box that appear request to enter a parameter value for each of the field,it ask for

Printing Media Library.CallNumber,
Printing Media Library.Accession Number,
Printing Media Library.ISBN/ISSN,
Author.Author Name,
Printing Media Library.Title,
Printing Media Library.Physical Description Area,
Subject.Topical Headings,
Publication.Publication Name,
Printing Media Library.Bibliography Notes,
Printing Media Library.CopyrightYear,
Provider.ProviderID,
Printing Media Library.CategoryID,
Printing Media Library.Description....

I click ok everytime this message box appear n finally an empty datasheet appear....

i understand if u dont want to continue this but i need help,i will give u every information that u need after this...

[NeoPa]

There seems to be a fundamental problem here, in that the references in your list don't appear in the SQL I provided in post #44.

SQL will only ask for the value of a reference when it finds it in the SQL and doesn't know how to resolve it.

The SQL I provided makes perfect sense in the context of the information I had from you at that stage, so if there are any failures of reference, they should be few and easily resolvable. In the process we make some headway, at least in the information communicated.

As these reference controls on your form in an incorrect manner, I can only assume that you have tried out some of your own attempted SQL instead of what I provided (in post #44). Looking at this will get us nowhere so let's not even waste our time looking at the various ways this is wrong.

###RESET###
Just go back to post #54 and try again from there.

[puT3]

There seems to be a fundamental problem here, in that the references in your list don't appear in the SQL I provided in post #44.

SQL will only ask for the value of a reference when it finds it in the SQL and doesn't know how to resolve it.

The SQL I provided makes perfect sense in the context of the information I had from you at that stage, so if there are any failures of reference, they should be few and easily resolvable. In the process we make some headway, at least in the information communicated.

As these reference controls on your form in an incorrect manner, I can only assume that you have tried out some of your own attempted SQL instead of what I provided (in post #44). Looking at this will get us nowhere so let's not even waste our time looking at the various ways this is wrong.

###RESET###
Just go back to post #54 and try again from there.

I tried again the SQL u post in post #44 but i do not include the [Provider Name] field from [Provider] table and i change a little bit to match the name of the field i changed which i already given u in earlier post (#52)...

SELECT tP.CallNumber, tP.AccessionNumber, tP.[ISBN/ISSN], tA.[Author Name], tP.Title, tP.[Issue/Edition], tS.[Topical Headings], tPu.[Publication Name], tP.[Physical Description Area], tP.[Bibliography Notes], tP.CopyrightYear, tP.ProviderID, tP.CategoryID
FROM (([Printing Media Library] AS tP INNER JOIN Publication AS tPu ON tP.PublisherID=tPu.[Publication ID]) INNER JOIN Author AS tA ON tP.AuthorID=tA.AuthorID) INNER JOIN Subject AS tS ON tP.SubjectID=tS.[Subject ID];
 

This time there is no more message box appear,its went straight to a datasheet with all the fields but there is no data in the datasheet,should this happen? Or should it have any data in it?I forgot to ask u what should I expect if I run this SQL code....

[puT3]

Another thing i to ask u,what do u mean by this "you may also want to change some of the control names on [PRINTING MEDIA LIBRARY] to reflect they are no longer referring to the IDs" you have post in post#44....

For the SQL,I should see a list of data right?it still has problem right?

in [Printing Media Library], i make AuthorID,PublisherID,ProviderID and SubjectID as a look up to [Author],[Publication],[Subject],[Provider] table, i shouldnt have any problems right?

[NeoPa]

I tried again the SQL u post in post #44 but i do not include the [Provider Name] field from [Provider] table and i change a little bit to match the name of the field i changed which i already given u in earlier post (#52)...

Your SQL

This time there is no more message box appear,its went straight to a datasheet with all the fields but there is no data in the datasheet,should this happen? Or should it have any data in it?I forgot to ask u what should I expect if I run this SQL code....

I am interested in seeing the results of the SQL I posted. I am not interested in seeing the results of any other SQL at this time.

I don't know how much more clear I can make this statement.

[NeoPa]

Another thing i to ask u,what do u mean by this "you may also want to change some of the control names on [PRINTING MEDIA LIBRARY] to reflect they are no longer referring to the IDs" you have post in post#44....
...

From post #26, the latest published version of this form (updated and displayed below), it has places for the IDs for various elements, but what we really want to show on this form is the descriptions for those elements. This has been handled in the SQL (it returns the descriptions rather than the IDs now), but the names of the controls on your form have not (as far as I'm aware - & I should be) been changed to reflect these changes.
Form Name=[PRINTING MEDIA LIBRARY]

Control; Type
Call Number; TextBox
Accession Number; TextBox
ISBN; TextBox
AuthorID; TextBox
Title Statement; TextBox
Issue; TextBox
SubjectID; TextBox
PublisherID; TextBox
Physical Description Area; TextBox
Bibliography Notes; TextBox
Description; TextBox
Copyright Year; TextBox
ProviderID; TextBox
CategoryID; TextBox

...
For the SQL,I should see a list of data right?it still has problem right?
...

I have no way of knowing yet, as you haven't given me any info regarding what happens when you run THE EXACT SQL I posted.

...
in [Printing Media Library], i make AuthorID,PublisherID,ProviderID and SubjectID as a look up to [Author],[Publication],[Subject],[Provider] table, i shouldnt have any problems right?

If you follow the instructions correctly there should be no need for doing any extra lookups. You would certainly NOT want to design any into your form.

[puT3]

From post #26, the latest published version of this form (updated and displayed below), it has places for the IDs for various elements, but what we really want to show on this form is the descriptions for those elements. This has been handled in the SQL (it returns the descriptions rather than the IDs now), but the names of the controls on your form have not (as far as I'm aware - & I should be) been changed to reflect these changes.
Form Name=[PRINTING MEDIA LIBRARY]

Control; Type
Call Number; TextBox
Accession Number; TextBox
ISBN; TextBox
AuthorID; TextBox
Title Statement; TextBox
Issue; TextBox
SubjectID; TextBox
PublisherID; TextBox
Physical Description Area; TextBox
Bibliography Notes; TextBox
Description; TextBox
Copyright Year; TextBox
ProviderID; TextBox
CategoryID; TextBox

I have no way of knowing yet, as you haven't given me any info regarding what happens when you run THE EXACT SQL I posted.

If you follow the instructions correctly there should be no need for doing any extra lookups. You would certainly NOT want to design any into your form.

Sorry I was away yesterday that i couldn't reply back to you...

If I exactly run your posted SQL,a message box will appear and requesting for parameter for each of the field and click ok for each box appear without entering anything it in and lastly it will show a empty datasheet....because i think it does not match the field name in the table...is it true? thats why i changed a little of the name to match the name of the field in the table....

this is your code that i run....i copy it exactly like it in the query

SELECT tP.[Call Number],
       tP.[Accession Number],
       tP.ISBN,
       tA.[Author Name],
       tP.[Title Statement],
       tP.Issue,
       tS.SubjectTitle,
       tPu.[Publication Name],
       tP.[Physical Description Area],
       tP.[Bibliography Notes],
       tP.[Copyright Year],
       tP.[Provider ID],
       tP.CategoryID
 
FROM (([Printing Media Library] AS tP INNER JOIN
       [Publication] AS tPu
  ON   tP.PublisherID=tPu.[Publication ID]) INNER JOIN
       [Author] AS tA
  ON   tP.AuthorID=tA.AuthorID) INNER JOIN
       [Subject] AS tS
  ON   tP.SubjectID=tS.SubjectID
 

[puT3]

Im sorry I just reread all your post n I missed to change the recordsource of the form to the query.However,the problems occur when i run it as I previously posted..there are no data in it...

[puT3]

sorry the same problem occur

[NeoPa]

This is more like what I need.

Creating the QueryDef from it is only important later. It should just make life easier for you when we get it right and want to move on from here.

There is still a bit more information I need from you though. I have designed the SQL based on the information you've provided for me. This means that, if it's not working perfectly there are two possible reasons :

1. I have made an error in creating the SQL (possible, we can check this).
2. The information you have passed is incorrect.

It seems that the SQL is not recognising the names of the objects listed. These have come directly from what you've told me and I have asked you to check these on a number of occasions.

What I need from you now is a list of the messages for all the prompts you get when you try to run this. Doing them one-by-one is possible, but will take much longer. From what you say it SEEMS that the errors are fundamentally simple ones of getting the names of the fields wrong. I see nothing so far that indicates the linking of the tables together might be a problem.

[puT3]

Ok,i'll do it...but i get it to you later because im at my hometown because of public holiday(independence day)....i'll give to you as soon as possible...

[NeoPa]

No worries.

I have plenty to keep me busy ;)

[puT3]

Sorry about the long break...the message box appear like this....
Enter Parameter Value....tP.Call Number
Enter Parameter Value....tP.Accession Number
Enter Parameter Value....tP.ISBN
Enter Parameter Value....tP.Title Statement
Enter Parameter Value....tP.Issue
Enter Parameter Value....tS.Subject Title
Enter Parameter Value....tP.Copyright Year
Enter Parameter Value....tP.Provider ID
Enter Parameter Value....tS.Subject ID

I think I want to give you the table of [Printing Media Library] again,see if i missed anything since i changed the name a little while the form i make directly from the change table...

Table Name=[Printing Media Library]

Field                      Type   IndexInfo
CallNumber                 String     PK
AccessionNumber            String
ISBN/ISSN                  String
Title                      String
SubjectID                  Number     FK
PublisherID                Number     FK
Physical Description Area  String
Bibliography Notes         String
Description                String
CopyrightYear              String
ProviderID                 Number     FK
CategoryID                 Number     FK
Issue/Edition              String
AuthorID                   Number     FK

Table Name=[Author]

Field             Type   IndexInfo
AuthorID          Number     PK
Author Name       String
Background        String

Table Name=[Subject]

Field             Type   IndexInfo
Subject ID
Topical Headings  String

Table Name=[Provider]

Field         Type  IndexInfo
ProviderID    Number    PK
ProviderName  String
Quantity      String

Table Name=[Publication]

Field             Type   IndexInfo
Publication ID    Number     PK
Publication Name  String
Foundation        String

Table Name=[Category]

Field          Type   IndexInfo
CategoryID     Number     PK
Category Name  String

[puT3]

i already try everything that i could think of like redo it,change the name so it is exactly as innthe table but the i have the same result..and i have approx 32 days left

[NeoPa]

Right.

This is so different from the data I was working from that there's no wonder the SQL doesn't work.

I have taken the precaution of shaving my head, to avoid the risk of pulling all my hair out. I can't bring myself to remove my beard at this time so I'll just have to watch myself.

I won't go through the importance of clear communication again, in any detail. It's enough to say that this situation has come about entirely through really bad communication from the start.

I will spend some time editing your post so that the metadata information (which describes the layouts of the tables) is laid out clearly and consistently, then I will see what I can do about posting some amended SQL for you to try.

From what you say, I understand that you have changed the name of what was the [Printing] table to be [PRINTING MEDIA LIBRARY] now. As there is already a form of that name, I can't imagine why you felt that was a good idea. This will make communication doubly difficult for you. I will be fine as I'm always careful about how I express things. You aren't, and I can only expect further problems from this. I will however continue on these lines as this is what you've indicated you have.

Lastly (for now), please check the information when I change it (you will see the difference) and make sure there are no errors. If there are any errors in this then this will cost you, and the project, time.

[NeoPa]

Having gone through looking at what you've posted I find I must compare it with what you posted previously to see if I can fix some of the more obvious errors. Some bits are missing. Some fields are flagged as FK where before they were PK. Etc.

This will take more time I'm afraid, and will further delay progress.

In future, if time is an issue for you, it would be a good idea to check what you post thoroughly for errors before you post it.

PS. Clearly this involves an element of deduction on my part so you will need to check that I haven't assumed anything incorrectly here.

[NeoPa]

For your Subject table, before, you had :
Table Name=[Subject]

Field             Type IndexInfo
SubjectID         Number   PK (where in main table ([Printing]) is PublisherID)
SubjectTitle      String
Topical Headings  String

Now you have :
Table Name=[Subject]

Field             Type   IndexInfo
Subject ID
Topical Headings  String

Either that's a mistake or you are randomly damaging your own project.
I cannot see why any sane person would add a space into the middle of a field name :S

Your posted error messages (EG. Enter Parameter Value....tS.Subject ID) are also inaccurate. Some have spaces added in where no spaces exist. I appreciate that you can't copy / paste this information in, but if you type the message out incorrectly (particularly the prompted name) then I will waste much time searching for something that doesn't exist. For someone who says they're in a hurry you certainly seem to waste a great deal of time.

I've been working for over an hour now SIMPLY fixing all your errors. Not doing anything that takes us forward at all. Simply fixing incorrect information that you have posted. I have to say that if I find much more of this I will walk away. Your attitude to my time is frankly disrespectful.

What is so frustrating particularly is that this question shouldn't even be particularly difficult. It's straightforward with the right info, producing powerful results.

[NeoPa]

Try the following SQL (as before) and tell me what, if any, errors it gives. The names in the error message must be reproduced exactly. No adding or losing of spaces please.

SELECT tPML.CallNumber,
       tPML.AccessionNumber,
       tPML.ISBN/ISSN,
       tA.[Author Name],
       tPML.Title,
       tPML.Issue/Edition,
       tS.SubjectTitle,
       tPu.[Publication Name],
       tPML.[Physical Description Area],
       tPML.[Bibliography Notes],
       tPML.CopyrightYear,
       tPr.ProviderName,
       tC.[Category Name]
 
FROM (((([Printing Media Library] AS tPML INNER JOIN
         [Publication] AS tPu
  ON     tPML.PublisherID=tPu.[Publication ID]) INNER JOIN
         [Author] AS tA
  ON     tPML.AuthorID=tA.AuthorID) INNER JOIN
         [Subject] AS tS
  ON     tPML.SubjectID=tS.SubjectID) INNER JOIN
         [Provider] AS tPr
  ON     tPML.ProviderID=tPr.ProviderID) INNER JOIN
         [Category] AS tC
  ON     tPML.CategoryID=tC.CategoryID

I have updated the names in the SQL as much as possible to match your latest posted tables. If the information provided is at least close enough to correct to enable a good guess as to what you have then this should work.

Again, the SQL is untested as yet so there is always room for errors on my part. I will only know for sure when you post back any updates as to current information. Either results of the query or known changes to the currently posted information.

[puT3]

All is my fault for not unclear information and communication...im trying your new posted SQL...but i posted the error as exactly the queries produce when i run for the first SQL u give me,i did it one by one,and write it here...

Ok I already run your new SQL in query,the result, there is no messages/prompt box appear when run it,it goes straight to datasheet however it just blank datasheet with the field name only...I believe and confident that I already give the correct information about all the table,their filed name,type and everything,there is no more changes made...if there anything else missing please let me know...

[puT3]

the row of the query has that (i dont know what it call,it appear when we want to add new words/things,when open word its there on the white paper)...when i try to enter any words,its said cannot add record(s);join key of table "Printing Media Library' not in recordset...
i attach the picture that shows the query and what i meant earlier,it is a part of it

[puT3]

I try ur SQL one by one. If I start the FROM clause from here clause

 
FROM (((([Printing Media Library] AS tPML INNER JOIN
         [Publication] AS tPu
  ON     tPML.PublisherID=tPu.[Publication ID]) 
 

until

INNER JOIN
         [Author] AS tA
  ON     tPML.AuthorID=tA.AuthorID)INNER JOIN
         [Category] AS tC
  ON     tPML.CategoryID=tC.CategoryID
 

it will shows all the data related and it works fine

but if I add this SQL

 INNER JOIN
         [Subject] AS tS
  ON     tPML.SubjectID=tS.SubjectID) INNER JOIN
         [Provider] AS tPr
  ON     tPML.ProviderID=tPr.ProviderID)
 

the datasheet will be completely blank,no data..do you think something wrong with the tables or the code?Sorry if this make you confuse or anything...
Is it okay if the table do not have any data at the moment because my [Provider] table do no have any but [Subject] do have data

[NeoPa]

I will reply to all your most recent three posts. Together, they make the situation quite clear (well done).

With INNER JOINs (as I have used) it is indeed important that all tables have matching data. If ANY of the tables is without a matching record then nothing is displayed.

The following SQL is changed such that all INNER JOINs are replaced by LEFT JOINs. This will give you more data, but it may also mean that you get some records with Null values for some of the linked fields. This may not be a problem for you, but it is important that you are aware of this and what it means.

SELECT tPML.CallNumber,
       tPML.AccessionNumber,
       tPML.ISBN/ISSN,
       tA.[Author Name],
       tPML.Title,
       tPML.Issue/Edition,
       tS.SubjectTitle,
       tPu.[Publication Name],
       tPML.[Physical Description Area],
       tPML.[Bibliography Notes],
       tPML.CopyrightYear,
       tPr.ProviderName,
       tC.[Category Name]
 
FROM (((([Printing Media Library] AS tPML LEFT JOIN
         [Publication] AS tPu
  ON     tPML.PublisherID=tPu.[Publication ID]) LEFT JOIN
         [Author] AS tA
  ON     tPML.AuthorID=tA.AuthorID) LEFT JOIN
         [Subject] AS tS
  ON     tPML.SubjectID=tS.SubjectID) LEFT JOIN
         [Provider] AS tPr
  ON     tPML.ProviderID=tPr.ProviderID) LEFT JOIN
         [Category] AS tC
  ON     tPML.CategoryID=tC.CategoryID

See how this works for you.

[NeoPa]

Would you mind also revisiting post #79 and confirming that the metadata for the [Subject] table is actually unchanged from the original (as displayed in that post).

[puT3]

Would you mind also revisiting post #79 and confirming that the metadata for the [Subject] table is actually unchanged from the original (as displayed in that post).

I changed a little bit of the field in [Subject] table.
This is the original.
Table Name=[Subject]

Field             Type IndexInfo
SubjectID         Number   PK
SubjectTitle      String
Topical Headings  String

to this
this is the new table
Table Name=[Subject]

Field             Type IndexInfo
SubjectID         Number   PK
Topical Headings  String

I just delete the SubjectTitle field. That's all that I changed

[puT3]

OK, the new SQL you posted work,it show all the data and i changed the recordsource of the form [Printing Media Library]...Sorry take so long just to make the SQL work...What is the next step...

[NeoPa]

Well, as there is a reference in the SQL to tS.SubjectTitle, I can't see how this is not throwing up a prompt at least (unless you've changed the SQL to handle it).

That line (#7) should now read :

tS.[Topical Headings]

Please let me know if this makes sense (did you change the SQL to handle this already)?

Have you made any other changes that I should be aware of (please explain any changes rather than posting the SQL you used)?

PS. In the mean time I will look at steps we can take from here. Getting the SQL working is a fundamental step (Yay :))

[NeoPa]

Right, now we look at the design of the [Search Form] form. This form will have pretty well all the logic and control built into it. In fact, this will mostly be in the [SearchRec] Command Button (I suggest you rename this to [cmdSearch] - this will make it clearer what we are working with).

[Search Form] should now have a record source of [qryPrinting] with SQL as post #84 but probably amended by post #88.

Form Name=[Search Form]

Control        Type           Values
[Search By]    OptionGroup    1 = optKeywords
                              2 = optExact
                              3 = optFirst
                              4 = optPhrase
[Search Type]  OptionGroup    1 = optTitle
                              2 = optAuthor
                              3 = optPublication
                              4 = optCall
                              5 = optSubject
                              6 = optISBN
txtFind        TextBox
cmdSearch      CommandButton

If everything I now understand is correct then the following code should go a long way to providing your solution.

Check first to see if it works.

If not then let's have the full and clear information as to what happens.

If it does, then look at the code and make sure you understand what it is doing, and why. I can explain any parts you're not sure of, and understanding the process is important for you if you are to support it going forward.

Private Sub cmdSearch_Click()
  Dim strWhere As String, strField As String
 
  '%F ==> Field to search on
  '%E ==> Equality operator
  '%V ==> Value (to search for)
  strWhere = "[%F] Like '%V'"
  Select Case Me.[Search Type]
  Case 1
    strField = "Title"
  Case 2
    strField = "Author Name"
  Case 3
    strField = "Publication Name"
  Case 4
    strField = "CallNumber"
  Case 5
    strField = "Topical Headings"
  Case 6
    strField = "ISBN/ISSN"
  End Select
  strWhere = Replace(strWhere, "%F", strField)
  Select Case Me.[Search By]
  Case 1
    strWhere = Replace(strWhere, "%V", "*" & Me.txtFind & "*")
  Case 2
    strWhere = Replace(strWhere, "%V", Me.txtFind)
    strWhere = Replace(strWhere, "Like", "=")
  Case 3
    strWhere = Replace(strWhere, "%V", Me.txtFind & "*")
  Case 4
    'Nothing here as I'm not sure what this means
  End select
  Call DoCmd.OpenForm(FormName:="[Printing Media Library]", _
                      WhereCondition:=strWhere, _
                      DataMode:=acFormReadOnly)
End Sub

[puT3]

In

Select Case Me.[Search By]
   Case 4 
 

It is for OptPhrase,I think I will skip this one,im not going to use it...you can ignore this...

[NeoPa]

Quote added retrospectively from memory as the original contents of post #90 have been completely replaced.

Yes, I fixed the SQL from tS.SubjectID to tS.[Topical Headings]...

We may be nearly there now, but making changes without telling me is the sort of thing which may still throw a spanner in the works.

I can't make intelligent decisions without correct information.

Did you see the other question? Were any other changes made?
If so, my earlier post may not work very well for you (although it should give you an example to work from at least).

[puT3]

I am trying urs code right now...Ill get it back to u

[puT3]

  '%F ==> Field to search on
  '%E ==> Equality operator
  '%V ==> Value (to search for)
 

What is these mean?
Do I need to write these one?

 ==> Field to search on
 ==> Equality operator
 ==> Value (to search for)
 

sorry I understan it now

[puT3]

  Case 1
    strField = "Title"
  Case 2
    strField = "Author Name"
  Case 3
    strField = "Publication Name"
  Case 4
    strField = "CallNumber"
  Case 5
    strField = "Topical Headings"
  Case 6
    strField = "ISBN/ISSN"
 

I dont understand this,is it the (Title,Author Name, Publication Name,CallNumber,Topical Headings and ISBN/ISSN) are from the query/table?

Why are OptTitle,OptPublication..... are not used?

strwhere =  Replace (strWhere,'%F",strField)
 

and what is the Replace function?

Another thing,I forgot 2 ask u, in this code

Select Case Me.[Search By]
 

Is it the name of the option group frame?

tq again

[puT3]

I try this code from ur post

DoCmd.OpenForm(FormName:="[Printing Media Library]", WhereCondition:=strWhere, DataMode:=acFormReadOnly)
 

but I get an error said "Expected:="

No other changes are made except for OptPhrase where I exclude it and delete it from the form...does it affect anything if I delete it?

Is this form for read only because i see the data mode is read only?
It shows result line by line right?

Do u remember from before I said that I want to have a checkbox in the form so that user can view the complete details of the search material in another form?

because I want the filtered form only shows partial of information for example,
if user click on author, in the filtered form i want it to show the author name,title,year,and ISBN number and have a checkbox next to it,so that the user will click it and another form that have full details will appear....

sorry if this will change the coding,i just want to tell u in case u forget about it....

[puT3]

(1) I already ur code, at first it seem to be running eventhough a message box appear
(2) However,the next time I try it,nothing happen when I click the Search button...what is the problem?Is it the code? I do not change anything except I change the name of the button (cmdSearch) and the text box(txtFind) to match the name in the code

I try to make a new form but it still doesnt work...Any detail that u want cuz im not sure what detail it is cuz there is no error message for (2)

[puT3]

I fixed the error but the code do not open the [Printing Media Library] form instead a Find and Replace box appear

[NeoPa]

In

Select Case Me.[Search By]
   Case 4 
 

It is for OptPhrase,I think I will skip this one,im not going to use it...you can ignore this...

Thank you for this info.

To effect this simply remove lines #31 & #32 from the code in post #89.

[NeoPa]

Case 1
  strField = "Title"
Case 2
  strField = "Author Name"
Case 3
  strField = "Publication Name"
Case 4
  strField = "CallNumber"
Case 5
  strField = "Topical Headings"
Case 6
  strField = "ISBN/ISSN"

I dont understand this,is it the (Title,Author Name, Publication Name,CallNumber,Topical Headings and ISBN/ISSN) are from the query/table?

Why are OptTitle,OptPublication..... are not used?
...

This is because when an option control is selected within an Option Group, the value of the Option Group itself reflects which of its option controls is currently selected. As there is only one that can be selected at any time, this is quite logical and makes for easier checking. It is also possible to check the vlaues of the option controls themselves.

...

strWhere = Replace(strWhere,'%F",strField)

and what is the Replace function?
...

I suggest you find the Help on this for a full explanation. Essentially though, it replaces all occurrences of the second parameter, where found in the first parameter, with the third parameter.

Debug.Print Replace("ABCDEFG", "C", "_")
AB_DEFG

...
Another thing,I forgot 2 ask u, in this code

Select Case Me.[Search By]

Is it the name of the option group frame?

tq again

Yes :)

See earlier explanation of your Select Case question.

[NeoPa]

I try this code from ur post

DoCmd.OpenForm(FormName:="[Printing Media Library]", WhereCondition:=strWhere, DataMode:=acFormReadOnly)

but I get an error said "Expected:="

No other changes are made except for OptPhrase where I exclude it and delete it from the form...does it affect anything if I delete it?
...

The code you have posted cannot run as you have posted it. You say you have made no changes to the code, yet I can immediately see that the first word of my original code on the line (Call) is missing and that you have merged the line into one from three.

Now, call me picky if you will, but I have 101 examples, in this thread, of where you've changed things you don't fully understand, without telling me what you've changed, and I've had to work out what's gone wrong.

So, for this, I want to see where my original code went wrong.

Removing OptPhrase should not cause any problems for the code as posted.

...
Is this form for read only because I see the data mode is read only?
It shows result line by line right?
...

I expect it will be read only. The query it's bound to will probably make it read only. I'm not sure what you mean when you say "I see the data mode is read only?" though. Where?

If it shows results line by line, then this is how you've designed your form. We have not discussed how this should be done as it doesn't effect what we're doing very directly. Certainly showing the results in DataSheet mode should not cause any problems.

...
Do u remember from before I said that I want to have a checkbox in the form so that user can view the complete details of the search material in another form?

because I want the filtered form only shows partial of information for example,
if user click on author, in the filtered form i want it to show the author name,title,year,and ISBN number and have a checkbox next to it,so that the user will click it and another form that have full details will appear....

sorry if this will change the coding,i just want to tell u in case u forget about it....

I don't remember that, but it's not important. You've mentioned it again now.

This is not something we should be looking at immediately. I assume you want the further detail to be shown from something found on the [Printing Media Library] form.

We're principally looking at the code on the [Search Form] for now. I don't think adding this functionality later to the [Printing Media Library] form will be a problem. It shouldn't effect what we're doing now.