Recordset Open - Error?

if your query works when you specify a value then perhaps something wrong with your selection?

see this

WHERE (((tblProvider.ID)=[forms]![form1]![cboproviderid]));

why not put value of [forms]![form1]![cboproviderid])); into a variable
then you can debug that value
e.g

dim x
x=[forms]![form1]![cboproviderid]

then sql

debug.print x

...
WHERE tblProvider.ID = x



what do you get

also where is this code, in a module? why not pass in a value to that function
or if in form, use this

cboProvider.Value

or cboProvider.Column(1)

note, use column value if you want to specify a different value to whats displayed

rockiroads

The query works both ways! That is if I do Run while in the Query Designer or Open. But in the VBA code it does not work when I have the Forms!form1!cboProviderID but it does work when I have 2 instead of the referece to the form.

So, you're code would become this:
   Dim db as DAO.Database
   Dim rqdfQuery as DAO.QueryDef
   Dim rs as DAO.Recordset

    Set db = CurrentDb
    Set rqdfQuery = "SELECT tblProvider.ID, tblProvider.ClinicName, tblProvider.DoctorName,
tblProvider.TownshipID, tblProvider.MembershipDate, tblProvider.ClinicNumber, tblProvider.Active, * FROM tblProvider
WHERE (((tblProvider.ID)=" & [forms]![form1]![cboproviderid] & "))"
    Set rs = LoadQuery(rqdfQuery)
   

mgrattan

if this is a problem with parameter why does it work when I use "Where ID = 2" as oppose to "Where ID = forms!form1!cboProviderID" ?

did u dump the value of [forms]!... in vba code?

e.g. msgbox [forms]![form1]![cboproviderid]

what value do you get

rockiroads

yes, and it's there.

The problem has to do with the fact that you are trying to grab a parameter via a saved query; if you pass the parameter value directly by specifying an actual value or the name of the form and control, you don't need to use the Parameter property of the querydef to create the recordset.  However, since you are referencing a saved query, the parameter is unknown until that query is opened and run.  Here's a routine that will work against a saved query:

Public Sub ShowExecuteParamQuery_DAO()
  ' Comments  : Shows how to run a saved query that uses Parameters with DAO.
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field

  ' Open the database
  Set dbs = CurrentDb

  ' Open a handle to the saved query
  Set qdf = dbs.QueryDefs("Q1")

  ' Specify the parameter values by assigning values
  ' to the query's Parameter objects
  qdf.Parameters("ProviderID") = [forms]![form1]![cboproviderid]

  ' Open a recordset on the query object. Since the parameters have
  ' been set, Jet knows to replace the parameters with our values.
  Set rst = qdf.OpenRecordset(dbOpenDynaset)

  ' Close the recordset
  rst.Close
 
  ' Close the database
  dbs.Close

End Sub

mgrattan

If I want to reconstruct the sql in vba then I don't need the LoadQuery, as this will work as is.

Namely:

    Dim rs As New ADODB.Recordset
    Dim strSQL As String
   
    strSQL = "SELECT tblProvider.ID, tblProvider.ClinicName, tblProvider.DoctorName, tblProvider.TownshipID, tblProvider.MembershipDate, tblProvider.ClinicNumber, tblProvider.Active, * FROM tblProvider WHERE (((tblProvider.ID)=" & [Forms]![form1]![cboProviderID] & "))"
   
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = CurrentProject.Connection
    rs.CursorType = adOpenStatic
    rs.Open strSQL


But have situations where I can't or don't want to redo the sql in vba, and want to run it from it's stored place.

That's correct; you don't need to use the LoadQuery function or the routine I just posted if you reconstruct the SQL in VBA.  Otherwise, if you want to use the saved query instead, use the routine I just posted as a guide.

doesnt make sense if you say [forms]![form1]![cboProviderID] has a value
did it make any difference by using another variable like suggested? or by passing in the value instead of referencing the form.

mgrattan,

when I try you last code I get an error "Item not found in this collection" on this line:
 qdf.Parameters("ProviderID") = [Forms]![form1]![cboProviderID]

rockiroads


doesn't make sense... well that is why I am asking. Does not make sense to me either. the fact is that in the code when trying to open a query or do a select on a saved query that contains "Where ID=2" it works but if "Where id=forms!form1!cboProviderID" then it does not work.

just a thought, the form is still open and has not closed when that vba code is run, right?
I guess so if you put diagnostics in the VBA code to display the value

try this, just to check for null values

WHERE NZ([Forms]![form1]![cboProviderID],2)

Basically, if no value/null value then use 2

what happens?

As I mentioned in my 1:26 p.m. post, it has to do with the saved query requiring a parameter.  "Where ID = 2" is not a parameter, it is a Where condition.  When you reference a value that must be passed from something outside the query, then you are using a parameter and the saved query cannot be run from VBA if it is expecting a parameter unless you reference the parameter and give it a value.  See the routine I posted in the 1:26 p.m. post that shows how to handle parameters in a saved query.