I have set a string equal to sql to match records to pull down menus. This works fine and I am able to get a record count, but I want to run this and have the records meeting these criteria open in a form. I figured I could use a Docmd. to achieve this, but am not having any luck. Any suggestions? my code is below.
sglCoupon = Me.LstCoupon
strInstrument = Me.LstInstrument
DteSettlement = Me.lstSettlement
strSQL = "select Tbl4.* from Tbl4 WHERE Tbl4.[Open Amount] > 0 and Tbl4.[Coupon]=" & sglCoupon & " and Tbl4.[Instrument Name] = '" & strInstrument & "' and Tbl4.[Settlement date]= #" & DteSettlement & "#"
Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs1.RecordCount <> 0 Then
DoCmd.RunSQL (strSQL)
rs1.Close
Else
MsgBox ("no record")
End If
Set rs1 = Nothing
sglCoupon = Me.LstCoupon
strInstrument = Me.LstInstrument
DteSettlement = Me.lstSettlement
strSQL = "select Tbl4.* from Tbl4 WHERE Tbl4.[Open Amount] > 0 and Tbl4.[Coupon]=" & sglCoupon & " and Tbl4.[Instrument Name] = '" & strInstrument & "' and Tbl4.[Settlement date]= #" & DteSettlement & "#"
Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs1.RecordCount <> 0 Then
DoCmd.RunSQL (strSQL)
rs1.Close
Else
MsgBox ("no record")
End If
Set rs1 = Nothing