October 24, 2005 at 7:39 am
Hi im trying to open a query from a form that displays all rows that meet the criteria set out by 3 combo boxes within the form. I have written the following Vba/sql but am getting
"error '91' Object variable or with block variable not set. (the red line is where the fault appears.
Can anyone help, if i can solve this problem then i can get on with my work.
Private Sub next_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim StrSql As String
Set db = CurrentDb
Set qdf = db.QueryDefs("AwnQry")
StrSql = "SELECT AWNING.*" & _
"FROM AWNING" & _
"WHERE AWNING.Manufacturer = '" & Me.cboManuf.Value & " ' " & _
"AND AWNING.Model = '" & Me.cboMake.Value & " ' " & _
"AND AWNING.Size = '" & Me.cboSize.Value & " ' " & _
"ORDER BY AWNING.RetailValue;"
qdf.SQL = StrSql
DoCmd.OpenQuery "AwnQry"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing
End Sub
Cheers
Armo (desperate)
October 24, 2005 at 8:03 am
You can't use your own query definition and a stored definition at the same time.
If you are using a stored query, then you must set the parameter values.
October 24, 2005 at 8:37 am
Im not 100% sure what you mean and how to go about it?
October 24, 2005 at 8:43 am
Is the query you are executing exists as a saved query?
October 24, 2005 at 8:50 am
yes, its just a basic sellect all query.
October 24, 2005 at 8:53 am
Here's an exemple of what you have to do. This returns a scalar vaue but it could as well return a recordset or not be used as a function at all.
Public Function GetDifferentielCote(iVictoires As Integer, iDefaites As Integer, iCoteAdversaire As Integer, iCoteJoueur As Integer, iMargeErrorCoteAdversaire) As Variant
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim MyRs As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("vwGetDifferentielCote")
qdf.Parameters("Victoires").Value = iVictoires
qdf.Parameters("Defaites").Value = iDefaites
qdf.Parameters("CoteAdversaire").Value = iCoteAdversaire
qdf.Parameters("CoteJoueur").Value = iCoteJoueur
qdf.Parameters("MargeErrorDifferentielCote").Value = iMargeErrorCoteAdversaire
Set MyRs = qdf.OpenRecordset
If Not MyRs.EOF Then
GetDifferentielCote = MyRs.Fields("DifferentielCote").Value
Else
GetDifferentielCote = Null
End If
MyRs.Close
Set qdf = Nothing
Set db = Nothing
Set MyRs = Nothing
End Function
October 24, 2005 at 9:15 am
Thanks that makes much more sense than all the examples i have found. Will give it a go.
Cheers
Armo
October 24, 2005 at 9:17 am
HTH.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply