OpenForm with where select based on users prompted input

[nastar1]

I have a main switchboard menu choice that would run the following:

DoCmd.OpenForm "frmQuickView", acFormDS, "qryWAbyPoc", , acFormReadOnly, acWindowNormal

However, the user gets prompted twice for the POC LastName and ultimately no records returned by frmQuickView.

qryWAbyPOC includes most fields from tblWA and only has one parameter in it prompting for the LastName which comes from tblNames. tblWA field POC is the PrimaryKey to a FK in tblNames.

I'm thinking that I should not be using the parameter query as the WHERE condition, but rather use a SQL statement. However, I can't get my head wrapped around how to draft it up.

In summary, I'd just like the user to be prompted for a lastname and have only those records related displayed in frmQuickView.

Any hints at getting started in the right direction are appreciated.

 

[TheAceMan1]

How are ya nastar1 . . .

Post the SQL of [blue]qryWAbyPoc[/blue] . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nastar1]

qryWAbyPoc is a Select Query and to get the SQL equivalent, I had to export a copy of the documentation. See below.

SELECT tblWA.WAID, tblWA.Seq, tblCLIN.CLIN & "-" & tblCONTRACTS.Name & "-" & [FY] & "-" & Format([Seq],"000")
& "-" & [Rev] AS [WA#], tblCONTRACTS.Name & " " & tblCONTRACTS.Number & " " & tblDO.Number AS [Contract#],
tblWA.Title, tblWA.Orig, tblWA.POC, tblWA.IssOff, tblWA.FY, tblWA.Rev, tblWA.Contr, tblWA.Stat, tblWA.CLIN,
tblWA.EstLabHrs, tblWA.EstLabCost, tblWA.EstMatCost, tblWA.EstTvlCost,
Format([EstLabCost]+[EstMatCost]+[EstTvlCost],"$#,###.00") AS EstWAVal, tblWA.ActCost, tblWA.EEIC, tblWA.DO,
tblWA.CDRLs, tblWA.WBS, tblWA.CRFRD, tblWA.CCaR, tblWA.Start, tblWA.Fin, tblWA.RevHist, tblWA.TaskDesc,
tblWA.RevHistDoc, tblWA.Backgrd, tblWA.Obj, tblWA.ExtCrit, tblWA.Comments, tblWA.TotHrs, tblWA.TotLabCost,
tblWA.TotMatCost, tblWA.TotTvlCost, Format([TotLabCost]+[TotMatCost]+[TotTvlCost],"$#,###.00") AS TotWAVal,
Format([TotHrs]-[EstLabHrs],"#,###.0") AS VarHrs, Format([TotWAVal]-[EstWAVal],"$#,###.00") AS VarTotVal,
tblWA.ProjOfcr, tblWA.FinalPerf, tblWA.ModBy, tblWA.DateStamp
FROM tblNAMES INNER JOIN (tblDO INNER JOIN (tblCONTRACTS INNER JOIN (tblCLIN INNER JOIN tblWA ON
tblCLIN.CLINID=tblWA.CLIN) ON tblCONTRACTS.ContrID=tblWA.Contr) ON tblDO.DOID=tblWA.DO) ON
tblNAMES.EmpID=tblWA.POC
WHERE (((tblWA.POC)=[Enter POC LastName:]));

 

[nastar1]

Anyone have examples of how to write a WHERE statement with one JOIN in it, that I could include in my OpenForm method?
Not sure if that is even possible or not.

 

[TheAceMan1]

nastar1 . . .

Sorry to get back so late! I see quite a few things wrong with the query (and in a big way). Best advise I can give you is to head on over to forum701 and post there . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886