List Box & Query problems

[OscarC]

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_ReturnDate_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

 

[Ken Snell [MVP]]

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>

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

 

[OscarC]

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

Thanks Ken, works a treat!