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