One form with 3 different options

[Compufreak]

Hi there

I am a newbie to access 2007. I would just like to know if it is possible for me to make one form with 3 different options i.e i have a start date and end date and a combo box. if the user enters only for the start date and end date , and clicks the run button, it must pull the query for that specific item and display the data in a report for that only. if a user selects items from a combo box it must show only data for that specific combobox selection. also if the user selects the start date and end date and combobox it must display for that selection.

As said before i am a newbie to access but i am catching on really quick. i would truly appreciate your help with an example .

Thanks again


Compu

[dbdesign]

RecordSource will open a form with the Query or Table you specify.

DoCmd.OpenForm "Form1"

If Whatever Then
Me.RecordSource = "Query1"
ElseIf SomethingElse Then
Me.RecordSource = "Query2"
Else
Me.RecordSource = "Query3"
End If

[JoeM]

Another option is to use VBA behind the scenes to actually build the SQL code for your query, and then run that.
It is really easier than it sounds. If you take query, you can change it to SQL view and see what the SQL code for that query looks like. That is what you have to recreate in your VBA code. Most of it will be the same, it is just the criteria that is changing, based on your selections.

Once you have built the SQL code, you just assign it to an existing query, then open that query. Something like this: https://www.accessforums.net/program...2-a-26332.html

[Compufreak]

thanks for the speedy response.
basically i have one report and this report must pull data based on the parameter selection in the form.
your help is appreciated

[JoeM]

Did you try my suggestion?
If you are having pronlems, let us know where you are getting stuck.

[Compufreak]

hi joem

yes i tried it but still abit confused , here is what my sql code looks like:

SELECT Cluster_Dept.ID, Clusters.Cluster_Desc, Department.Dept_Desc, Source.Day_Month_Year, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Flag
FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID
WHERE (((Clusters.Cluster_Desc)=[Forms]![search by cluster]![Combo9]));

N.B i have 3 different queries to perform the actions that i require , so this is where i am getting confused. if you require my other queries i will most definately post it to you.

Thanks again

[JoeM]

OK, let' say that you have a selection Form where you enter in the criteria. Let can this Form "frmSelect",
For your specific example, let's create an unbound Combo Box and named it "cboDesc".
For the Form we want to return (based on our criteria selection), let's call that "frmEntry". And let's say the source of that form is a query name "qryEntry".

So, on "frmSelect", let's also have a command button that you click after you enter your criteria when you want to return your entry Form. Let's call this "cmdOpenForm".
On the Click event of "cmdOpenForm", your VBA code would look something like this:

Private Sub cmdOpenForm_Click()

    Dim mySQL as String

'   Set default part of SQL string without criteria
    mySQL= "SELECT Cluster_Dept.ID, Clusters.Cluster_Desc, Department.Dept_Desc, Source.Day_Month_Year, Source.Original_Source, Source.Headline, " & _
                "Source.Issue, Source.Analysis, Source.Action, Source.Flag "& _
                "FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) " & _
                "ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID "

'   Check for criteria and add if necessary
    If Len(cboDesc & "X")>1 Then
        mySQL=mySQL & "WHERE Clusters.Cluster_Desc=" & Chr(34) & cboDesc & Chr(34) & ";"
    Else
        mySQL=mySQL & ";"
    End If

'   Assign SQL code to query that the entry form uses
    CurrentDQ.QueryDefs("qryEntry").SQL=mySQL

'   Open the entry Form
    DoCmd.OpenForm "frmEntry"

End Sub

[Compufreak]

thanks for your repsonse .

just a few problems i am having . I Have a query that will will use the combobox, must i rename the query to qryEntry?

[Compufreak]

this project is really messing with my brains . All i need is just one form to open one report. In my form i have 3 options such as :
start date
End date
and a combobox together with two buttons called runreport and cancel.

at the moment i have 3 queries .

query1 brings in information based only on just the start date and end date entry.
query2 brings in information based only on just the combobox selection
query3 brings in information based on start date and end date and the combobox selection.

hope by me explaining this better , you will be able to help me

Thank you again

[JoeM]

We are going about this from a different angle. Instead of having the query use the Combo Box from the Form in the query, we are actually building the necessary SQL code using the Selection Form, and physically updating the SQL code of that query each time (kind of like "hard-coding" it, but it is still dynamic, because the query is re-created every time via the Selection Form).

The names of the objects (queries, etc), are whatever you want them to be. You just need to update the code I provided accordingly to reflect that.

[JoeM]

at the moment i have 3 queries .

query1 brings in information based only on just the start date and end date entry.
query2 brings in information based only on just the combobox selection
query3 brings in information based on start date and end date and the combobox selection.

hope by me explaining this better , you will be able to help me

You don't need three queries, because we are building the query on the fly!

You saw how we added in the first criteria. We checked to see if there was anything in that combo box, and if there was, we added it to the SQL code of our query. Likewise, you will want to do the same for the other criteria. If you are unsure of how to write the criteria, just open up one of your pre-existing queries that you had made that uses that criteria, switch to SQL View, and note how it is written. That is the SQL code we want to create via VBA.

If you are having trouble getting one to work, you can add a line in to the VBA code after completing building mySQL like this:

MsgBox mySQL

This will bring the code your created to a Message Box, and you can compare it against the SQL of you original query to see where the discrepancies lie.

BTW, I had added an Open Form command at the end, but it sounds like you actually want Open Report instead. Not a big deal, it follows the same logic (make sure the SQL code we are building gets applied to the query that is the source file of the Report you are trying to open).