R
Rafi
I have the following Select statament in VBA which is returning a Run-Time
error 3061. The final intent is to populate a continuous form with the
records set however, for now I just need help getting the Select statement to
execute.
The Sub stops on the line -- Set rst = db.OpenRecordset(strSQL)
The value in stSearch looks like 28-1-2-23
Thanks
Private Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim intSearchlen as Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value _
& "-" & Me.Territory.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = " &
stSearch & ");"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
MsgBox ("Reference #: " & rst.Fields.[Adjustment Reference Number])
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
End Sub
error 3061. The final intent is to populate a continuous form with the
records set however, for now I just need help getting the Select statement to
execute.
The Sub stops on the line -- Set rst = db.OpenRecordset(strSQL)
The value in stSearch looks like 28-1-2-23
Thanks
Private Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim intSearchlen as Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value _
& "-" & Me.Territory.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = " &
stSearch & ");"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
MsgBox ("Reference #: " & rst.Fields.[Adjustment Reference Number])
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
End Sub