August 1, 2003 at 1:23 pm
When the user selects a record in the subform and then clicks on the button on the main form, it opens up to another form(filtered form) that contains the history of the selected record).
To obtain a filtered recordset, I created and executed a recordset through a stored procedure in the click event procedure. I am not sure how I can populate the form with the recordset from the stored procedure. I did a debug.print on the recordset and right results displayed.
**I want the results from the stored procedure as the recordsource for the form I want to open to.***
I'm using Access 2000 and SQL Server 7.0
This is what I have:
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, strWhere As String
stDocName = "shipment_hist_list"
strWhere = "tblShipment_history!work_ord_num = Me!shipping_sched_list_subform.Form!work_ord_num"
Call load_const
' ensure that the Work order number is on the tblShipment_history
With check_wo_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spCheck_wo_history"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.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(recordsaffected:=lngRecs)
End With
Debug.Print lngRecs
If lngRecs <> 0 Then
Do While Not rs_check_wo_history.EOF
DoCmd.OpenForm FormName:=stDocName
Debug.Print rs_check_wo_history(0)
rs_check_wo_history.MoveNext
Loop
End If
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
Your help is greatly appreciated!
August 2, 2003 at 9:36 am
You may have better luck posting to a Access specific site. I use Access, but rarely the forms part of it. It should be a matter of binding the recordset to the the control/form.
Andy
August 4, 2003 at 12:22 am
Hi alicejwz,
quote:
Do While Not rs_check_wo_history.EOFDoCmd.OpenForm FormName:=stDocName
Debug.Print rs_check_wo_history(0)
rs_check_wo_history.MoveNext
Loop
End If
why are you doing this within a Do..While construction?
You can only select one record at a time on your subform. The new form should only show the records based on the current value of your subform and this subform can be opened only once. (Well, of course it can stay opened and be reloaded, but that's another question)
So, you have created the sproc which takes some input parameters based on the selection made on the subform. Seems to be everything fine.
I wouldn't use the Click() event of the subform, but rather the Current() event. This ensures you always have the actual search criteria, which the Click() event does not necessarily do.
Within the Click() event of the button you can easily open the form.
Forms!<your_form>.visible = False
Forms!<your_form>.RecordSource = <your_recordset>
...maybe some other initialisation
Forms!<your_form>.visible = True
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply