Re: List Box & Query problems
From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/30/04
- Next message: Stan S.: "Re: Simple DMax lookup with "Like" filter"
- Previous message: hermie: "Re: IIF formula"
- In reply to: OscarC: "List Box & Query problems"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 30 Aug 2004 08:48:28 -0400
Queries cannot "see" any column in a combo box or list box except the bound
column (because the bound column is the value of that control).
Create a public function that gets the value of the column you want and
returns it to the query. Then use that value as a second criterion for your
query (on the Site letter field).
For example, in your query, use this as the criterion for the site letter
field:
Like GetMyColumnValue()
Create a public function in a regular module (name the module basFunction):
Public Function GetMyColumnValue() As Variant
GetMyColumnValue = [Forms]![FormName]![ListBoxName].Column(1)
End Function
--
Ken Snell
<MS ACCESS MVP>
"OscarC" <speedbird155@hotmail.com> wrote in message
news:5f46j09ili4mrc6j4ee4k81n122keegr2t@4ax.com...
> Hello All,
>
> Ok, I am working on a database application which has linked tables.
> In the front end I am creating forms/queries to do the following:
>
> The first form (frm_select_site) has a unbound listbox. The row
> source for this listbox comes from the query qry_select_site:
>
> SELECT tbl_CMN_SiteName.strSiteName, tbl_SMP_AESSamples.lngSiteNameID,
> tbl_SMP_AESSamples.dtmSubmitDate, tbl_SMP_AESSamples.dtmReturnDate
> FROM tbl_CMN_SiteName INNER JOIN tbl_SMP_AESSamples ON
> tbl_CMN_SiteName.lngSiteNameID = tbl_SMP_AESSamples.lngSiteNameID
> GROUP BY tbl_CMN_SiteName.strSiteName,
> tbl_SMP_AESSamples.lngSiteNameID, tbl_SMP_AESSamples.dtmSubmitDate,
> tbl_SMP_AESSamples.dtmReturnDate
> HAVING (((tbl_SMP_AESSamples.dtmSubmitDate) Is Not Null) AND
> ((tbl_SMP_AESSamples.dtmReturnDate) Is Null));
>
>
> So the resulting list box will look something like this:
>
> Site Name Submit Date
>
> Site A 01/08/04
> Site A 08/08/04
> Site E 09/08/04
> Site H 08/08/04
> Site H 13/08/04
>
> The list box is bound to column 2 (Submit Date).
>
> My question is:
>
> I want to be able to select one of the rows in the list box and then
> open a new form, to display more information about the selected site
> for that particular submit date.
>
> The problem I have is that if I select the second row in the example
> above (Site A 08/08/04), the query below returns the information for
> Site A with a submit date of 08/08/04, but it also returns the
> infiormation for Site H for the same submit date. The query is:
>
> SELECT tbl_SMP_AESSamples.lngAESSampleID,
> tbl_SMP_AESSamples.lngOurAESSampleID, tbl_CMN_SiteName.strSiteName,
> tbl_SMP_AESSamples.lngSiteNameID,
> tbl_SMD_SamplePoint.strSamplePointName,
> tbl_SMP_AESSamples.strQCSampleName, tbl_CMN_Suite.strSuiteName,
> tbl_SMP_AESSamples.dtmSampleDate, tbl_SMP_AESSamples.dtmSubmitDate,
> tbl_SMP_AESSamples.dtmReturnDate, tbl_SMP_AESSamples.ysnSubmit
> FROM tbl_CMN_Suite INNER JOIN ((tbl_CMN_SiteName INNER JOIN
> tbl_SMD_SamplePoint ON tbl_CMN_SiteName.lngSiteNameID =
> tbl_SMD_SamplePoint.lngSiteNameID) INNER JOIN tbl_SMP_AESSamples ON
> tbl_SMD_SamplePoint.lngSamplePointID =
> tbl_SMP_AESSamples.lngSamplePointID) ON tbl_CMN_Suite.lngSuiteID =
> tbl_SMP_AESSamples.lngSuiteID
> WHERE (((tbl_SMP_AESSamples.dtmSampleDate) Is Not Null) AND
>
((tbl_SMP_AESSamples.dtmSubmitDate)=[Forms]![frm_SMP_AES_SelectSite_ReturnDa
te_qry]![lstSelectSiteName])
> AND ((tbl_SMP_AESSamples.dtmReturnDate) Is Null) AND
> ((tbl_SMP_AESSamples.ysnSubmit)=Yes));
>
> How do I get the query only to return the information for the specific
> site and submit date selected in the list box?
>
> Many thanks,
>
> Michael
- Next message: Stan S.: "Re: Simple DMax lookup with "Like" filter"
- Previous message: hermie: "Re: IIF formula"
- In reply to: OscarC: "List Box & Query problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
