Results 1 to 6 of 6
  1. #1
    mwhcrew is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    4

    DoCmd.OpenQuery with Parameters?

    I have a form, ClientLookupFrm which has combo box ClientLookupCmb on it. I would like to select a value on the client lookup combo box and have it run the ClientItemQry with parameters. The parameter is that the bound column on the combo box goes to one of the values on the query, ClientAutoID. I have updated the afterupdate value to read:



    Private Sub ClientLookupCmb_AfterUpdate()

    DoCmd.OpenQuery ("ClientItemQry", , ,)
    WHERE "ClientAutoID = " & Me.ClientLookupCmb


    End Sub

    But this is not working. I clearly don't know [COLOR=blue ! important][COLOR=blue ! important]VBA[/COLOR][/COLOR] and am just fumbling through here, so please help! I have put the criteria
    [Forms]![ClientLookupFrm]![ClientLookupCmb]
    on the query (which runs well if I put the actual value in as a criteria instead of the code).

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    as far as I know Docmd.OpenQuery does not allow a Filter Criteria like Docmd.OpenReport or Docmd.OpenForm.

    I have used CreateQueryDef to create my query and then open it.
    1) I Have a table Member
    2) I want to create query Dynamically taking the Value of the Member_ID from a Text Box Text11 on my form.

    The following code is attached to a Command button:

    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click
    Dim strSQL As Object
    Dim strMySQL As String
    Dim strDrop As String


    strDrop = "Drop Table MyQuery"
    On Error Resume Next
    CurrentDb.Execute strDrop, dbFailOnError


    strMySQL = "SELECT tblmember.Member_id, tblmember.MemberName, tblmember.DOB FROM tblmember Where tblmember.Member_id=" & Me.Text11
    Set strSQL = CurrentDb.CreateQueryDef("MyQuery", strMySQL)
    DoCmd.OpenQuery "MyQuery"

    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

    End Sub


    You will notice that I make sure that Myquery is Deleted every time the code is run. This ensures that MyQuery is always created with the current Criteria.

  3. #3
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    or perhaps simply put the criteria in the query itself and not into a constructed sql statement:

    Forms!ClientLookupFrm.ClientLookupCmb

  4. #4
    mwhcrew is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    4
    thanks, i was making it too complicated. I simply needed to docmd.openquery and then have the parameters in the query. Thanks!

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Now u know both ways of doing it the easy and the complicated way. please mark the thread solved.

  6. #6
    mrashid is offline Novice
    Windows 7 64bit Access 2002 (version 10.0)
    Join Date
    Nov 2011
    Posts
    1
    I used to use Access 2003 and I wrote my own function to use the Command(ADODB) object for executing an existing query with parameter. You have to put the values for parameter in the sequence they appear in the saved query, no need to mention the names of the parameters. You may put the names of textbox containing the values for parameter but wrap that withe the function CStr(). For example CStr([txtProductType]). Here is my function:
    Public Function ExecuteQuery( _
    QueryName As String, _
    cn As ADODB.Connection, _
    ParamArray params() As Variant) As ADODB.Recordset
    Dim cm As New ADODB.Command

    cm.ActiveConnection = IIf(IsNull(cn), CurrentProject.Connection, cn)
    cm.CommandText = QueryName
    cm.CommandType = adCmdStoredProc
    Set ExecuteQuery = cm.Execute(Parameters:=params)
    Set cm = Nothing
    End Function



    Regards,

    munim

    Quote Originally Posted by mwhcrew View Post
    I have a form, ClientLookupFrm which has combo box ClientLookupCmb on it. I would like to select a value on the client lookup combo box and have it run the ClientItemQry with parameters. The parameter is that the bound column on the combo box goes to one of the values on the query, ClientAutoID. I have updated the afterupdate value to read:

    Private Sub ClientLookupCmb_AfterUpdate()

    DoCmd.OpenQuery ("ClientItemQry", , ,)
    WHERE "ClientAutoID = " & Me.ClientLookupCmb


    End Sub

    But this is not working. I clearly don't know [COLOR=blue ! important][COLOR=blue ! important]VBA[/COLOR][/COLOR] and am just fumbling through here, so please help! I have put the criteria
    [Forms]![ClientLookupFrm]![ClientLookupCmb]
    on the query (which runs well if I put the actual value in as a criteria instead of the code).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with DoCmd.RunSQL
    By Peter01 in forum Programming
    Replies: 2
    Last Post: 09-10-2009, 07:11 PM
  2. docmd.runsql not working
    By Rameez in forum Programming
    Replies: 3
    Last Post: 08-07-2009, 10:07 AM
  3. Issues with DoCmd.GoToControl
    By RaMcHiP in forum Programming
    Replies: 1
    Last Post: 05-09-2009, 08:56 PM
  4. DoCmd.OpenForm Modification
    By alsoto in forum Forms
    Replies: 6
    Last Post: 05-01-2009, 07:28 AM
  5. DoCmd.SendObject Help
    By bgreer5050 in forum Programming
    Replies: 0
    Last Post: 01-12-2007, 06:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums