Resort subform recordset after adding a sequence number 1

[sterlecki]

I have a form/subform situation. Main form gets it's data from a combobox pick list. The subform uses a query based on the combobox selection and sorts the data by name.

The user will now select some or all of the records in the subform and assign tasks in the task field and add a sequence number in the DrillSequencebyPad field. After doing such I would like to be able to resort the data based on the sequence number from a command button.

What is the best/easiest way to code this?

The SQL from the Query is:
SELECT data_Wells_Drillout.WaterDatum, data_Wells_Drillout.TargetX, data_Wells_Drillout.TargetY, data_Wells_Drillout.SlotX, data_Wells_Drillout.SlotY, data_Wells_Drillout.PadAssignment_original, data_Wells_Drillout.PadAssignment, data_Wells_Drillout.PadID, data_Wells_Drillout.[Slot#], data_Wells_Drillout.Rcat, data_Wells_Drillout.[Target Density], data_Wells_Drillout.NewPad_X, data_Wells_Drillout.NewPad_Y, data_Wells_Drillout.QEP_Status, data_Wells_Drillout.DrillSeason, data_Wells_Drillout.DrillYear, data_Wells_Drillout.Rig, data_Wells_Drillout.Task, data_Wells_Drillout.DrillDays, data_Wells_Drillout.DrillSequenceByPad, data_Wells_Drillout.PodName, data_Wells_Drillout.DrillSequenceByPOD, data_Wells_Drillout.StartTask, data_Wells_Drillout.EndTask, data_Wells_Drillout.DateCreated, data_Wells_Drillout.DateModified
FROM data_Wells_Drillout
WHERE (((data_Wells_Drillout.PadID)=[Forms]![frm_DrillOutSchedule]![cboSelectPad]) AND ((data_Wells_Drillout.[Target Density]) Between [Forms]![frm_DrillOutSchedule]![txt_Targdenmin] And [Forms]![frm_DrillOutSchedule]![txt_Targdenmax]));

The SQL from the subform data is:
SELECT qry_data_Wells_Drillout.WaterDatum, qry_data_Wells_Drillout.TargetX, qry_data_Wells_Drillout.TargetY, qry_data_Wells_Drillout.SlotX, qry_data_Wells_Drillout.SlotY, qry_data_Wells_Drillout.PadAssignment_original, qry_data_Wells_Drillout.PadAssignment, qry_data_Wells_Drillout.PadID, qry_data_Wells_Drillout.[Slot#], qry_data_Wells_Drillout.Rcat, qry_data_Wells_Drillout.[Target Density], qry_data_Wells_Drillout.NewPad_X, qry_data_Wells_Drillout.NewPad_Y, qry_data_Wells_Drillout.QEP_Status, qry_data_Wells_Drillout.DrillSeason, qry_data_Wells_Drillout.DrillYear, qry_data_Wells_Drillout.Rig, qry_data_Wells_Drillout.Task, qry_data_Wells_Drillout.DrillDays, qry_data_Wells_Drillout.DrillSequenceByPad, qry_data_Wells_Drillout.PodName, qry_data_Wells_Drillout.DrillSequenceByPOD, qry_data_Wells_Drillout.StartTask, qry_data_Wells_Drillout.EndTask, qry_data_Wells_Drillout.DateCreated, qry_data_Wells_Drillout.DateModified FROM qry_data_Wells_Drillout;

 

[TheAceMan1]

How are ya sterlecki . . .

Add an [blue]ORDER BY[/blue] clause to the SQL of the subform:

[blue] ORDER BY [DrillSequencebyPad][/blue]

Then just requery the subform (Me.Requery) anytime you wish to resort . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[sterlecki]

Thanks AceMan. I guess I'm not sure as to how to replace the existing SQL in the subform when it's opened with the modified (sorted) SQL when the command button is clicked.

The subform SQL is what's present in the RecordSource Property of the Form. I want to keep that as the default view when the form opens but be able to sort when the command button is clicked.

thanks

 

[jordanking]

I would use the recordset value of the form,

you can set it to a default with the open form event of the main form

just establish a recordset and use the following to set the recordset of the subform.

Set Forms!mainFormName.Form!subFormName.Form.Recordset = rs

then you can redefine your recordset / sql with the click event of the command button, and reset the subforms recordset with the above code.


.....
I'd rather be surfing

 

[jordanking]

sorry, rs is the recordset variable


.....
I'd rather be surfing

 

[sterlecki]

JordanKing

I solved the problem as you suggested. thanks

Forms![frm_DrillOutSchedule]![qry_data_Wells_Drillout subform1test].Form.OrderBy = "DrillSequenceByPad"

Forms![frm_DrillOutSchedule]![qry_data_Wells_Drillout subform1test].Form.OrderByOn = True

 

[jordanking]

glad to help


.....
I'd rather be surfing