Updating a field in a subform dynamically (1 Viewer)

ryetee

Registered User.
Local time
Today, 22:04
Joined
Jul 30, 2013
Messages
952
I had posted this in forms but maybe I need todo something in VBA..

I'm working on a small system that I need to change.

The user can make an order by clicking on a button after selecting a client. He is taken to a screen/form with details about the client. In a subform there is a blank order. Within the subform he then selects an order template and clicks a copy button and a query in the click event appends the order template data to a suborder table. The user can then go ahead and change fields and these changes are reflected in the suborder table. There is one field that he can not update that contains effectively the sort order of the copied template lets call this strSortOrder. The user can delete and add extra items.He can change the visual order as well. Once complete I want to be able to preserve this visual order for further forms, reports and printouts. After googling this to death the only sensible way forward is to use the row number itself. This has been quite challenging in itself but I have managed to find some code that works well. The only problem I have now is updating strSortOrder. I have managed to display it in an unbound field. So I have


Item-A-ROW1 price-A-ROW-1 quantity-A-row-1 strSortorder-A-ROW1 unboundfieldROW1
Item-B-ROW2 price-B-ROW-2 quantity-B-row-3 strSortorder-B-ROW2 unboundfieldROW2
Item-C-ROW2 price-C-ROW-3 quantity-C-row-3 strSortorder-C-ROW3 unboundfieldROW4
Item-D-ROW4 price-D-ROW-4 quantity-D-row-4 strSortorder-D-ROW4 unboundfieldROW4
Item-E-ROW5 price-E-ROW-5 quantity-E-row-5 strSortorder-E-ROW5 unboundfieldROW5

The field unboundfieldROW1 contains 1 unboundfieldROW2 contains 2 up to unboundfieldROW5 containing5.

I want strSortorder-E-ROWn = unboundfieldROWn

I can get this (almost) to work using a before update event BUT ONlY IF the user changes something on a particular row. I need it to work on any row that is unchanged as well. In fact the user does't need to change anything anyway.

Any help appreciated.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:04
Joined
Jan 23, 2006
Messages
15,364
Perhaps a copy of the db with enough info and data to highlight the issue would help.
 

Orthodox Dave

Home Developer
Local time
Today, 22:04
Joined
Apr 13, 2017
Messages
218
In order to "freeze" the sort order set up by the last user, you'll need a table field - not an unbound field - that saves the record number, so it can be used to sort the records when next opened.

Used in a form's RecordsetClone, ".absoluteposition" outputs the record number position in the form as currently sorted. It starts at 0 for the first record, so for neatness, I've shown it as +1 below. This code populates a chosen field with the record number as currently sorted. [YourField] should be the fieldname from the form itself. The code below was developed from the work of others who should be acknowledged.

Code:
Sub AddRecordRowNumbers()

'Code taken from that Created by Stephen Lebans in 1999 with help from Chris Bergmans, Updated by Allen Browne Oct/2002 and modified 2017 for this application.

   Dim db As Database
   Dim rst As Recordset

   Set db = CurrentDb()
   Set rst = Forms![YourForm]![YourSubform].Form.RecordsetClone

   With rst
      .MoveLast
      .MoveFirst

      Do While Not .EOF
           .edit
            rst![YourField] = (.AbsolutePosition + 1)
          .update
          .MoveNext
      Loop

      .Close
   End With

Set db = Nothing
Set rst = Nothing

End Sub

As for saving the sort order, this procedure should update the row number for all the records at one go, so you wouldn't need to update a row at a time as you seem to be suggesting.

I hope that helps.
 
Last edited:

ryetee

Registered User.
Local time
Today, 22:04
Joined
Jul 30, 2013
Messages
952
In order to "freeze" the sort order set up by the last user, you'll need a table field - not an unbound field - that saves the record number, so it can be used to sort the records when next opened.

Used in a form's RecordsetClone, ".absoluteposition" outputs the record number position in the form as currently sorted. It starts at 0 for the first record, so for neatness, I've shown it as +1 below. This code populates a chosen field with the record number as currently sorted. [YourField] should be the fieldname from the form itself. The code below was developed from the work of others who should be acknowledged.

Code:
Sub AddRecordRowNumbers()

'Code taken from that Created by Stephen Lebans in 1999 with help from Chris Bergmans, Updated by Allen Browne Oct/2002 and modified 2017 for this application.

   Dim db As Database
   Dim rst As Recordset

   Set db = CurrentDb()
   Set rst = Forms![YourForm]![YourSubform].Form.RecordsetClone

   With rst
      .MoveLast
      .MoveFirst

      Do While Not .EOF
           .edit
            rst![YourField] = (.AbsolutePosition + 1)
          .update
          .MoveNext
      Loop

      .Close
   End With

Set db = Nothing
Set rst = Nothing

End Sub

As for saving the sort order, this procedure should update the row number for all the records at one go, so you wouldn't need to update a row at a time as you seem to be suggesting.

I hope that helps.

Thanks I'll give that a go. The unbound field btw just hods the row number. I was trying to figure out how to get it into he table.
i.e strSortorder-E-ROWn (table) = unboundfieldROWn (unbound field)
 

ryetee

Registered User.
Local time
Today, 22:04
Joined
Jul 30, 2013
Messages
952
In order to "freeze" the sort order set up by the last user, you'll need a table field - not an unbound field - that saves the record number, so it can be used to sort the records when next opened.

Used in a form's RecordsetClone, ".absoluteposition" outputs the record number position in the form as currently sorted. It starts at 0 for the first record, so for neatness, I've shown it as +1 below. This code populates a chosen field with the record number as currently sorted. [YourField] should be the fieldname from the form itself. The code below was developed from the work of others who should be acknowledged.

Code:
Sub AddRecordRowNumbers()

'Code taken from that Created by Stephen Lebans in 1999 with help from Chris Bergmans, Updated by Allen Browne Oct/2002 and modified 2017 for this application.

   Dim db As Database
   Dim rst As Recordset

   Set db = CurrentDb()
   Set rst = Forms![YourForm]![YourSubform].Form.RecordsetClone

   With rst
      .MoveLast
      .MoveFirst

      Do While Not .EOF
           .edit
            rst![YourField] = (.AbsolutePosition + 1)
          .update
          .MoveNext
      Loop

      .Close
   End With

Set db = Nothing
Set rst = Nothing

End Sub

As for saving the sort order, this procedure should update the row number for all the records at one go, so you wouldn't need to update a row at a time as you seem to be suggesting.

I hope that helps.

Btw, sorry to being ignorant but where does this code go? Is it as part of an event when the main form is closed?
 

Orthodox Dave

Home Developer
Local time
Today, 22:04
Joined
Apr 13, 2017
Messages
218
The first thing is to paste the procedure into the main Form's VBA. Then you can "call" it with the line: "Call AddRecordRowNumbers" (without quotes) from whatever control/event you choose. Not sure how familiar you are with VBA, but you'll need to enter the actual names where it says [YourForm], [YourSubform] and [YourField]. I'm hoping the code will work without bugs, but can't guarantee it. It's always easy to overlook something in coding.

Exactly what control(s) or event(s) (there can be many) you choose to call it from depends on the structure of your form / subform. It wouldn't work with the Close event because by then the subform would be gone out of reach of the procedure. You could put a button on the main form or subform marked "Freeze sort order" or something. Or if you have a "Close" button (rather than the user closing via the X button) you could call the procedure from that button before closing the form. You also said the user can change the visual order but don't say how. If this is via a button you could call the procedure from the Click event of that button.

In short you need to do a little experimentation to see what works best for you.
 
Last edited:

ryetee

Registered User.
Local time
Today, 22:04
Joined
Jul 30, 2013
Messages
952
The first thing is to paste the procedure into the main Form's VBA. Then you can "call" it with the line: "Call AddRecordRowNumbers" (without quotes) from whatever control/event you choose. Not sure how familiar you are with VBA, but you'll need to enter the actual names where it says [YourForm], [YourSubform] and [YourField]. I'm hoping the code will work without bugs, but can't guarantee it. It's always easy to overlook something in coding.

Exactly what control(s) or event(s) (there can be many) you choose to call it from depends on the structure of your form / subform. It wouldn't work with the Close event because by then the subform would be gone out of reach of the procedure. You could put a button on the main form or subform marked "Freeze sort order" or something. Or if you have a "Close" button (rather than the user closing via the X button) you could call the procedure from that button before closing the form. You also said the user can change the visual order but don't say how. If this is via a button you could call the procedure from the Click event of that button.

In short you need to do a little experimentation to see what works best for you.
Thanks again - had more or less done what you have suggested anyway. I had tried adding a "freeze" button on the form but I had to move it to the sub form as I was getting a weird db error. I then had to build in some code to make sure the freeze button had been clicked before exiting!
The visual order on the form is really a bit of a red herring. When calling AddRecordRowNumbers it uses the "visual" position on the form anyway, so all is good. I was having problems when adding a new row but that was before I had "perfected" my positioning of the call to AddRecordRowNumbers!

No bugs at all in AddRecordRowNumbers. I was getting my weird db error in there but that was down to calling it from the wrong place.

So it's passed unit test now needs a user test!
Again thanks for the help and the nudge in the right direction.
 

Users who are viewing this thread

Top Bottom