Query

[kpouya]

Anyone could tell me how i could have a form, that runs a query and returns the result in a report form? it seems simple but i can't get it to work.

so basically the criteria for the form should be a Drop down Menu, that gets its information (Drop down) from another table, and from that information, runs the query.

Also

if you could tell me how i could with words, rather drop down menu. So basically using text book. i would appreciate it.

[PianoMan64]

@kpouya

Hey kpouya,

Welcome to Bytes.

I'm assuming this is your first post. One of the big things you're going to have to understand here is that we know nothing about what it is that you're trying to do with the exception of what it is you write. Since you aren't describing anything in detail at all, I'm assuming you haven't even started the project yet.

What we're going to need to know is:

1. Current Table structure that would allow us to build an example of what you're explaining? Exmaple:

TableName
-----------------------------------------------------
FieldName    Field Type    Description of Field
-----------------------------------------------------
ID              Number(12)    Primary Key Value
FirstName       String(15)      First Name of Customer
LastName        String(30)      LastName of Customer
 
 
and so on...
 

2. You're going to have to explain how you've built the form. What controls are on the form and what it is that you want it to do, or not do on the form.

3. Explain the steps you've taken, and where you're having problems.

This way, you'll find a great deal more people willing to help you out, if you can do a lot of the leg work.

This is not intended to be a free Developer network, its simple users helping other users with issues so they can learn how to do things.

With all that said, try putting all this information together, and I'd be more than happy to help you out.

Thanks,

Joe P.

[NeoPa]

@kpouya
Perhaps it would help you to understand if I explain that you don't run a query then convert it into report form. What you do is design the report and include the query as the Record Source. That way, any time you run the report, it necessarily runs and uses the query.

[kpouya]

Hey Joe P.
So what i am trying to develop is a knowledge database. i will try my best to explain the structure i got..

tbl_articles
article name
article year
article author
article publisher
article field (business, science, social science, arts, etc)
article subfield (so let say i got choose business, then i would have like accounting, marketing, management, etc)
article type (so like research, journal, review)
article sumary
article keywords

frm_articleentry
article name (text box)
article year (drop down)
article author (text box)
article publisher (text box)
article field (drop down)
article subfield (gets it values from a table, and its values are based on selection from article field) [drop down]
article type (drop down)
article summary (text box)
article keywords (text box)

so i want to do query, where i should be able to do a search based on the keyword(s), based on the person's keyword

and also

i want to be able to choose a field then a subfield then do a query based on that

possible?

[kpouya]

anyone is able to help?

[PianoMan64]

@kpouya
Hey kpouya,

Sorry for not getting back to you sooner. It seems that the instructions that I gave you were a little unclear as to what it is that I was looking for.

The information that I'm trying to find out is what have you done (Built) so far and where are you having the issue?

Since you didn't include field types in your answer that would be somewhat helpful to trying to solve your problem.

Like NeoPa had said, can you include a description of what it is that you have so far, where you're having problems, and what specificy the question(s) that you have about this process.

You have to understand that building a query is simply a set of instructions that combine and set of one or many tables into some form that is displayed in order to answer a question. It seems you're trying to write the query before you've even written the table structure.

From what I read on your last answer, it seems you have ONE table that has all the items. You want to be able to filter those items in some order, and display them on screen, or in a report?

You want to be able to enter those filtered items on the form?

1. If that is the case, then you're going to need to build a query that displays every record in the order that you want it in.

2. Then for the fields on the form, your going to have to do something like:

 
docmd.OpenReport "ReportName",acViewNormal,,"[Artical Field]='" & me.ArticalField & "' AND [Artical Subfield] = '" & me.ArticalSubField & "'"
 
 

[kpouya]

Hello PianoMan64

Thank you for your replay. I will try to give you a better description this time.

i have built the following items:

tbl_articles
this is where all the articles information gets recorded.

article_name
article_year
article_author
article_publisher
article_field (business, science, social science, arts, etc)
article_subfield (so let say i got choose business, then i would have like accounting, marketing, management, etc)
article_type (so like research, journal, review)
article_sumary
article_keywords

frm_articleentry
this form help the person to enter article information to get stored into tbl_articles

article_name (text box)
article_year (drop down)
article_author (text box)
article_publisher (text box)
article_field (drop down)
article_subfield (gets it values from a table, and its values are based on selection from article field) [drop down]
article_type (drop down)
article_summary (text box)
article_keywords (text box)

so what i want to do is do both of the following

1. create a form based on above information so the person can run a query that allows the person select a field then a subfield and run a query based on the information.
so it would be something like

article_field (drop down)
article_subfield (drop down)
and run a query button.

i know how to set up the form, but what i want to know is how do i get the button to run the query based on information selected from the drop downs.

2. i want to create a form where the person text box input. so the person can let says put a word in the text box and run a query based on it. so lets think of it as search engine. so therefore how can i create a search engine with access? is that even possible?

[PianoMan64]

@kpouya

Hey kpouya

The simplest way you're going to do what you're speaking of, is to create a string that contains only the criteria. Then take that criteria and use it to open the report that you're speaking of.

EXAMPLE:

Private Sub cmdSearch_Click()
     Dim strCriteria as String
     If Not IsNull(me.Artical_Field) Then
          If Len(strCriteria) > 0 Then
               strCriteria = strCriteria & " AND [Artical_Field]='" & me.artical_field & "'"
          Else
               strCriteria = strCriteria & "[Artical_Field]='" & me.artical_field & "'"
          end if
     end if
     If Len(strCriteria) > 0 Then
          DoCmd.OpenReport "ReportName", vbViewNormal,, strCriteria
     End if
End Sub
 

That should give you enough to get you started.

Let me know if you need any more help,

Joe P.