Results 1 to 7 of 7
  1. #1
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9

    Question Help troubleshooting a VBA error on a MySQL query...

    Hi I am back with another question... This place is a great resource for knowledge!



    I have no idea what is causing this error! I'd really appreciate another set of eyes looking over it, as it's probably something so simple I completely overlooked it. But for the life of me I CANNOT find it

    Upon clicking the button that initiates this code, I get a runtime error: "No value given for one or more required parameters."

    The debugger shows the star'd line to be culprit:
    Code:
    ...
        mySQL = "SELECT [Victim], [VAdd], [VTel] FROM VicWit WHERE 'Case Name'='" & (CStr(Forms!Case!DName)) & "'"
        'MsgBox (mySQL3)
        myRS.Open mySQL '***** <--this one
        If myRS.BOF And myRS.EOF Then
            'do nothing if no witnesses
        Else
    ...
    ...and here's the entire function!
    Code:
    Public Function GenerateSubpoenaForm(strDocPath As String)
        If IsNull(strDocPath) Or strDocPath = "" Then
            Exit Function
        End If
        
        Dim dbs As Database
        Dim objWord As Object
        Dim PrintReponse
        Set dbs = CurrentDb
        
        Dim cnn1 As ADODB.Connection
        Set cnn1 = CurrentProject.Connection
        
        Dim myRS As New ADODB.Recordset
        myRS.ActiveConnection = cnn1
        Dim myRS2 As New ADODB.Recordset
        myRS2.ActiveConnection = cnn1
        
        Dim mySQL As String
        
        'get cops and stars
        Dim cops As String
        Dim stars As String
        mySQL = "SELECT [star] FROM PoliceAssignments WHERE DName='" & (CStr(Forms!Case!DName)) & "'"
        myRS.Open mySQL
        If myRS.BOF And myRS.EOF Then
            'do nothing if no police
        Else
            Do Until myRS.EOF
                mySQL = "SELECT [Officer] FROM Police WHERE STAR=" & myRS![star]
                myRS2.Open mySQL
                
                'build cops string
                cops = cops & myRS2![Officer] & vbCr
                
                'build stars string
                stars = stars & myRS![star] & vbCr
                
                myRS.MoveNext
                myRS2.Close
            Loop
        End If
        myRS.Close
        
        'get civilian witnesses
        Dim witnesses As String
        Dim telephones As String
        Dim addresses As String
        
        mySQL = "SELECT [Victim], [VAdd], [VTel] FROM VicWit WHERE 'Case Name'='" & (CStr(Forms!Case!DName)) & "'"
        'MsgBox (mySQL3)
        myRS.Open mySQL
        If myRS.BOF And myRS.EOF Then
            'do nothing if no witnesses
        Else
            Do Until myRS.EOF
        '        'build strings
                witnesses = witnesses & myRS![Victim] & vbCr
                addresses = addresses & myRS![VAdd] & vbCr
                telephones = telephones & myRS![VTel] & vbCr
                myRS.MoveNext
            Loop
            myRS.Close
        End If
        
        
        'Start MSWord
        Set objWord = CreateObject("Word.Application")
        With objWord
            .Visible = True
            .Documents.Open (strDocPath)
            'move to each bookmark, and insert correct text.
            If Not IsNull(Forms!Case!DName) Then
                .ActiveDocument.Bookmarks("defendant").Select
                .Selection.Text = (CStr(Forms!Case!DName))
            End If
            If Not IsNull(Forms!Case!CaseNo) Then
                .ActiveDocument.Bookmarks("casenumber").Select
                .Selection.Text = (CStr(Forms!Case!CaseNo))
            End If
            If Not IsNull(Forms!Case!Charges) Then
                .ActiveDocument.Bookmarks("charges").Select
                .Selection.Text = (CStr(Forms!Case!Charges))
            End If
            If Not IsNull(Forms!Case!IncDate) Then
                .ActiveDocument.Bookmarks("incidentdate").Select
                .Selection.Text = (CStr(Forms!Case!IncDate))
            End If
            If Not IsNull(Forms!Case!IncidentNo) Then
                .ActiveDocument.Bookmarks("incidentnumber").Select
                .Selection.Text = (CStr(Forms!Case!IncidentNo))
            End If
            If Not IsNull(Forms!Case!JT) Then
                .ActiveDocument.Bookmarks("jurytrial").Select
                .Selection.Text = (CStr(Forms!Case!JT))
            End If
            If Not IsNull(cops) Then
                .ActiveDocument.Bookmarks("police").Select
                .Selection.Text = (cops)
            End If
            If Not IsNull(stars) Then
                .ActiveDocument.Bookmarks("star").Select
                .Selection.Text = (stars)
            End If
        End With
        Set dbs = Nothing
    End Function
    muchas thx por favor

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this work?

    mySQL = "SELECT [Victim], [VAdd], [VTel] FROM VicWit WHERE [Case Name]='" & (CStr(Forms!Case!DName)) & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9
    nope

    Just tried it, same exact error.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Add this right after the SQL is built:

    Debug.Print mySQL

    which will print out the finished SQL to the VBA Immediate window. Examine it there and see if you spot the problem. If not, post it here. Also verify all the table and field names.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9
    Damnit. It's VName, not Victim. Careless mistake, of course! Thanks for the help guys.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    matto is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    9
    Also good call on [Case Name], as 'Case Name' does not work at all!

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

Similar Threads

  1. Replies: 0
    Last Post: 05-14-2010, 08:43 AM
  2. Export to MySQL
    By avincent in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2009, 09:48 AM
  3. Using Access and going to Mysql
    By fsmikwen in forum Programming
    Replies: 1
    Last Post: 11-26-2009, 01:15 PM
  4. Access database to Mysql
    By fsmikwen in forum Access
    Replies: 4
    Last Post: 11-16-2009, 05:40 AM
  5. How to Mirror Access in MySQL???
    By s3rvant in forum Database Design
    Replies: 0
    Last Post: 07-28-2009, 08:25 AM

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