I have a query that retrieves vendor info based on what users select from a vendor dropdown report criteria page.
This dropdown is an unbound control with the following values:
Text Value (bound to column)
Select a Vendor 0
A 1
B 2
c 3
so I bind the dropdown to the value column
I even tried a text field and typed in the value 1 and still the query didn't read the control. See, the query below I highlighted in bold where I tried to make the query point to the textbox instead. This still didn't work. the query runs fine, but once I call this query from the form, it doesn't work. But, if I typed in the actual values where I have highlighted below say I put in a 2 then when I call it from the form the query is ran and it retrieves the value.
if I have Query qryVendor :
SELECT tblRFPResults.fldVendorID, tblSubFactor.fldNarrative,
FROM tblRFP LEFT JOIN (tblFactor LEFT JOIN (tblSubFactor LEFT JOIN tblRFPResults ON tblSubFactor.fldSubFactorID=tblRFPResults.fldSubFactorID) ON tblFactor.fldFactorID=tblSubFactor.fldFactorId) ON tblRFP.fldRFPID=tblFactor.fldRFPID
WHERE (tblRFPResults.fldVendorID=Forms![frmVendorReportCriteria]![txtVendor1])
UNION 2, "" as fldNarrative
FROM (tblSubFactor Left JOIN tblFactor on tblSubFactor.fldFactorID = tblFactor.fldFactorID) LEFT JOIN tblRFP on tblFactor.fldRFPID = tblRFP.fldRFPID
ORDER BY tblFactor.fldFactorNum;
to check to see if it runs and I display a msgbox. On click even of a button I have:
Dim strSQL As String
Dim dbs As Database
strSQL= "Select fldRFPID from qryVendor "
Set rst = dbs.OpenRecordset(strSQL)
MsgBox "vendor before outputting field"
MsgBox "output Id: " & rst("fldRFPID")
The 1st message box prints out but the second one with the Id doesn't.
Can some one please tell me what could be wrong? I have access 2003. I'm going crazy trying to figure this out.
This dropdown is an unbound control with the following values:
Text Value (bound to column)
Select a Vendor 0
A 1
B 2
c 3
so I bind the dropdown to the value column
I even tried a text field and typed in the value 1 and still the query didn't read the control. See, the query below I highlighted in bold where I tried to make the query point to the textbox instead. This still didn't work. the query runs fine, but once I call this query from the form, it doesn't work. But, if I typed in the actual values where I have highlighted below say I put in a 2 then when I call it from the form the query is ran and it retrieves the value.
if I have Query qryVendor :
SELECT tblRFPResults.fldVendorID, tblSubFactor.fldNarrative,
FROM tblRFP LEFT JOIN (tblFactor LEFT JOIN (tblSubFactor LEFT JOIN tblRFPResults ON tblSubFactor.fldSubFactorID=tblRFPResults.fldSubFactorID) ON tblFactor.fldFactorID=tblSubFactor.fldFactorId) ON tblRFP.fldRFPID=tblFactor.fldRFPID
WHERE (tblRFPResults.fldVendorID=Forms![frmVendorReportCriteria]![txtVendor1])
UNION 2, "" as fldNarrative
FROM (tblSubFactor Left JOIN tblFactor on tblSubFactor.fldFactorID = tblFactor.fldFactorID) LEFT JOIN tblRFP on tblFactor.fldRFPID = tblRFP.fldRFPID
ORDER BY tblFactor.fldFactorNum;
to check to see if it runs and I display a msgbox. On click even of a button I have:
Dim strSQL As String
Dim dbs As Database
strSQL= "Select fldRFPID from qryVendor "
Set rst = dbs.OpenRecordset(strSQL)
MsgBox "vendor before outputting field"
MsgBox "output Id: " & rst("fldRFPID")
The 1st message box prints out but the second one with the Id doesn't.
Can some one please tell me what could be wrong? I have access 2003. I'm going crazy trying to figure this out.
Last edited: