How to load a record into a subform using stored procedure

[aitutaki1000]

Access 2003. SQL 2000 Server
How to load a record into a subform using stored procedure

If I select just the first combo boxes (ex: Dept, or So, or Item, or Sectionno) the procedure
Activate_Stored_Procedure_Tbtain_Rows_For_Subform worked fine.
But When I select a second combo boxes (Exept, So, Item, Sectionno) the procedure
Activate_Stored_Procedure_Tbtain_Rows_For_Subform crashed.

//////////////////

To be specific, I selected the combo box Dept and the procedure
Activate_Stored_Procedure_Tbtain_Rows_For_Subform worked fine.

Then I selected a second combo box, namely SO and the procedure
Activate_Stored_Procedure_Tbtain_Rows_For_Subform crashed.

A dialog box pop up and asked for SO_Number (Enter a Parameter Value). When I clicked the OK button without entering in any value an error message appeared

Run-time error '8145'
P1 is not a parameter for procedure
Obtain_Records_For_Subform_Selector

The YELLOW HIGLIGHT IS at the code
Me.Selector_Sub_Form.Form.RecordSource = SQL_Subform

////////////////////////////

Private Sub Form_Load()
Me.Dept = Null
Me.so = Null
Me.Sectionno = Null
Me.Item = Null

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_Tbtain_Rows_For_Combo_Boxes
End Sub

Private Sub SO_AfterUpdate()
Call Activate_Stored_Procedure_Tbtain_Rows_For_Combo_Boxes
End Sub

Private Sub Item_AfterUpdate()
Call Activate_Stored_Procedure_Tbtain_Rows_For_Combo_Boxes
End Sub

Private Sub Sectionno_AfterUpdate()
Call Activate_Stored_Procedure_Tbtain_Rows_For_Combo_Boxes
End Sub

/////////////////////////////

Private Sub Activate_Stored_Procedure_Tbtain_Rows_For_Combo_Boxes()
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_From_Date = SQL_From_Date & strStep & " @From_Date = " & "'" & Me.From_Date & "'"
'SQL_To_Date = SQL_To_Date & strStep & " @To_Date = " & "'" & Me.To_Date & "'"

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
Me.so.RowSource = SQL_SO
Me.Item.RowSource = SQL_Item
Me.Sectionno.RowSource = SQL_Section

Call Activate_Stored_Procedure_Tbtain_Rows_For_Subform
End Sub

/////////////////

Private Sub Activate_Stored_Procedure_Tbtain_Rows_For_Subform()
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

Me.Selector_Sub_Form.Form.RecordSource = SQL_Subform

End Sub

 

[Qik3Coder]

Try asking that question here:
forum702


I'll answer your question, not solve your problem