Hi there,

I've created a form in Access that has a text field and a single button which is to search the database by matching up a Cost Center number entered into the text field, and return a number of fields in association with the number. In this case: DIVISION, BRANCH, SECTION AND SUBSECTION.

Below is the code I use in the click event. Every time I run this code it cacks on the line:

rst.Open query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

and gives me the message: -2147467259 Automation Error Unspecified Error



That tells me nothing!

For whatever reason it doesn't like my SQL statement. Actually, I have two which do the same thing. I've used both as Queries in Access and they both work. So I don't understand why the ADODB.Recordset has a problem with either one of them.

Please advise.

Alan

Code:
 
Private Sub Command2_Click()
Dim query As String
Dim rst As ADODB.Recordset
Dim division As String
Dim branch As String
Dim section As String
Dim subsection As String
Dim costcenter As String
On Error GoTo ErrHandler
Me.Text0.SetFocus
costcenter = Text0.Text
If costcenter = "" Then
   MsgBox "You must enter a value for Cost Center!", vbExclamation
   Exit Sub
'Else
   'If costcenter Then
      'Determine if the value entered by the user is numeric.
   'End If
End If
 
query = "SELECT DIVISIONS.DIVISION, BRANCHES.BRANCH, SECTIONS.SECTION, SUBSECTIONS.SUBSECTION FROM DIVISIONS, BRANCHES, SECTIONS, SUBSECTIONS WHERE (((SUBSECTIONS.SECTION_ID)=[SECTIONS].[SECTION_ID]) AND ((SECTIONS.BRANCH_ID)=[BRANCHES].[BRANCH_ID]) AND ((BRANCHES.DIVISION_ID)=[DIVISIONS].[DIVISION_ID]) AND ((SUBSECTIONS.COST_CENTER)=" & costcenter & "));"
'query = "SELECT DIVISIONS.DIVISION, BRANCHES.BRANCH, SECTIONS.SECTION, SUBSECTIONS.SUBSECTION" & _
'" FROM (DIVISIONS INNER JOIN (BRANCHES INNER JOIN SECTIONS ON BRANCHES.BRANCH_ID=SECTIONS.BRANCH_ID) ON DIVISIONS.DIVISION_ID=BRANCHES.DIVISION_ID) INNER JOIN SUBSECTIONS ON SECTIONS.SECTION_ID=SUBSECTIONS.SECTION_ID" & _
'" WHERE SUBSECTIONS.COST_CENTER=" & costcenter & ";"
Set rst = New ADODB.Recordset
rst.Open query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
If Not (rst.EOF) Then
  rst.MoveFirst
  Do While Not (rst.EOF)
    division = rst.Fields("DIVISION").Value
    branch = rst.Fields("BRANCH").Value
    section = rst.Fields("SECTION").Value
    subsection = rst.Fields("SUBSECTION").Value
    rst.MoveNext
  Loop
End If
rst.Close
Set rst = Nothing
Me.Text3.SetFocus
Me.Text3.Text = division
Me.Text5.SetFocus
Me.Text5.Text = branch
Me.Text7.SetFocus
Me.Text7.Text = section
Me.Text9.SetFocus
Me.Text9.Text = subsection
 
ErrHandler:
    If Err.Number = 3021 Then
      'There are no records for comm. devices for this particular employee
      rst.Close
      Set rst = Nothing
      Cancel = False
      MsgBox "The value privided as Cost Center " & costcenter & " does not exist.", vbInformation
      Exit Sub
    ElseIf Err.Number <> 0 Then
      MsgBox Err.Number & " Description: " & Err.Description, vbExclamation
      rst.Close
      Set rst = Nothing
      Exit Sub
    End If
End Sub