I am having trouble to get all my records displayed on a form in form view. It will only displayed the last record. I have set both scroll bars visibily in the property of the form.
I'm developing in Access 2000 and SQL Server 7.0.
This is my procedure I used:
The user selects a record on the subform, clicks "open history' btn on the main form. This event procedure calls the a stored procedure to find all records that matches the selected record. The .openform method opens the history form. The form is then populated with the recordset.
I did a debug on the number of recordset it suppose to return and the values of a field returns and they are correct. But the form will only display the last record. The record scroll bar @ the bottom of the form indicated 1 record.
Am I forgetting to set one of the properties in the form?
Or is it Something in the logic of my code?
The history form properties are:
recordset type: updatable snapshot
scroll bars: both
record selectors: yes
navigation buttons: yes
My code:
Private Sub w_o__history_Click()
On Error GoTo Err_w_o_history_Click
Dim ret_val As Integer
Dim check_wo_history As New ADODB.Command
Dim rs_check_wo_history As New Recordset
Dim work_ord_num As String, work_ord_line_num As String
Dim lngRecs As Long
Dim stDocName As String, stLinkCriteria As String
stDocName = "shipment_hist_list"
With check_wo_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spCheck_wo_history"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
''''.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, mfg_ord_num)
.Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length, Me!shipping_sched_list_subform.Form!work_ord_num.Value)
.Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Me!shipping_sched_list_subform.Form!work_ord_line_num.Value)
Set rs_check_wo_history = .Execute
End With
lngRecs = rs_check_wo_history.RecordCount
''Debug.Print lngRecs
If lngRecs <> 0 Then
DoCmd.OpenForm FormName:=stDocName
Do While Not rs_check_wo_history.EOF
Forms!shipment_hist_list!work_ord_num = rs_check_wo_history(1)
Forms!shipment_hist_list!cust_name = rs_check_wo_history(7)
Forms!shipment_hist_list!mfg_ord_num = rs_check_wo_history(3)
:
:
Forms!shipment_hist_list!reason_code = rs_check_wo_history(26)
Debug.Print rs_check_wo_history(22)
rs_check_wo_history.MoveNext
Loop
End If
Debug.Print lngRecs
Exit_w_o_history_Click:
Set check_wo_history = Nothing
Exit Sub
Err_w_o_history_Click:
MsgBox Err.Description
Resume Exit_w_o_history_Click
End Sub
I appreciate your help!!
I'm developing in Access 2000 and SQL Server 7.0.
This is my procedure I used:
The user selects a record on the subform, clicks "open history' btn on the main form. This event procedure calls the a stored procedure to find all records that matches the selected record. The .openform method opens the history form. The form is then populated with the recordset.
I did a debug on the number of recordset it suppose to return and the values of a field returns and they are correct. But the form will only display the last record. The record scroll bar @ the bottom of the form indicated 1 record.
Am I forgetting to set one of the properties in the form?
Or is it Something in the logic of my code?
The history form properties are:
recordset type: updatable snapshot
scroll bars: both
record selectors: yes
navigation buttons: yes
My code:
Private Sub w_o__history_Click()
On Error GoTo Err_w_o_history_Click
Dim ret_val As Integer
Dim check_wo_history As New ADODB.Command
Dim rs_check_wo_history As New Recordset
Dim work_ord_num As String, work_ord_line_num As String
Dim lngRecs As Long
Dim stDocName As String, stLinkCriteria As String
stDocName = "shipment_hist_list"
With check_wo_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spCheck_wo_history"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
''''.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, mfg_ord_num)
.Parameters.Append .CreateParameter("@work_ord_num", adChar, adParamInput, work_ord_num_length, Me!shipping_sched_list_subform.Form!work_ord_num.Value)
.Parameters.Append .CreateParameter("@work_ord_line_num", adChar, adParamInput, 3, Me!shipping_sched_list_subform.Form!work_ord_line_num.Value)
Set rs_check_wo_history = .Execute
End With
lngRecs = rs_check_wo_history.RecordCount
''Debug.Print lngRecs
If lngRecs <> 0 Then
DoCmd.OpenForm FormName:=stDocName
Do While Not rs_check_wo_history.EOF
Forms!shipment_hist_list!work_ord_num = rs_check_wo_history(1)
Forms!shipment_hist_list!cust_name = rs_check_wo_history(7)
Forms!shipment_hist_list!mfg_ord_num = rs_check_wo_history(3)
:
:
Forms!shipment_hist_list!reason_code = rs_check_wo_history(26)
Debug.Print rs_check_wo_history(22)
rs_check_wo_history.MoveNext
Loop
End If
Debug.Print lngRecs
Exit_w_o_history_Click:
Set check_wo_history = Nothing
Exit Sub
Err_w_o_history_Click:
MsgBox Err.Description
Resume Exit_w_o_history_Click
End Sub
I appreciate your help!!
