Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Unanswered: Criteria iser input

    Can I setup a query to ask the user for the criteria to limit the query to. I thought I had it working but can't figure it out now.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You can put variables into a query which will then prompt the user when run, but personally I much prefer to have a form for user-inputs and then build the query dynamically. So many more advantages, though admittedly, it takes a bit more time.
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    185
    How do I put the variable in the query. I like your option two but have not idea where to start. I can't find anything in my books and don't know where to start with google for this.

    Sorry I missed the link the first time I read your post but I am still very confused.
    Last edited by Bob2119; 08-02-09 at 18:34.

  4. #4
    Join Date
    Jul 2009
    Posts
    185
    Well I got variable prompt to work. If I want to query all the records do I have create a second query without the user prompt? or is there a way to let the user enter "1 or 2" . Which works as the criteria in a Sql query.

    Still working on using a form.

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Bob2119
    Well I got variable prompt to work. If I want to query all the records do I have create a second query without the user prompt? or is there a way to let the user enter "1 or 2" . Which works as the criteria in a Sql query.
    That's what I meant by "So many more advantages", you have to enter criteria if you put it into the query. I don't think I've ever used this approach in an end-user application. If you go through the 6 part tutorial I linked to earlier you should have a pretty good idea how to do the form, at least that's the tutorial how I learned (way back in the day). If you have specific questions, post and let us know. Cheers.
    Me.Geek = True

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    How do I get a command button to run a query? My book doesn't make any sense on this part. I am assuming you add a command button on the form to run the query.

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    I got a macro to run my query on selecting my button. I have [Forms]![QueryForm]![ibatteryid] for the Query's criteria but it only asks me for input ?

  8. #8
    Join Date
    Jul 2009
    Posts
    185
    I add a text box to my form and link it in the query. If enter 1 it works just find but I want to allow "1 or 7" or "*" to allow any values to match?

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    The answers to all your questions and more can be found in the 6 part lecture I linked to in Post #2.

    Sorry, I hope that didn't come off as rude. But you really can find all the information you need there, and I guarantee you'll learn a lot more (and it may even be faster) than if I just give you all the answers.

    If you still have questions, please post what you have, what you want, and why those two things are the same thing.
    Me.Geek = True

  10. #10
    Join Date
    Jul 2009
    Posts
    185
    I down loaded the sample database and part 6 does exactly what I want. Now to just convert the code to my application.

    Thank-you very much for pointing me there. I would have taken me hours to figure it out by myself.

  11. #11
    Join Date
    Jul 2009
    Posts
    185
    I have the following code pasted and updated from the sample But I get a compile error sub or function not defined with Queryexists highlited. If I comment the if not quertyexists and less the else part of the if statement it goes ahead and sends the Select query to the query. The query statement appears right but doesn,t show any records. But that is another problem. I am so close it hurts!!!! But I am out of ideas now.

    Private Sub cmdok_Click()
    ' Pointer to error handler
    On Error GoTo cmdOK_Click_err
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strBatteryID As String
    Dim strPlaneID As String
    Dim strSQL As String
    ' Identify the database and assign it to the variable
    Set db = CurrentDb
    ' Check for the existence of the query, create it if not found,
    ' and assign it to the variable
    If Not QueryExists("FlightsBatteryQuery") Then
    Set qdf = db.CreateQueryDef("FlightsBatteryQuery")
    Else
    Set qdf = db.QueryDefs("FlightsBatteryQuery")
    End If
    ' Get the values from the combo boxes
    If IsNull(Me.cboBatteryID.Value) Then
    strBatteryID = " Like '*' "
    Else
    strBatteryID = "='" & Me.cboBatteryID.Value & "' "
    End If
    If IsNull(Me.cboPlaneID.Value) Then
    strPlaneID = " Like '*' "
    Else
    strPlaneID = "='" & Me.cboPlaneID.Value & "' "
    End If
    ' Build the SQL string
    strSQL = "SELECT Flights.iBatteryID, Flights.iPlaneID, Planes.spDescription, Flights.dDate, Flights.dTime " & _
    "FROM Battery INNER JOIN (Planes INNER JOIN Flights ON Planes.apID = Flights.iPlaneID) ON Battery.abID = Flights.iBatteryID " & _
    "WHERE Flights.BatteryID" & strBatteryID & _
    "AND Flights.PlaneID" & strPlaneID & _
    "ORDER BY Flights.dDate Desc,Flights.dTime Desc;"
    Debug.Print strSQL
    ' Pass the SQL string to the query
    qdf.SQL = strSQL
    ' Turn off screen updating
    DoCmd.Echo False
    ' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "FlightsBatteryQuery") = acObjStateOpen Then
    DoCmd.Close acQuery, "FlightsBatteryQuery"
    End If
    ' Open the query
    DoCmd.OpenQuery "FlightsBatteryQuery"
    cmdOK_Click_exit:
    ' Turn on screen updating
    DoCmd.Echo True
    ' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    cmdOK_Click_err:
    ' Handle errors
    MsgBox "An unexpected error has occurred." & _
    vbCrLf & "Please note of the following details:" & _
    vbCrLf & "Error Number: " & err.Number & _
    vbCrLf & "Description: " & err.Description _
    , vbCritical, "Error"
    Resume cmdOK_Click_exit
    End Sub

    Private Sub RunSelectedQuery_Click()
    On Error GoTo Err_RunSelectedQuery_Click
    ' If Not IsNull(Battery query) Then
    If Not IsNull(Me.QueryToRun) Then
    DoCmd.OpenQuery Me.QueryToRun, acNormal, acEdit
    'DoCmd.OpenQuery [Battery query], acNormal, acEdit
    Else
    MsgBox "You must first Enter/Select a Query to Run!"
    End If

    Exit_RunSelectedQuery_Click:
    Exit Sub

    Err_RunSelectedQuery_Click:
    MsgBox err.Description
    Resume Exit_RunSelectedQuery_Click

    End Sub

  12. #12
    Join Date
    Jul 2009
    Posts
    185
    if I remark the If Not QueryExists("FlightsBatteryQuery") Then the create set statement create works ok.

  13. #13
    Join Date
    May 2005
    Posts
    1,191
    On Part 4 of his tutorial, he gives you the code for a function called QueryExists that you have to cut and paste into your code (below your current code in the same module will work fine, or you can put it in a public module). See if that works.
    Me.Geek = True

  14. #14
    Join Date
    Jul 2009
    Posts
    185
    I don't see it in Part 4 of his tutorial but I got it from the sample mdb. Thank-you very much. I would have never of figured that out.

    Now to get my sql to work. It looks corrent and no errors but doesn,t display any records.

  15. #15
    Join Date
    Jul 2009
    Posts
    185
    Does the fields in the where statement have to be str variables? Mine are number fields. I can't seem to get it to build a working where statement. If create my own it will work. I am at a lose what else to try now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •