aTricky one

[bunji]

I am not sure how to word this so bear with me.

I have a form with a subform in datasheet view. These are linked by an ID number from the entry in the mainform to the subform. Each entry of the subform has its on ID (autogenerated).

Problem: A user enters a load of records in the datasheet view but realises that they have missed out a line (record) and want to go back and enter a new one. How can this be done.

So to create a new record at a particular place so that it is in sequence?

 

[KenHigg]

Give it up - The autonumber was never intended to be used in that way. Sorry -

 

[GailFialho]

Create a new field in the subtable called "SortOrder"
Have subform and parent link as necessary through the indexes but DISPLAY the records in order of the "SortOrder" field.
User has created records #1,2,3,4,5 and now needs to add a new #3 and move all the others up one. Let user add new record and change the SortOrder field as needed.

 

[KenHigg]

The sample db I posted here:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

In post #18 has this feature if anyone wants to reverse engineer it...

 

[bunji]

Ok many thanks for your comments. I have put this to the users, they didnt seem that impressed with having to change all the subsequent numbers as there maybe a lot.

Maybe I could create some sort of function that would allow the user to automatically update the rest.

So a user already has 1-6 records filled. I could do the 'SortOrder' field to afterupdate if number already exists then renumber all the rest from that position.
eg. Users add another 'SortOrder' 3 record, so the check would realise it already exists and number the original 3 to 4, 4 to 5, 5 to 6 and 6 to 7.

Do you think this is possible? If so how? as its way beyond me!!

Thanks for your help so far

 

[GailFialho]

Capture new SortOrder and recordID in variables.
Query the records for the RecordID and SortOrder field >= variable as a recordset.
Loop through the records.
If RecordID <> recIDVariable then SortOrder = SortOrder+1

 

[bunji]

I think i get this but not sure how the RecordID comes into it. Do you mean the already existing SortORder and the position of the new Record?

My coding is pretty basic, could you help by explaining the coding. How do I loop the record?

 

[GailFialho]

I hope I have your setup correct, just guessing fro your info, but here goes:

ParentTable: ParentTableID(autonumber and primaryKey), other fields

ChildTable: ChildTableID(autonumber and primaryKey for childTable), ParentTableID(longInteger, foreignKey, no autonumber), SortOrder (longInteger), other fields in ChildTable

Relationship of Parent to child is One to many on the ParentTableID

Subform Parent and Child link on ParentTableID

Subform field for ParentTableID.Properties.DefaultValue =Parent!ParentTableID (This will automatically fill in the correct ID record to associate the subform record to)
When user adds a new record the SortOrder field should be blank.
On AfterUpdate of the SortOrder field the code opens a recordsetClone of the subform

Dim rs as recordset
Dim lngChildID as Long
Dim lngSortNumber as Long

lngChildID = Me!ChildTableID
lngSortNumber = Me!SortOrder

Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
If rs!SortOrder >= lngSortNumber And rs!ChildTableID <> lngChildID Then
With rs
.Edit
.SortOrder = .SortOrder + 1
.Update
End With
End If
Loop
rs.Close
Set rs = Nothing
Me.Refresh