Recordset/report question

[Guest]

I want to make a report that that prompts you for the department and spits
out the values in Budget.aaib & Budget.cib to a text box. When i keep
getting an error that says that there is a missing operator in the SQL part
of the code. It highlights the "budget1.open tope2" statement. Does anyone
know what i did wrong?




Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim Tope1 As ADODB.Connection
Set Tope1 = CurrentProject.Connection
Dim Budget1 As New ADODB.Recordset
Budget1.ActiveConnection = Tope1
Dim tope2 As String
tope2 = "SELECT Budget.aaib, Budget.cib,employee_department"
tope2 = tope2 + "FROM Budget, Department"
tope2 = tope2 + "WHERE (((employee_department)=[enter branch]))"

Budget1.Open tope2 'Replace this comment with a table name or SQL
statement.

'Skeleton code to loop through each record in the recordset.
Budget1.MoveFirst
While Not Budget1.EOF

'Code to perform on each record goes here.
If employee_department = "cib" Then
Text13 = Budget.cib
Else
If employee_department = "aaib" Then
Text13 = Budget1.Fields(4).Value
End If
End If

'Go to next record in recordset
Budget1.MoveNext
Wend


'Code below should only be executed when recordset is no longer needed.
Budget1.Close
Set Tope1 = Nothing
Set Budget1 = Nothing


End Sub

 

[Brendan Reynolds]

You're missing spaces between "employee_department" and "FROM" and between
"Department" and "WHERE".

You're also going to have to set the parameter ("[enter branch]")
programmatically - the user will not be prompted to enter the parameter the
way they would if you were executing this query in the UI.

 

[Guest]

That corrected my error. I created a form that contains the
employee_department parameter. It contains a button that leads you to the
report. I placed the code for the recordset for the onclick event for the
button. I set the parameter to the value of the field on the form. When i
click on the button, i get an error message telling me that there's no value
given for one or more required parameters. I highlights the same line as
before refering to the SQL statement. I took off the [enter branch prompt] in
the query that i bound the report to.

tope2 = " SELECT Budget.aaib, Budget.cib, Department.employee_department,
Budget.meeting_budget "
tope2 = tope2 + " FROM Department INNER JOIN Budget ON
Department.employee_department = Budget.employee_department "
tope2 = tope2 + " WHERE (((Department.employee_department)=forms!form1!
employee_department))"

You're missing spaces between "employee_department" and "FROM" and between
"Department" and "WHERE".

You're also going to have to set the parameter ("[enter branch]")
programmatically - the user will not be prompted to enter the parameter the
way they would if you were executing this query in the UI.

--
Brendan Reynolds (MVP)

I want to make a report that that prompts you for the department and spits
out the values in Budget.aaib & Budget.cib to a text box. When i keep
getting an error that says that there is a missing operator in the SQL
part
of the code. It highlights the "budget1.open tope2" statement. Does anyone
know what i did wrong?




Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim Tope1 As ADODB.Connection
Set Tope1 = CurrentProject.Connection
Dim Budget1 As New ADODB.Recordset
Budget1.ActiveConnection = Tope1
Dim tope2 As String
tope2 = "SELECT Budget.aaib, Budget.cib,employee_department"
tope2 = tope2 + "FROM Budget, Department"
tope2 = tope2 + "WHERE (((employee_department)=[enter branch]))"

Budget1.Open tope2 'Replace this comment with a table name or SQL
statement.

'Skeleton code to loop through each record in the recordset.
Budget1.MoveFirst
While Not Budget1.EOF

'Code to perform on each record goes here.
If employee_department = "cib" Then
Text13 = Budget.cib
Else
If employee_department = "aaib" Then
Text13 = Budget1.Fields(4).Value
End If
End If

'Go to next record in recordset
Budget1.MoveNext
Wend


'Code below should only be executed when recordset is no longer needed.
Budget1.Close
Set Tope1 = Nothing
Set Budget1 = Nothing


End Sub