Inserting a new row on a continous form

[Massamiya]

Hello everyone,
I have searched the internet without any success and
would like to know whether it is possible to insert a new
row anywhere beside the last row in a continous form ?
If it is possible, would really appreciate if anyone would
help me with code to achieve it.
Thanks very much in advance.

 

[DCrake]

As the term suggests the layout is continuous one after the other. The only way to get a record to be anywhere other than the last one is to insert the record in the underlying recordsource in such a way that the sort order would cause it to be part way through the list.

As a matter of interest why would you want to do something like this?

 

[missinglinq]

Bottom line answer here is "No," you cannot select a spot in the middle of existing records and insert a new record, as you can in a spreadsheet program such as Excel.

If there is a field that you want to sort by, so that the newly created record will appear in the middle of existing records, you can Requery the form after entering the new record, and the sort order will then be re-established, as David suggested.

And I have to reiterate David's question, "Why would you want to do something like this?"

Linq ;0)>

 

[Massamiya]

Thanks Linq & David for the prompt response.
I am writing an estimation program , some parts have
higher priority and the user may want the part to appear at
the top or middle of a list that is initially displayed.
This is what I am thinking and don't know whether it is the
best possible means.
Would get the user to click anywhere on the continous form, and then
get the relative position, add a new record and then update the new record's
Display or ranking ID (Disp_ID) an Integer with the ID of the relative position.
I then use SQL to open a recordset sorted by the Disp_ID, Goto to the
top of the recordset and change the Disp_ID starting with a value of 1 and loop.
the entire recordset.
The table I am dealing with vary but would be max 60 rows by 12 columns.
Any input would be highly appreciated.

 

[DCrake]

You don't want a subform you need a listbox that has the factility to promote/demote items in the list so as to manually change the order in the list using the listindex property.

 

[missinglinq]

This site has code to do exactly what David has suggested:

http://www.bigresource.com/VB-listbox-move-up-down--SAcygbmOdm.html

 

[Massamiya]

Thanks again for the prompt suggestions.
Sorry I have not been able to respond as well
due to work assignment away from my base.
I have yet to look into the suggestions raised and
intend to do so when I get back home tonight.
More grease to your elbows.

 

[gemma-the-husky]

a way of doing this is maybe to base the recordset on a pre-designed table, with as many "slots" as you are likely to need.

then you can write code to shuffle items within the record set.

I do this in a quotation system, where the quote form itself has up to 50 visible items, but users rarely quote as many as this, so they have the facility to move blocks of items up or down - to position the quoted items in the order they want.

 

[Massamiya]

Thanks for the input.
I succeeded with the delete and insert of a row on a continous form.
However, I am facing a major problem with the deletion.
I am able to delete a row or rows initially but when I navigate through the
subform and attempt to delete another row or rows I am unable to delete.
Have checked but cannot figure out what the problem is or the solution.
The subform and mainform are unlinked and the delete and insert command
buttons are on the foot of the mainform.
The recordsource of the subform is a basic table based (TBL_Estimate) query
without any filter.
Anybody has an idea what the problem is ? Here is the code for the deletion.

Private Sub DeleteRow_Click()

If Me!FanMitumori.Form.Dirty Then DoCmd.RunCommand acCmdSaveRecord
If MsgBox("The checked Rows will be deleted ?", vbOKCancel, "DELETE") = vbOK Then

With Me!FanMitumori.Form.RecordsetClone

If .BOF And .EOF Then Exit Sub

' txtChkList Not visible Text box to store the disp_id

Do Until .EOF

If InStr(Me!FanMitumori.Form.[txtChkList] & ",", "," & !DISP_ID & ",") > 0 Then
.Delete
ElseIf !RowID <> .AbsolutePosition + 1 Then
.Edit
!ROWID = .AbsolutePosition + 1
!DISP_ID = .AbsolutePosition
.Update
End If
If Not .EOF Then .MoveNext
Loop
End With

Me!FanMitumori.Form.txtChkList = Null
Me!FanMitumori.Requery
Me.FanMitumori.SetFocus
End If
End Sub

 

[bdemps03]

How did you end up inserting a row on a continuous form?