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.

[NeoPa]

There isn't much information in your question, but to answer the question as to whether or not a query must be created - No.

The second form can be created off a table as long as a filter is passed when it is invoked.

To be any more help I would need the question explained much more clearly I'm afraid.

[puT3]

thanks and sorry about that little information...the search form i intend to make is the one that when user click the search button, the result will appear based on the text they entered and the button they click from the two option group...the thing i need is what the coding to connect those two option group and the text box and the result that will appear on another form....my form look like this...
__________________________________________________ ________________
Search By: ()Keywords
()Exact Match
()First In Field
()Phrase

Search type: () Title () Publication () Subject ()Author () Call Number () ISBN

Find:___________
SEARCH
__________________________________________________ ________________
the search type have different field of references for example title, call number and ISBN is from table called printing,publication from table publication,author from table author and subject from table subjectheadings...

I really need the help because i need to finish it in limited time....any unclear plz let me know.....

[NeoPa]

You don't explain how these various DIFFERENT tables allow you to end up with a book (publication, whatever is designed to show on your search results form).

Other than that, though, the explanation is a lot clearer. I think we may have a workable scenario here when the details are all clarified.

[puT3]

I think what I need to achieve is when user click option button of title or call number or ISBN it will search from Printing table then the result form will display the book title,author name and call number based on what they enter in the text box

[NeoPa]

Alright, well, the first thing you need to do is to design a form bound to the [Printing] table which displays records as you'd like them shown when a book is found after the search. This form should be unrestricted in it's designed format. IE It should natively show ALL records from the table.

Let us know when this has been accomplished or if you have any problems understanding what to do.

[puT3]

Ok...I have done that,what 'unrestricted in it's designed format" mean?...what should i do next?and how i connect it with search form i created earlier? I am very sorry about this,I still learning...

[puT3]

I'm having problem understanding of what should I do with this form.and how im supposed to connect the [printing] table in the form with [author],[publication],[subject] table which it has the information that i want it to appear in the result form because in [printing] table it has only the authorID,publicationID and SubjectID of [author],[publication],[subject] table which is an autonumber.

[puT3]

i want to know how to use the radio button to search together with the textbox, i already read many example but i cant seem to understand it because most searching by combo box,but i want to use radio button in an option group.....especially about the filter

[NeoPa]

Ok...I have done that,what 'unrestricted in it's designed format" mean?...what should i do next?and how i connect it with search form i created earlier? I am very sorry about this,I still learning...

Throwing multiple questions up all at once is very rarely a good idea.

I'm quite disciplined in my approach and I can handle it ok, but you're also trying to understand what I'm telling you about the subject, so adding a whole bunch of extra posts to read through and understand in the correct sequence is likely just to make your task more complicated.

Anyway, what I meant by "unrestricted in it's designed format" was that you design a form to show all records in the table. At this stage ignore the fact that in production you may only want to see one item on the form at a time.

[NeoPa]

I'm having problem understanding of what should I do with this form.and how im supposed to connect the [printing] table in the form with [author],[publication],[subject] table which it has the information that i want it to appear in the result form because in [printing] table it has only the authorID,publicationID and SubjectID of [author],[publication],[subject] table which is an autonumber.

You shouldn't even be thinking about that at this stage. That will just make understanding the issues more complicated. One step at a time will ensure that we get there in one piece. Trust that I know what I'm doing. I may have misunderstood your requirement (I don't think so in this case - but that's always a possibility), but I know what I'm doing with the Access side of things.

[NeoPa]

i want to know how to use the radio button to search together with the textbox, i already read many example but i cant seem to understand it because most searching by combo box,but i want to use radio button in an option group.....especially about the filter

Unfortunately, as there is little context described in your question I very much doubt that I could explain it now in any way you could understand better than anything else you've read.

When we have a context that we are both aware of, then I can take you through it in such a way that I hope will be clearer for you and easier to understand. That way, you will not need to ask in future, and may even be able to help others with it at a later date, because you will fundamentally understand the concept.

[NeoPa]

I will assume that you have the form prepared, even though you didn't seem quite sure of the instructions before, and you haven't said what the name of the form is yet. If not, then we can pause the rest and go back to that until it's ready.

I will call the form [frmPrinting] for now. What we intend to do, is to open the form, and pass a filter (like a WHERE condition string) as part of the open command, which tells the form ON THIS OCCASION only to allow the showing of records which match the criteria. I know we haven't discussed the preparing of the criteria yet, but we will. It is probably the most complicated, as well as the most interesting, part I know.

For now though, we will assume that part's been done and the string is stored in a variable strFilter. The following code is an example of how the call is then done :

...
Call DoCmd.OpenForm(FormName:="frmPrinting", WhereCondition:=strFilter)
 

NB. the code in the statements AFTER the form open call will continue to execute even though the other form is open. It does NOT wait for the form to be closed again before it proceeds.

[NeoPa]

Let me know when you're fully up to speed with everything so far. Any questions can also be thrown up at this time.

When we're both at that stage then I will go into the main bit in some detail with you.

Before we get there though, I will need layouts for your main form (names of the relevant controls) and the [Printing] table (names of the relevant fields).

Here is an example of how to lay it out neatly. You can examine my post (using Reply) to get all the codes.

Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.

Table Name=[tblStudent]
 Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[puT3]

Im sorry about the too questions before....
Where should i put the code?How i can i examine your post using reply?
About the main form,is it the new form we created or the search form i previously created?I cannot see the example....

Table Name=[Printing]

Field; Type; IndexInfo
Call Number; String; PK
Accession Number; String; FK
ISBN; String
AuthorID; Number
Title Statement; String
Issue; ???
SubjectID; ???
PublisherID; ???
Physical Description Area; String
Bibliography Notes; String
Copyright Year; Date/Time
Provider ID; ???
CategoryID; ???

if it the new form,the layout is....the name is Printing Media Library

Form Name=[PRINTING MEDIA LIBRARY]

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

Find : ................................... (textbox name is Text32)

SEARCH -------search button is Command31

is this what u mean?Sorry if its not,im trying to understand...

[puT3]

PRINTING MEDIA LIBRARY

Call Number ....................
Accession Number ..............
ISBN .............................
AuthorID.............................
Title Statement..............................
Issue..........................
SubjectID..........................
PublisherID...............................
Physical Description Area......................................
Bibliography Notes...............................
Description......................
Copyright Year..........................
ProviderID.............
CategoryID .........

Find : ................................... (textbox name is Text32)

SEARCH -------search button is Command31

How can i add option group which is radio button?
Is it too soon 2 add the option group? i want to used the option button for which field to match

[NeoPa]

Im sorry about the too questions before....
Where should i put the code?How i can i examine your post using reply?
...

No worries about the multiple posts. That's mainly to keep you from getting too confused. I will however expect you to keep up with all the posts that I put in in response to all of yours. This may escalate and become a little confusing. You have been warned ;)

To get the code that I used simply click on the Reply link within the post that you're interested in. That will give you my post within the [ QUOTE=... ] tags.

Simply select the part that you're interested in and copy it. Paste and modify to your own requirements before putting it in a post.

Does that make it clearer?

[NeoPa]

I just reread my earlier reply (post #14) and found there IS NO example in there. I'm very sorry for that and I will add some in as soon as possible.

Then I'm off to bed. I'm afraid I won't get much more done on this today but I will look again tomorrow and see what I can manage.

[puT3]

That is ok...i want too add some more for the [Printing] table,i didnt write the PK and FK of the table....

Table Name=[Printing]

Field; Type; IndexInfo
Call Number; String; PK
Accession Number; String; FK
ISBN; String
AuthorID; Number; FK
Title Statement; String
Issue; String
SubjectID; Number; FK
PublisherID; Number; FK
Physical Description Area; String
Bibliography Notes; String
Copyright Year; String
Provider ID; Number; FK
CategoryID; Number; FK

[NeoPa]

Right, I have updated the relevant posts to display the data a bit more clearly now.

I have most of what I need to know right now, but just the one form has been mentioned. This isn't a problem if you wish to create a form which has the searching details as well as the display details on it. It's not what I understood you to mean originally, but it can work certainly. I need to know if this is your understanding. Call that question A.

Can you read over what I've changed in your earlier posts and confirm that I've made no mistakes as well as filling in any missing information, like what types of controls you've used on the form for displaying the various fields from your table. That's question B.

I need to know if you understand what an FK is. This is a Foreign Key. That indicates that it is a value stored in THIS table which points to the Primary Key of ANOTHER table. Is this your understanding? There seems to be a large number of FKs in your table layout. This is question C.

I'm conscious that you're itching to get to defining the Option Groups with Radio Buttons, but we need to be talking about the same things otherwise any conversation is likely to be misunderstood and go off track. You can however, try playing with the concept in a dummy form using the wizard etc if you'd like. Extra experience will do you no harm and will help you to understand things better later when we get on to that.

[NeoPa]

I'm posting this separately in order to separate out the technical discussion from some timing practicalities.

I will be away from home over the weekend. I may, or may not, get any opportunities to get on the forums in that time. I should be able to get back on to this on Monday though if I don't manage it over the weekend.

[puT3]

Right, I have updated the relevant posts to display the data a bit more clearly now.

I have most of what I need to know right now, but just the one form has been mentioned. This isn't a problem if you wish to create a form which has the searching details as well as the display details on it. It's not what I understood you to mean originally, but it can work certainly. I need to know if this is your understanding. Call that question A.

Can you read over what I've changed in your earlier posts and confirm that I've made no mistakes as well as filling in any missing information, like what types of controls you've used on the form for displaying the various fields from your table. That's question B.

I need to know if you understand what an FK is. This is a Foreign Key. That indicates that it is a value stored in THIS table which points to the Primary Key of ANOTHER table. Is this your understanding? There seems to be a large number of FKs in your table layout. This is question C.

I'm conscious that you're itching to get to defining the Option Groups with Radio Buttons, but we need to be talking about the same things otherwise any conversation is likely to be misunderstood and go off track. You can however, try playing with the concept in a dummy form using the wizard etc if you'd like. Extra experience will do you no harm and will help you to understand things better later when we get on to that.

That ok,we can continue after you are back..

For QA, I mean to make two form...first form is for the search and second form will show the result

For QB,you missed the field for AuthorID,it is a FK

For QC,i do understand what FK is...Is it ok to have many FK in one table?

thanks again......

[NeoPa]

QA- Two forms it is then. I have the information for the second form (that displays the results). Information for the first form (that you've quite cleverly illustrated in post #3) would be helpful. Names and types of the controls.

QB- A little confused. AuthorID is included in the table layout (although without the FK indicator) as well as the layout for the form. Is this all you were trying to say ("Add the FK indicator in the table")? Anyway that's done.

For QB it would also help if you could indicate what types of controls they all are. If they are all TextBox controls it will be enough to say so.

QC- It is certainly ok to have many FKs. As long as you understand what that means then I know what you mean.

[puT3]

QA- Two forms it is then. I have the information for the second form (that displays the results). Information for the first form (that you've quite cleverly illustrated in post #3) would be helpful. Names and types of the controls.

QB- A little confused. AuthorID is included in the table layout (although without the FK indicator) as well as the layout for the form. Is this all you were trying to say ("Add the FK indicator in the table")? Anyway that's done.

For QB it would also help if you could indicate what types of controls they all are. If they are all TextBox controls it will be enough to say so.

QC- It is certainly ok to have many FKs. As long as you understand what that means then I know what you mean.

For QB,i want it to be radio button so that user can click to search the field related and according to the search text...

So what will happen next?

[puT3]

I want to add another thing,I dont know if it is important to let u know now...after the search result is found in the second form,it will only show the list right?So,I want to allow the user to view the complete details of the book they are searching in another form by selecting a checkbox...

For example,if user click radio button of title and in search text they write crime..the list of the crime book will be listed,i want to put a checkbox at each lines so when the checkbox is click,the user can view the detail...

Sorry but I only have about 45 days to finish this library....

[NeoPa]

For QB,i want it to be radio button so that user can click to search the field related and according to the search text...

So what will happen next?

QB. I'm confused again.

How can you display this information from the table in radio buttons :S

Perhaps you've misunderstood the question. We currently have the table and one form (where the results are listed) defined. Except the form data doesn't show what type of controls they are (see all the ???s). I very much doubt these are all radio buttons, so can you either confirm they are all TextBoxes or say what they all are.

In case it helps I will redisplay the layout data we have so far in this post. We should ideally end up without any ???s. If there are any controls that shouldn't be in this form (used for displaying the filtered results) then a lot of time will be saved if you tell me now and save me the bother of asking what controls like [Text32] and [Command31] are doing on a form designed for displaying your filtered data. Remember, accurate and clear information is critical to our ability to work together in a productive way.

Table Name=[Printing]

Field; Type; IndexInfo
Call Number; String; PK
Accession Number; String; FK
ISBN; String
AuthorID; Number; FK
Title Statement; String
Issue; String
SubjectID; Number; FK
PublisherID; Number; FK
Physical Description Area; String
Bibliography Notes; String
Description; String
Copyright Year; String
Provider ID; Number; FK
CategoryID; Number; FK

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
Text32; TextBox
Command31; CommandButton

I presume you want this form to show data in continuous mode. In other words, more than one record on the form at a time? Call this one Question D (QD).

[NeoPa]

I want to add another thing,I dont know if it is important to let u know now...after the search result is found in the second form,it will only show the list right?So,I want to allow the user to view the complete details of the book they are searching in another form by selecting a checkbox...

For example,if user click radio button of title and in search text they write crime..the list of the crime book will be listed,i want to put a checkbox at each lines so when the checkbox is click,the user can view the detail...

Sorry but I only have about 45 days to finish this library....

I'll try to bear that in mind, but we are not ready for that yet. Trying to worry about that at this stage will only cause extra confusion.

[puT3]

I'll try to bear that in mind, but we are not ready for that yet. Trying to worry about that at this stage will only cause extra confusion.

For QD,i guess im the one confuse here because i thought this is the search form..it is the result form right?so all the control is in text box...yes i want it to be line by line,a result and another result...ok, i get it now...so that mean the search form that i did before this,i should leave it for a while right?i need to complete the filtered form first right?

[NeoPa]

Right : Next step is the Search Form.
I will start preparing some thoughts on that shortly (running out of lunchtime - hectic couple of days I'm afraid).

While I'm thinking of that though, can you confirm a couple of points for me.
Question E.
The name and layout of the Search form. It will help to know what we're talking about if we have names for all the items we're dealing with.

Question F.
Looking at the last two controls on your [PRINTING MEDIA LIBRARY] form ([Text32] & [Command31]), is it true that these are actually no part of that form at all but are part of the search form instead?

Question G.
Do you have the search form designed yet? Or do you need help designing a form with Radio Buttons grouped into Frame controls?

I have the basic concepts clear enough in my head. I just need to find some time to get them down in a post. Obviously, the more detailed information I have the more it can fit in your own situation and be easier for you to understand.

[NeoPa]

Search By: ()Keywords
()Exact Match
()First In Field
()Phrase

Search type: () Title () Publication () Subject () Author () Call Number () ISBN

I've been looking at how to get the filtering to work, but there seems to be a fairly big problem.
You give the Search Types as :
() Title () Publication () Subject () Author () Call Number () ISBN
Now this would make sense if all those fields were available in the table whose layout you posted. It would be fine if your Search Types were just :
() Title () Call Number () ISBN
Publication doesn't seem to appear anywhere and the Subject and Author fields are numbers. Presumably links into other tables you've told us nothing about. This also means that the information you've provided so far is fundamentally flawed as the form you've specified cannot be populated by the table :S

I'm sure you appreciate that unless I have a question that makes sense there is very little help I can provide that makes sense. Guesswork is not a good basis for answering questions.

[puT3]

Right : Next step is the Search Form.
I will start preparing some thoughts on that shortly (running out of lunchtime - hectic couple of days I'm afraid).

While I'm thinking of that though, can you confirm a couple of points for me.
Question E.
The name and layout of the Search form. It will help to know what we're talking about if we have names for all the items we're dealing with.

Question F.
Looking at the last two controls on your [PRINTING MEDIA LIBRARY] form ([Text32] & [Command31]), is it true that these are actually no part of that form at all but are part of the search form instead?

Question G.
Do you have the search form designed yet? Or do you need help designing a form with Radio Buttons grouped into Frame controls?

I have the basic concepts clear enough in my head. I just need to find some time to get them down in a post. Obviously, the more detailed information I have the more it can fit in your own situation and be easier for you to understand.

For QE,the name is [Search Form] & the layout i already give u previously..
its like the one before...
__________________________________________________ ____________
Search Type: () Keywords ()Exact Match () First In Field () Phrase

Search By: () Title () Publication () Subject () Author () Call Number () ISBN

Find: {textbox;name:Text64}

SEARCH button {name:searchrec}
__________________________________________________ ____________
both Search Type n Search By is radio button group....

For QF,yes both are in the search form..

For QG,I already design the form as shown in QE but i will be grateful if u can help me design the radio button into frame controls..if u dont mind...

[puT3]

I've been looking at how to get the filtering to work, but there seems to be a fairly big problem.
You give the Search Types as :
() Title () Publication () Subject () Author () Call Number () ISBN
Now this would make sense if all those fields were available in the table whose layout you posted. It would be fine if your Search Types were just :
() Title () Call Number () ISBN
Publication doesn't seem to appear anywhere and the Subject and Author fields are numbers. Presumably links into other tables you've told us nothing about. This also means that the information you've provided so far is fundamentally flawed as the form you've specified cannot be populated by the table :S

I'm sure you appreciate that unless I have a question that makes sense there is very little help I can provide that makes sense. Guesswork is not a good basis for answering questions.

Sorry i forgot to tell u this,the Publication is the PublisherID...i think ur right because that is where i got stuck,the Publication,Author and Subject field is link to another table where

Table Name=[Publication]

Field; Type; IndexInfo
Publication ID; Number; PK (where in main table ([Printing]) is PublisherID)
Publication Name; String
Foundation Year; String

Table Name=[Author]

Field; Type; IndexInfo
AuthorID; Number; PK
Author Name; String
Background; String

Table Name=[Subject]

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

[puT3]

Is it possible to have a frame control with radio button where i can search in multiple table such as Title,Call Number and ISBN in [Printing] table, Author in [Author] table, Subject in [Subject] table and Publication in [Publication] table (publication is PublisherID in main table [Printing])...

and is it possible to make the filter form of the search result from multiple table...for example

if user click the radio button of title, i want the result form to display Title,Author Name,Copyright Year

if user click the radio button of author,the result form will display Author Name,Publisher Name(Publication Name), Copyright Year,Call Number,Title

if user click publication,the result form will display Publisher Name,Author Name,Call Number,Title

if user click ISBN,the result form will display Author Name,Title,Copyright Year,Call Number

if user click Call Number,the result will display Author Name,Title,Copyright Year

Im really sorry if this is making u more confuse and bit complicated,is there any where that i can send u the actual layout or relationship table to u so u can understand it better

[puT3]

I've been looking at how to get the filtering to work, but there seems to be a fairly big problem.
You give the Search Types as :
() Title () Publication () Subject () Author () Call Number () ISBN
Now this would make sense if all those fields were available in the table whose layout you posted. It would be fine if your Search Types were just :
() Title () Call Number () ISBN
Publication doesn't seem to appear anywhere and the Subject and Author fields are numbers. Presumably links into other tables you've told us nothing about. This also means that the information you've provided so far is fundamentally flawed as the form you've specified cannot be populated by the table :S

I'm sure you appreciate that unless I have a question that makes sense there is very little help I can provide that makes sense. Guesswork is not a good basis for answering questions.

Im having problems with my database when look back at it,because i didnt insert any data to the table at first but no when i want to insert the data, i have problem with the AuthorID,PublisherID and ProviderID in [Printing] table,because when i to insert data it is a number and it is connected to another table [Author],[Publication],[Provider] table.Other problem is i when i to add new data in a form...im having problems with control of these three....im really sorry about this...really sorry...

[puT3]

Please ignore the last post i sent u about im having the problem, we can just on the search form,its already settle...sorry again...

[NeoPa]

For QE,the name is [Search Form] & the layout i already give u previously..
its like the one before...
__________________________________________________ ____________
Search Type: () Keywords ()Exact Match () First In Field () Phrase

Search By: () Title () Publication () Subject () Author () Call Number () ISBN

Find: {textbox;name:Text64}

SEARCH button {name:searchrec}
__________________________________________________ ____________
both Search Type n Search By is radio button group....

For QF,yes both are in the search form..

For QG,I already design the form as shown in QE but i will be grateful if u can help me design the radio button into frame controls..if u dont mind...

QE - The name of the form is [Search Form]. Fine. The graphical picture of what the form will look like is also clear. What I'm still missing is the names of the controls on the form (like [optKeywords; optExactMatch; optPublication; etc). However, this may need to be reconsidered somewhat in light of one of your other posts. I'll come to that later.

QF - Good. This means they are not in [PRINTING MEDIA LIBRARY] (where frankly, they made no sense).

QG - As earlier, we may need to reconsider the second one (this would have been so much easier with the name provided) as an option group at all, but I'll certainly give some pointers on setting up the first one as a functional Option Group. I need to respond to all your posts first though or we will really get in a muddle.

[NeoPa]

Sorry i forgot to tell u this,the Publication is the PublisherID...i think ur right because that is where i got stuck,the Publication,Author and Subject field is link to another table where

Table Name=[Publication]

Field; Type; IndexInfo
Publication ID; Number; PK (where in main table ([Printing]) is PublisherID)
Publication Name; String
Foundation Year; String

Table Name=[Author]

Field; Type; IndexInfo
AuthorID; Number; PK
Author Name; String
Background; String

Table Name=[Subject]

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

This changes things somewhat. The main thing that has to change is that now your [PRINTING MEDIA LIBRARY] form needs its .RecordSource property set to a query instead of the simple table [Printing]. This can either be a QueryDef (saved query) or a set of SQL. For simplicity I would suggest saving it in, and using, a QueryDef.

I should have enough information now to produce some SQL that you can save as the QueryDef. I'd better get on to that next, as pretty well everything depends on that being right.

Time is running out though (this all takes time and is a little wasteful as this is information you had when I first asked earlier in the thread). Please try to be more careful in future only to post accurate, correct information.

My lunch hour is nearly over, so I may have to continue this later on.

[NeoPa]

Is it possible to have a frame control with radio button where i can search in multiple table such as Title,Call Number and ISBN in [Printing] table, Author in [Author] table, Subject in [Subject] table and Publication in [Publication] table (publication is PublisherID in main table [Printing])...

and is it possible to make the filter form of the search result from multiple table...for example

if user click the radio button of title, i want the result form to display Title,Author Name,Copyright Year

if user click the radio button of author,the result form will display Author Name,Publisher Name(Publication Name), Copyright Year,Call Number,Title

if user click publication,the result form will display Publisher Name,Author Name,Call Number,Title

if user click ISBN,the result form will display Author Name,Title,Copyright Year,Call Number

if user click Call Number,the result will display Author Name,Title,Copyright Year

Im really sorry if this is making u more confuse and bit complicated,is there any where that i can send u the actual layout or relationship table to u so u can understand it better

The way we will handle this is that all fields in [PRINTING MEDIA LIBRARY] will show whenever you do a search. This is done by binding the form to a Query rather than to a simple table.

When I first read this, I thought you were asking to search by multiple fields, but it seems that is not what you meant. This means that both of your original Option Groups will be set up as Option Groups after all. You can forget that particular earlier comment I made.

I don't think sharing the database is a good idea here. Sure it might make it easier for me to understand you, but you'd never then learn how to ask a question properly and we'd just go through the same complications next time. It's better that you get into the habit of giving the correct information and answering questions more precisely. I can see that you are getting better at this so this is not bad news.

[NeoPa]

Option Groups

To add an Option Group simply follow these steps :

• Open the form in Design View.
• Make sure the Toolbox is visible.
• Click on the Option Group tool, then click on an empty area of your form.
• The Option Group Wizard now starts up...
• Enter in the various label names you need and click Next.
• Select a default value and click Next.
• Click Next to leave the default values as they are.
• Choose your display options and click Next.
• Enter a name for your new Option Group and click Finish.

Let me know if there is anything further you need to know about this, or even if any of this is unclear (if so then you must specify what is unclear). I will help if I can.

[puT3]

Option Groups

To add an Option Group simply follow these steps :

• Open the form in Design View.
• Make sure the Toolbox is visible.
• Click on the Option Group tool, then click on an empty area of your form.
• The Option Group Wizard now starts up...
• Enter in the various label names you need and click Next.
• Select a default value and click Next.
• Click Next to leave the default values as they are.
• Choose your display options and click Next.
• Enter a name for your new Option Group and click Finish.

Let me know if there is anything further you need to know about this, or even if any of this is unclear (if so then you must specify what is unclear). I will help if I can.

This option group is bind to the query right?
The control name for the both option group are

Search by:
()Keywords - Option26
()Exact Match - Option28
()First In Field - Option30
()Phrase - Option32

Search Type:
()Title - Check37
()Author - Check39
()Publication - Check41
()Call Number - Check43
()Subject - Check47
()ISBN/ISSN - Check49

[puT3]

I want to ask u something,how can i enter data to the field AuthorID,PublicationID,SubjectID in the main table,[Printing] which all data types are number, i want to enter text and i do not want it to be a lookup field...Im having trouble with entering data directly to these field and adding new data from a form...

And another thing,if i make the main table of [Printing] which i include all in one table like this

CallNumber;Number;PK
Accession Number;Number;FK
Author Name;Text
Title;Text
ISBN/ISSN;Text
Edition;Text
Subject;Number
Publisher;Text
Provider;Text
Physical Description Area;Text
Bibliography;Text
Description;Text
CopyrightYear;Text
CategoryID;Number;FK

Is it ok to not make the Author,Provider,Publisher in another table? is it a good database?

im thinking about because i think it will make things easier for me to enter data to the table and to make a adding new data form...but at the same time, i hope that i could settle the problem in the original database...let make me know what you think..im sorry for all this trouble

[NeoPa]

In response to post #40

You're surely keeping me busy.

This option group will NOT be bound to anything. This FORM ([Search Form])will not be bound to anything. Forms don't necessarily need a record source. The [PRINTING MEDIA LIBRARY] will need to be bound, but theis one doesn't.

[Search By] Option Group.
This seems fine except the radio buttons need proper meaningful names (optKeywords; optExact; optAtStart; optPhrase are possible names but whatever you use it's a bad idea to code with default (meaningless) names). Please arrange to get this changed. At least in here. If you want to translate from here into meaningless names yourself then that's fine, but I want nothing to do with them. They are simply a request to introduce silly mistakes into the project.

[Search Type] Option Group.
Here we start getting into strange territory. Why are there CheckBoxes in the option group instead of Radio Buttons? CheckBoxes can only work if you have multiple TextBox controls for entering the different fields to search. These need to be replaced by RadioButtons (Option controls), and be assigned meaningful names as above.

[NeoPa]

In response to post #41

I would hold off on this. This is one of those things that actually get simpler when you understand better how things work.

It is not necessary to change the design of the table, as we will not use the table directly in your ([PRINTING MEDIA LIBRARY]) form. We will set up a QueryDef which links this table to all the other tables needed. THEN, when you work with [PRINTING MEDIA LIBRARY] it will be AS IF you had changed the table.

How you have it set up now is definitely a preferable way to organise things.

[NeoPa]

As well as using the following SQL for your QueryDef (let's call it [qryPrinting]), 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. Please keep me informed of any changes to names etc.

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

I was also unable to see where the [Description] control got its data. It's included in [PRINTING MEDIA LIBRARY], but nowhere else as far as I could tell.

[puT3]

In response to post #40

You're surely keeping me busy.

This option group will NOT be bound to anything. This FORM ([Search Form])will not be bound to anything. Forms don't necessarily need a record source. The [PRINTING MEDIA LIBRARY] will need to be bound, but theis one doesn't.

[Search By] Option Group.
This seems fine except the radio buttons need proper meaningful names (optKeywords; optExact; optAtStart; optPhrase are possible names but whatever you use it's a bad idea to code with default (meaningless) names). Please arrange to get this changed. At least in here. If you want to translate from here into meaningless names yourself then that's fine, but I want nothing to do with them. They are simply a request to introduce silly mistakes into the project.

[Search Type] Option Group.
Here we start getting into strange territory. Why are there CheckBoxes in the option group instead of Radio Buttons? CheckBoxes can only work if you have multiple TextBox controls for entering the different fields to search. These need to be replaced by RadioButtons (Option controls), and be assigned meaningful names as above.

Sorry about that...
I've changed the name...

Search by:
()Keywords - OptKeywords
()Exact Match - OptExact
()First In Field - OptFirst
()Phrase - OptPhrase

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

[puT3]

As well as using the following SQL for your QueryDef (let's call it [qryPrinting]), 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. Please keep me informed of any changes to names etc.

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

I was also unable to see where the [Description] control got its data. It's included in [PRINTING MEDIA LIBRARY], but nowhere else as far as I could tell.

Sorry I missed it,[Description] is in the [Printing] table...

u missed ProviderName from the table [Provider]

Can u explain about this SQL?

[Printing] AS tP
[Publication] AS tPu
[Author] AS tA
[Subject] AS tS
I do no understand what this mean

Im using the wizard and the SQL that I have is like this

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

is this correct?

and what is the next step...

[puT3]

As well as using the following SQL for your QueryDef (let's call it [qryPrinting]), 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. Please keep me informed of any changes to names etc.

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

I was also unable to see where the [Description] control got its data. It's included in [PRINTING MEDIA LIBRARY], but nowhere else as far as I could tell.

I already try ur SQL like this;

SELECT [Printing].CallNumber, [Printing].ISBN/ISSN, [Printing].Title, [Printing].CopyrightYear, Subject.SubjectTitle, Publication.[Publication Name], Provider.ProviderName, Author.[Author Name]
 
FROM (([Printing] AS tP INNER JOIN [Publication] AS tPu 
ON tP.PublisherID = Publication.Publication ID) INNER JOIN [Author] AS tA ON tP.AuthorID = tA.AuthorID) INNER JOIN [Subject] AS tS 
ON tP.Subject ID = tS.SubjectID) INNER JOIN [Provider] AS tPr 
ON tP.ProviderID = tPr.ProviderID);
 

Error Message appear,Syntax Error (missing operator) in query expression 'tP.PublisherID = Publication.PublicationID.......

[NeoPa]

Sorry about that...
I've changed the name...

Search by:
()Keywords - OptKeywords
()Exact Match - OptExact
()First In Field - OptFirst
()Phrase - OptPhrase

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

This all seems fine.

I would just like to confirm that both the [Search By] and [Search Type] Option Groups have been created using the method illustrated above. Renaming CheckBoxes as Opt... will not be enough to fix the earlier problem.

If you need to, simply delete them both and recreate them in the way you now know.

[NeoPa]

Sorry I missed it,[Description] is in the [Printing] table...

u missed ProviderName from the table [Provider]

Can u explain about this SQL?

[Printing] AS tP
[Publication] AS tPu
[Author] AS tA
[Subject] AS tS
I do no understand what this mean

Im using the wizard and the SQL that I have is like this

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

is this correct?

and what is the next step...

There are a number of issues raised here and I will try to deal with them in order.

1. Please use the [ CODE ] tags provided. I should not be spending my time going around fixing your posts for the basics. I don't mind where I can reformat them in a different way, but simply adding the tags for you is not something you should be leaving for me to do.
2. I will add [Description] to the [Printing] table layout in post #26. I will assume a String field. {Done}
3. I missed the ProviderName from the [Provider] table?
   This is hardly surprising as you have not posted any information about this table at all. The only reference anywhere to this is an oblique one in post #34. This refers to the table but nowhere is there posted any information about this table.
   
   What you really mean is YOU missed the ProviderName from the [Provider] table. I will need proper information for this table before I can fix this oversight.
4. [Printing] AS tP (etc)
   Generally speaking, SQL is not the easiest code to read. It can get quite cumbersome and having full field references (including the table names), while often necessary, makes the reading of it all the more complicated. For this reason (and one or two others) I often supply aliases for the tables. This is very similar to showing fields with a different name in the SELECT clause. EG
   

   SELECT My_Long_Table_Name.Customer_Full_Name
   FROM My_Long_Table_Name

   can be renamed in the output by adding "AS CustName". In the same way a table can be referenced within the SQL with a more convenient and understandable alias in the FROM clause. I typically start a table name with lower-case t to indicate immediately that it's a table (and not a query for instance). This is usually followed by the initials of the name to keep it quite brief. This may result in more clear SQL code as below.
   

   SELECT tMLTN.Customer_Full_Name AS CustName
   FROM My_Long_Table_Name AS tMLTN

   As SQL gets bigger and more complicated this becomes more and more necessary to enable someone to have a chance of reading and making sense of it.
5. You may well have your SQL like that, but this is not something I intend to work with. If you want to communicate with me then you must provide SQL in the same civilised format that I have gone to the trouble of preparing for you. SQL can be copied and pasted directly into an Access QueryDef so there is no good reason for changing it in the Query design window. If changes are to be made then we both need to know about it (I'm working in the dark enough as it is thank you) and dumping the mess that Access produces just doesn't cut it I'm afraid.
6. The next step, when all the correct information has been shared, is to take the latest SQL (at whichever point), inject it into your query (we can agree to call this [qryPrinting] unless you have a name you'd prefer) and then make the [PRINTING MEDIA LIBRARY] form run from that.
   Test that this shows all the possible records you may require and that the layout and what is shown is as you'd expect for when you are running live.

[NeoPa]

I already try ur SQL like this;
...
Error Message appear,Syntax Error (missing operator) in query expression 'tP.PublisherID = Publication.PublicationID.......

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.