Re: Select statement in VBA

Tech-Archive recommends: Speed Up your PC by fixing your registry

In your where clause, stSearch is a String value and thus needs to have
quotes around it. By the way, I'm not as good as other people and
don't know errors by their error number. When you post, it is always
good to include the error message for mere mortals like myself. :-)


Rafi wrote:
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

.

Relevant Pages

  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)
  • RE: Recordset looping (and debug looping!)
    ... Is there any way to address this Sndx issue for multi-users? ... I added Sndx to my table, but "String" is not an available Data Type. ... Dim rst As DAO.Recordset, strNames As String ... Set rst = CurrentDb.OpenRecordset ...
    (microsoft.public.access.formscoding)
  • RE: Recordset looping (and debug looping!)
    ... I added Sndx to my table, but "String" is not an available Data Type. ... 'NEW SOUNDEX CODE FROM STEVES ... Dim rst As DAO.Recordset, strNames As String ... Set rst = CurrentDb.OpenRecordset ...
    (microsoft.public.access.formscoding)
  • RE: Need Help | Rolling out new App. | Trouble with some computers.
    ... Option Compare Database ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Ascii Line Separated File Format - Export
    ... Dim strFile As String ... Set rst = CurrentDb.OpenRecordset ... Sub WriteFld(strFile As String, strTable As String) ...
    (comp.databases.ms-access)