Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Pass parameter to VBA stored query - access

    Good evening,
    I am new to programming in Access and just trying to figure out how to get query a stored query and pass a parameter

    Code:
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "sp_getWOInfo"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
    
    Set Param = .CreateParameter("@wo", adVarChar, adParamInput, , 255)
        .Parameters.Append Param
        .Parameters("@wo") = Me.txtWO.Value
    End With
    
    rs.Open cmd
        
    If rs.RecordCount < 1 Then
        MsgBox "No file found", vbOKOnly, "No file found"
    Else
        GLBL_wonumber = Me.txtWO.Value
        DoCmd.OpenForm "frmMain", acNormal, , , acFormReadOnly, acDialog
    End If
    
    Set rs = Nothing
    Set cmd = Nothing
    Any help would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What happens now when you run this code (faulty line, error message)?

    By the way, this line:
    Code:
    If rs.RecordCount < 1 Then
    will never work. You need to use the EOF and BOF properties of a RecordSet to know if it's empty or not.
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    What happens now when you run this code (faulty line, error message)?

    By the way, this line:
    Code:
    If rs.RecordCount < 1 Then
    will never work. You need to use the EOF and BOF properties of a RecordSet to know if it's empty or not.
    Wow, I just had to change
    Code:
    Set Param = .CreateParameter("@wo", adVarChar, adParamInput, 255)
    and
    Code:
    If rs.EOF = True Then
    Thank you very much!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    May 2011
    Posts
    5
    Okay, I've hit another snag with parameters.

    SELECT tblwo.ktblWoID, tblContactInfo.sOrderStreet
    FROM tblContactInfo INNER JOIN tblwo ON tblContactInfo.ktblwoID=tblwo.ktblwoID
    WHERE (((tblContactInfo.sOrderStreet) Like "*" & [@street] & "*"));

    Code:
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim param As New ADODB.Parameter
    
    
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "sp_getWOsbySTREET"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
       
    Set param = .CreateParameter("@street", adVarChar, adParamInput)
        .Parameters.Append param
        .Parameters("@street") = Me.txtStreetName.Value
    End With
    
    rs.Open cmd
          
    If rs.EOF = True Then
        MsgBox "No file found", vbOKOnly, "No file found"
    Else
            Me.lstSearchResult.ColumnCount = 2
        Do Until rs.EOF
            Me.lstSearchResult.AddItem (rs.Fields("ktblwo").Value & ";" & rs.Fields("sOrderStreet").Value)
            rs.MoveNext
        Loop
    End If
     
    rs.Close
     
    Set param = Nothing
    Set rs = Nothing
    Set cmd = Nothing
    But it pulls nothing; but ill run the query inside access and it pulls 2 records (for string i use in both)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Standard wildcards characters in SQL are % and _, while Access uses * (instead of %) and ? (instead of _)
    Have a nice day!

  7. #7
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    Standard wildcards characters in SQL are % and _, while Access uses * (instead of %) and ? (instead of _)
    Sinndho,

    You have no idea how much I appreciate your help! Thanks again.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

Posting Permissions

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