Do command to open a form

[mbaue002]

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

 

[Lennon]

For me work this:

DoCmd.OpenForm "NameOfForm", , , "[ID] = " & Me!ID

Hope it will help

 

[mbaue002]

I basically have 3 pull down menus that I am using as a filter in a sql statement to sort by the 3 criteria. I can get a record count, but basically am looking for a way to create some sort of form out of the records meeting the criteria. So I basically want to turn results from an sql statement into some sort of form that opens with the criteria.

 

[smig]

You can open the form as hidden, set its rowsource to the SQL, and reopen the form to show it
another option is sending the swell as openargs, and set the rowsource

 

[mbaue002]

how could I put this into code? I thought setting a docmd to run the above sql statement would open the records, but that didnt work.

 

[Pat Hartman]

The SQL needs to be saved as a querydef and used as the RecordSource for the report. When the report opens, it runs the query in it's RecordSource to obtain the data it needs. You don't run the query outside of the report. If you don't want to put the criteria into the report's RecordSource, you could do what Lennon recommended with a simple example which is to provide the criteria as a runtime argument. This method gives you quite a bit of flexibility.

 

[mbaue002]

Thanks I will give this a try.

 

[mbaue002]

okay so heres what I did..

Private Sub CmdRun_Click()
Dim db As DAO.Database
Set db = CurrentDb()
Dim rs1 As DAO.Recordset
Dim strInstrument As String
Dim sglCoupon As Single
Dim DteSettlement As Date
Dim strSQL As String
Dim qdf As DAO.QueryDef


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
Set qdf = db.CreateQueryDef("OpenAmounts", strSQL)
Else
MsgBox ("no record")
End If
Set rs1 = Nothing

End Sub

When I try to run this I get an error 3012. any ideas on how to fix this ?

 

[Pat Hartman]

Lennon gave you an easy solution. did you try it? The WHERE clause without the WHERE is used in the OpenForm (or OpenReport) methods to tell the form or report to show only the specified data.