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