Renumber order field in a table 1

[ronphx]

I have an invoice form (frminvoicedetail) that contains a subform (frminvoiceDetailSubform) based on a table (tbltempdet). Each row in the table is a separate line on the invoice. One of the table's fields is called ORDR, which is the order the rows will be printed on the invoice. When the user enters an invoice line, the ORDR field is entered by the program and is incremented by one. Periodically the user needs to change the entry order of a previously entered row, and will click on the ORDR field in the subform and change the field to indicate where the row is to appear on the invoice (they might change from 05 to 02 to move the line higher). I would then like to go to the table and renumber all of the numbers in ORDR starting with 01, and putting the changed row in its new place. I do have the ORDERBY property in the subform set to ORDR.

I don't know how to access the tbltempdet in the subform to do this. I hope this question makes sense.

Thanks.

 

[CautionMP]

This might work if ORDR is not a key field.

Add two buttons to your subform;

cmdMoveUp
cmdMoveDown

Then add the following code to the subform;

Private Sub cmdMoveUp_Click()
Dim intIndexCurrent As Integer
intIndexCurrent = Me.CurrentRecord
If intIndexCurrent > 1 Then
Me.ORDR = intIndexCurrent - 1
Me.Recordset.MovePrevious
Me.ORDR = intIndexCurrent
Me.Requery
End If
End Sub

Private Sub cmdMoveDown_Click()
Dim intRecordCount As Integer
Dim intIndexCurrent As Integer
intIndexCurrent = Me.CurrentRecord
intRecordCount = Me.Count - 1
If intIndexCurrent <= intRecordCount Then
Me.ORDR = intIndexCurrent + 1
Me.Recordset.MoveNext
Me.ORDR = intIndexCurrent
Me.Requery
End If
End Sub

This should allow the user to move lines up and down on the invoice.

Hope this helps.

 

[CautionMP]

Footnote: I didn't like the way the focus changed on the recordset after the requry so I added a line to each sub that moves the focus with the record allowing a record to be moved from top to bottom (or vice versa) just by clicking the up/down button repeatedly.

Private Sub cmdMoveUp_Click()
Dim intIndexCurrent As Integer
intIndexCurrent = Me.CurrentRecord
If intIndexCurrent > 1 Then
Me.ORDR = intIndexCurrent - 1
Me.Recordset.MovePrevious
Me.ORDR = intIndexCurrent
Me.Requery
Me.Recordset.Move (intIndexCurrent - 2)
End If
End Sub

Private Sub cmdMoveDown_Click()
Dim intRecordCount As Integer
Dim intIndexCurrent As Integer
intIndexCurrent = Me.CurrentRecord
intRecordCount = Me.Count - 1
If intIndexCurrent <= intRecordCount Then
Me.ORDR = intIndexCurrent + 1
Me.Recordset.MoveNext
Me.ORDR = intIndexCurrent
Me.Requery
Me.Recordset.Move (intIndexCurrent)
End If
End Sub

There's always time to re-develop.

 

[ronphx]

Very interesting. I like this idea. Thank you so much for spending all that time to do this. I do have one problem. I am showing the subform in datasheet view, and I can't get the command buttons to display in the subform. Is there a way to do this?

Thanks again.

 

[CautionMP]

Quick solution: Change the sub-form from a datasheet to a continuous form that looks like a datasheet. This will allow you to add a footer to house the buttons, or better yet add buttons to the detail section so that they appear on each record (probably a better user interface as it will be more intuitive).

Not so quick solution: I briefly played around with references to the sub-form from the parent form and could not get to the CurrentRecord property of the sub-form. With that said you could write a custom method for the sub-form that could be called from the parent form that would take care of the mechanics of moving the record. I would think it would look something like (code for the sub-form);

Public Sub MoveRecord(Step as integer)
Dim intIndexCurrent As Integer
intIndexCurrent = Me.CurrentRecord
If intIndexCurrent > 1 Then
Me.ORDR = intIndexCurrent + Step
Me.Recordset.MovePrevious
Me.ORDR = intIndexCurrent
Me.Requery
If Step > 0 Then
Me.Recordset.Move (intIndexCurrent - 2)
Else
Me.Recordset.Move (intIndexCurrent)
End If
End Sub

Then this one method could be used to move the record up (Step=-1) or down (step=1) using the following routines in the Parent form;

Private Sub cmdMoveUp_Click()
ChildControlName.MoveRecord(-1)
End Sub

Private Sub cmdMoveDown_Click()
ChildControlName.MoveRecord(1)
End Sub


Hope this nudges you in the right direction.

 

[ronphx]

I took your first suggestion and changed the subform to a continuous form from a datasheet. This is a MUCH better way to go. Great suggestion. What I think I am going to do with the ORDR numbering is just have the user type in the number where they want the line to appear, and then I'll renumber the ORDR fields. Since the table is ordered by the ORDR field it automatically puts the record in the right place. It isn't as elegant a solution, but I think it works fine.

Thanks again for the suggestions.