Results 1 to 3 of 3

Thread: how to call function query in access using command button

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2005
    Posts
    228

    how to call function query in access using command button

    hi guys is there any codes to call query function using command button or all i have to do is only to set the properties of the command button?

    thanks in advance

  2. #2
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: how to call function query in access using command button

    Quote Originally Posted by ayahnabunda
    hi guys is there any codes to call query function using command button or all i have to do is only to set the properties of the command button?

    thanks in advance
    in Access or from VB?

    From Access:

    VB Code:
    1. Private Sub Command10_Click()
    2. On Error GoTo Err_Command10_Click
    3.  
    4.     Dim stDocName As String
    5.  
    6.     stDocName = "Query1"
    7.     DoCmd.OpenQuery stDocName, acNormal, acEdit
    8.  
    9. Exit_Command10_Click:
    10.     Exit Sub
    11.  
    12. Err_Command10_Click:
    13.     MsgBox Err.Description
    14.     Resume Exit_Command10_Click
    15.    
    16. End Sub

    If you want to run it from VB, I'll have to research that to see if it is possible, but I know RobDog888 knows if this is possible or not.
    Last edited by Mark Gambo; Jun 18th, 2005 at 07:20 AM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  3. #3
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: how to call function query in access using command button

    What was I thinking, instead of running as Access Query by using Access why not execute the query via ADO from VB6:


    VB Code:
    1. Option Explicit
    2. Private strDataBaseName As String
    3. Private strDBCursorType As String
    4. Private strDBLockType As String
    5. Private strDBOptions As String
    6. Private rs As ADODB.Recordset
    7. Private cn As ADODB.Connection
    8. Private strSQL As String
    9.  
    10. Private Sub Command1_Click()
    11. Dim b as Long
    12.  
    13. On Error GoTo Command1_Click_Error
    14.  
    15. Me.Command1.Enabled = False
    16. strDBCursorType = adOpenDynamic  'CursorType
    17. strDBLockType = adLockOptimistic   'LockType
    18. strDBOptions = adCmdText         'Options
    19.  
    20. Set cn = New ADODB.Connection
    21. Me.MousePointer = 11
    22.  
    23. cn.Open ConnectString()
    24.    
    25.     With cn
    26.         .CommandTimeout = 0
    27.         .CursorLocation = adUseClient
    28.     End With
    29.  
    30.    
    31.     Set rs = New ADODB.Recordset       'Creates record set
    32.    
    33.     strSQL = "SELECT * "
    34.     strSQL = strSQL & "FROM TABLE1 "
    35.     strSQL = strSQL & "WHERE FIELDNAME1 ='" & Me.Text1.Text & "' "
    36.     strSQL = strSQL & "ORDER BY FIELDNAME1;"
    37.    
    38.     rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
    39.  
    40.     If Not rs.EOF then  
    41.         For b = 1 To rs.RecordCount
    42.                '<=== Do whatever you need to do with the recordset here
    43.                rs.MoveNext
    44.         Next b
    45.     Else
    46.         MsgBox "The recordset contained no Records"
    47.     End If
    48.  
    49.  Beep
    50.  
    51. rs.Close
    52. Set rs = Nothing
    53. cn.Close
    54. Set cn = Nothing
    55.  
    56. On Error GoTo 0
    57. Exit Sub
    58.  
    59. Command1_Click_Error:
    60.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command1_Click of Form " & Me.Name
    61. End Sub
    62.  
    63. Private Function ConnectString()
    64.              
    65.         ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    66.                         "Data Source=" & <Type the Path and Name of DB Here> & _
    67.                         ";Jet OLEDB:Engine Type=5;"
    68.  
    69. End Function
    Last edited by Mark Gambo; Nov 26th, 2006 at 08:35 PM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


Posting Permissions

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



Click Here to Expand Forum to Full Width