Put results of query into a form based on a value in form

[Handford]

I am a bit of a novice and trying to produce database. I need to put the results of a query into a form. However the query itself depends on a value selected in a combo box in the same form.

 

[Remou]

Hi
Perhaps you could post the SQL for the query?

 

[Handford]

Ok yes,

I have got a form called Booster_Main_Page with a combo box called Booster_Combo. I would like the results of this query to apear in the same farm. Here is SQL code:

SELECT [Boosters].[Booster_ID], [People_List].[Ohter]
FROM People_List INNER JOIN (Boosters INNER JOIN Booster_Att_LNK ON [Boosters].[Booster_ID]=[Booster_Att_LNK].[Booster_ID]) ON ([People_List].[Forename]=[Booster_Att_LNK].[Forename]) AND ([People_List].[Surname]=[Booster_Att_LNK].[Surname])
WHERE ((([Boosters].[Booster_ID])=[Forms]![Booster_Main_Page]![Booster_Combo]));

Thanks...

 

[Remou]

Here is one idea. Set up your form to use the query without the Where bit. Then add a little code:

Private Sub Booster_Combo_AfterUpdate()
Me.RecordSource = "SELECT [Boosters].[Booster_ID], " _
                & "[People_List].[Other] " _
                & "FROM (Boosters INNER JOIN Booster_Att_LNK " _
                & "ON [Boosters].[Booster_ID]=" _
                & "[Booster_Att_LNK].[Booster_ID]) " _
                & "INNER JOIN People_List " _
                & "ON ([Booster_Att_LNK].[Surname]=" _
                & "[People_List].[Surname]) " _
                & "AND ([Booster_Att_LNK].[Forename]=" _
                & "[People_List].[Forename]) " _
                & "Where [Boosters].Booster_ID = " _
                & Me.Booster_Combo
End Sub

 

[Bullschmidt]

Here's something I put together that may give you some ideas about making time periods and other criteria dynamic although the criteria and output forms are two different forms.

The free downloadable sample database at

http://www.bullschmidt.com/access

uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of * and not allow the search fields to be blank.

J. Paul Schmidt, Freelance Web and Database Developer

http://www.Bullschmidt.com

Access Database Sample, Web Database Sample, ASP Design Tips