A recordset based on two parameters

[Syerston]

Could someone provide a little piece of code that would allow a recordset to be returned, based on two parameters held in text boxes.
The form is frmMain;
The two text boxes are "txtStreet" and "txtNo";
The table is tblValueBand, with the fields being returned [STREET],[PROPNO] and [LRRCODE];
The parameter for [STREET] is txtStreet and [PROPNO] is txtNo.
Any help is greatly appreciated.

 

[nickjar2]

Hi,

Try:

dim sSQL as string
dim db as Database
dim rs as DAO.recordset

set db = currentdb

sSQL = "SELECT Table1.a, Table1.s, Table1.d "
sSQL = sSQL & "FROM Table1 "
sSQL = sSQL & "WHERE (((Table1.a)= '"
sSQL = sSQL & [forms]![frmMain]![TxtStreet]) & "' AND " sSQL = sSQL & "((Table1.s)= " & [forms]![frmMain]![TxtNo]))

set rs = db.openRecordset(sSQL)

if rs.recordcount<1 then
msgbox &quot;No records match&quot;
exit sub
else
rs.moveLast
rs.moveFirst
end if

msgbox rs.recordcount

Nick

 

[nickjar2]

I messed up a bit while posting:

sSQL = &quot;SELECT Table1.a, Table1.s, Table1.d &quot;
sSQL = sSQL & &quot;FROM Table1 &quot;
sSQL = sSQL & &quot;WHERE (((Table1.a)= '&quot;
sSQL = sSQL & [forms]![frmMain]![TxtStreet]) & &quot;' AND &quot;
sSQL = sSQL & &quot;((Table1.s)= &quot; & [forms]![frmMain]![TxtNo]))

How did you post in BOLD???

Nick