Forms and Queries Question

[purceld2]

I have a Query where 3 of the fields are linked to 3 combo boxes on a form. how can i get to kick off the query from the from. at the moment I set the values on the form the go back to the query and run it. I would like to be able to run it from the form.

Sometimes not all the values in boxes are set requiring all the values for that field to be retruned but at the moment if you do notset a value no rows are returned. how do I get round this.

thank you

 

[jwhite]

Not certain what you are asking.... but:

In a Combo Box, you set the RowSource Type to Table/Query, then in RowSource you put your SQL statement, set Columns to count of fields returned, set Column Widths for columns you want to show/hide (set to 0 to hide), and then set Bound Column to the one you want to save as the selection. If you want the Combo Box name to automatically show the first selection, add this to the .DefaultValue property: Me.YourComboBoxControlName.ItemData(0)

 

[purceld2]

Thanks for your reply, my main problem is that if a value is not set in one of the combo boxes all the data is reterned for that field. at the momment if you do not set a value in one of the combo boxes no rows are returned.

 

[jwhite]

Still don't completely understand, but by chance is this form like a Search form to then return matching records? If so, Query By Form is a good method to use. Here are some examples:

Search criteria (QBF)
Sample Search Form (QBF)
Adding a (Dynamic) Search form to a database (QBF)
SearchBox, Query By Form Example (QBF)

 

[purceld2]

Correct, Using combo boxes which determine the Search criteria. But my problem is, if I wish to leave one of the combo boxes without a value it does not return any rows, essentially I would like to have a value of "ALL" in the combo boxes or do something that has the same effect

Hope this is clearer

 

[KW99]

Sounds to me like you have a null value problem....unless I have misunderstood you.

You need to amend your data in the query SQL to something like:

WHERE (([ref for your table].[field name in table]=Forms!name of form!field in form Or Forms!name of form!field in form Is Null) And ([ref for your table].Field Name in Table=Forms!name of form!field in form Or Forms!Name of Form!Field in Form Is Null));

Just keep adding And etc etc for extra fields.

This will let you leave any field in your form blank and simply match the criteria selected.

Hope this helps.

 

[jwhite]

Adding "All" to a ListBox or ComboBox

 

[KW99]

I have a Query where 3 of the fields are linked to 3 combo boxes on a form. how can i get to kick off the query from the from. at the moment I set the values on the form the go back to the query and run it. I would like to be able to run it from the form.

You could do one of either two things to do this:

1). Put the query as a sub form in your parameter selection form

2). Put a command button on the form to open the query

I would personally go for the sub form, it looks more professional.

 

[purceld2]

Thanks KW99

I am quite new to Access can you explain what you mean by

1). Put the query as a sub form in your parameter selection form

How do you do the above

thanks

 

[purceld2]

KW99

I have the A Run Query botton on the form linked to Select Query and have the following [Forms]![Form1]![List2] in the criteria box. should i cut paste your solution replacing with Form1 and List2

Thanks for your help

 

[KW99]

Purceld2

No problem it is fairly easy.

You have made your form and query...ok.

minimise your form so that you can see your main screen and drag your query icon onto your form and place it in the detail section.

Name your subform.

Your subform is now set up.

Create a button on your form and right click to pull up properties.

On Event and On Click, open up the code builder and enter the following:

Me.nameofyoursubform.Requery

This goes between the Private Sub and End Sub bit of the expression.

Now when you go back to your form, when you click the button the query will update.

Job Done!!

 

[KW99]

KW99

I have the A Run Query botton on the form linked to Select Query and have the following [Forms]![Form1]![List2] in the criteria box. should i cut paste your solution replacing with Form1 and List2

Yes but do it in SQL view and Access will do the hard part for you. Make sure you include the Null bit.

 

[purceld2]

Thanks KW99 it works you have been very helpfull.

One small problem when I click run query botton on the form it displays the Enter Paramter Value first I then click Ok and the select Query runs as expected.

how do I stop the Enter Paramter Value being display and let just display the results.

 

[KW99]

Without looking at what you have done it is hard to say.

I suspect that you have probably got a parameter on your query, that you have not entered the null part of the expression for, or have forgotton to include on your form for parameter selection.

The clue will be what the parameter is prompting for.

Easy to do by a spelling mistake etc....

Check your query SQL

 

[purceld2]

There is one search criteria

Enter Parameter Value

Forms!Level4!List2

Sql below

ELECT Milestone_Overview.Level4, Milestone_Overview.Level5, Milestone_Overview.Parent_Project, Milestone_Overview.[Project code], Milestone_Overview.[Project title], Milestone_Overview.Description, Milestone_Overview.Inception_Comments, Milestone_Overview.Status_Comments, Milestone_Overview.Budgeted_Costs, Milestone_Overview.Project_manager, Milestone_Overview.Sponsor, Milestone_Overview.[Project Start Date], Milestone_Overview.Stage, Milestone_Overview.Status, Milestone_Overview.BU_Ref, Milestone_Overview.Project_Category, Milestone_Overview.OCCD, Milestone_Overview.CCCD, Milestone_Overview.LOB, Milestone_Overview.Last_Completed_Task
FROM Milestone_Overview
WHERE (([Milestone_Overview].[Level4]=Forms!Form1!List2 Or Forms!Level4!List2 Is Null) And ([Milestone_Overview].Level4=Forms!Form1!List2 Or Forms!Form1!List2 Is Null));

 

[jal]

I take it you have two forms, one called Form1 and the other is called Level4? Because that's what your code is saying:

Forms!Level4!List2

 

[purceld2]

Thanks Jal, problem fixed


Also thanks very much KW99 for all your help much apprecated.