-
How to use a stored procedure on a form
Access 2003 form. SQL 2000 Server
The procedure
Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
does load all of the records into the four combo boxes correctly.
The procedure
Activate_Stored_Procedure_To_Obtain_Rows_For_Subfo rm()
is the problem.
When I clicked on one of the combo box, the subform supposed to reload the records but instead I got an error
"Run-Time error 8145. P1 is not a parameter for procedure"
Obtain_Records_For_Subform_Selector
THE YELLOW HIGHLIGHT THE CODE Below
Me.Selector_Sub_Form.Form.RecordSource = SQL_Subform
///////////////////////////////////
Private Sub Form_Load()
Me.Dept = Null
Me.so = Null
Me.Sectionno = Null
Me.Item = Null
'Execute a stored procedure in SQL_2000_Server
Me.Dept.RowSource = "Exec [get_Dept_ID]"
Me.Selector_Sub_Form.Form.RecordSource =
"Exec [Obtain_Records_For_Subform_Selector]"
End Sub
///////////////
Private Sub Dept_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
End Sub
Private Sub SO_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
End Sub
Private Sub Item_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
End Sub
Private Sub Sectionno_AfterUpdate()
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
End Sub
//////////////
Private Sub Activate_Stored_Procedure_To_Obtain_Rows_For_Subfo rm()
Dim SQL_Subform As String
Dim strStep As String
SQL_Subform = "Exec [Obtain_Records_For_Subform_Selector]"
strStep = ""
If Not IsNull(Me.Dept) Then 'Department
SQL_Subform = SQL_Subform & " @Department = " & "'" & Me.Dept & "'"
strStep = ","
End If
If Not IsNull(Me.so) Then 'SO_Number
SQL_Subform = SQL_Subform & strStep & " @SO_Number = " & "'" & Me.so & "'"
strStep = ","
End If
If Not IsNull(Me.Item) Then 'Item_Number
SQL_Subform = SQL_Subform & strStep & " @Item_Number = " & "'" & Me.Item & "'"
strStep = ","
End If
If Not IsNull(Me.Sectionno) Then 'Section_Number
SQL_Subform = SQL_Subform & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
strStep = ","
End If
'Obtain all records that match the criteria
Me.Selector_Sub_Form.Form.RecordSource = SQL_Subform
THE YELLOW HIGHLIGHT THE CODE ABOVE
End Sub
////////////
This procedure does loads all records into the combo box
correctly.
Private Sub Activate_Stored_Procedure_To_Obtain_Rows_For_Combo
Dim SQL_Department As String
Dim SQL_SO As String
Dim SQL_Item As String
Dim SQL_Section As String
Dim strStep As String
SQL_Department = "Exec [Get_Department_1] "
SQL_SO = "Exec [Get_SO_Number_1] "
SQL_Section = "Exec [Get_Section_Number_1] "
SQL_Item = "Exec [Get_Item_Number_1] "
strStep = ""
If Not IsNull(Me.Dept) Then 'Department
SQL_Department = SQL_Department & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_SO = SQL_SO & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_Item = SQL_Item & strStep & " @Department = " & "'" & Me.Dept & "'"
SQL_Section = SQL_Section & strStep & " @Department = " & "'" & Me.Dept & "'"
strStep = ","
End If
If Not IsNull(Me.so) Then 'SO_Number
SQL_Department = SQL_Department & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_SO = SQL_SO & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_Item = SQL_Item & strStep & " @SO_Number = " & "'" & Me.so & "'"
SQL_Section = SQL_Section & strStep & " @SO_Number = " & "'" & Me.so & "'"
strStep = ","
End If
If Not IsNull(Me.Item) Then 'Item_Number
SQL_Department = SQL_Department & strStep & " @Item_Number = " & "'" & Me.Item & "'"
SQL_SO = SQL_SO & strStep & " @Item_Number = " & "'" & Me.Item & "'"
SQL_Item = SQL_Item & strStep & " @Item_Number = " & "'" & Me.Item & "'"
SQL_Section = SQL_Section & strStep & " @Item_Number = " & "'" & Me.Item & "'"
strStep = ","
End If
If Not IsNull(Me.Sectionno) Then 'Section_Number
SQL_Department = SQL_Department & strStep & " " & " @Section_Number = " & "'" & Me.Sectionno & "'"
SQL_SO = SQL_SO & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
SQL_Item = SQL_Item & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
SQL_Section = SQL_Section & strStep & " @Section_Number = " & "'" & Me.Sectionno & "'"
strStep = ","
End If
Me.Dept.RowSource = SQL_Department 'Obtain the Department rows that matches the criteria.
Me.so.RowSource = SQL_SO 'Obtain the SO_Number rows that matches the criteria.
Me.Item.RowSource = SQL_Item 'Obtain the Item_Number rows that matches the criteria.
Me.Sectionno.RowSource = SQL_Section 'Obtain the Section_Number rows that matches the criteria.
Call Activate_Stored_Procedure_To_Obtain_Rows_For_Subfo rm
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|