inserting a line in a quote/Order

[mleinss]

I am working on a database for order quoting. There are times I need to insert a new line between two other lines. The line numbers need to be resequences when I do as in the original quote has line 1,2,3 I need to add a line between lines 2 and 3 making the new line 2 and the old line 2 needs to become 3 and line 3 will become line 4.

the code I currently have doing does now work with access 2007 I am not sure why....I get a message the the record is read only. the code is attached to a button click
thanks for any help
Mary


Private Sub InsertBtn_Click()
Dim rst As Recordset
Dim lngSeq As Long

On Error GoTo Errortrap

DoCmd.RunCommand acCmdSaveRecord

Set rst = Me!QuoteDetFrm01.Form.RecordsetClone
rst.MoveLast
If rst.EOF Then
lngSeq = 1
Else
lngSeq = Forms!QuoteFrm01!QuoteDetFrm01![QuoteDetTbl.Seq]
Do Until rst![QuoteDetTbl.Seq] < lngSeq
rst.Edit
rst![QuoteDetTbl.Seq] = rst![QuoteDetTbl.Seq] + 1
rst.Update
rst.MovePrevious
If rst.BOF Then Exit Do
Loop
End If
rst.Close

DoCmd.OpenForm "QuoteDetFrm03", , , , acFormAdd
Forms!QuoteDetFrm03!QuoteId = Me!QuoteId

Forms!QuoteDetFrm03!Seq = lngSeq

Exit Sub

 

[penguino29]

Hi Mary,

Recordset is read-only so you cannot edit it. That's why you were getting the error.

http://msdn.microsoft.com/en-us/library/aa207086(v=office.10).aspx

Just create a fresh recordset based on whatever you want to edit.

 

[mleinss]

so all I have to do is change the code from recordsetclone to recordset? or do I actually have to go back to the query behind the form.. I have not used recordsets before

 

[gemma-the-husky]

make the numbers doubles, rather than longs for this purpose.

then if you have 1,2,3 you can insert a line by making the next one 2.5, or whatever. then you don't have to renumber existing items at all.

 

[penguino29]

Hi Mary,

Yes, basically you will open a fresh recordset - either directly based on the table you want to update (maybe better or simpler), or based on the query of the form.

Essentially the code hasn't changed much and will look a bit like this

Private Sub InsertBtn_Click()
Dim rst As Recordset
Dim lngSeq As Long
Dim mydb as Database

On Error GoTo Errortrap

DoCmd.RunCommand acCmdSaveRecord

Set mydb = Currentdb()
Set rst = mydb.openrecordset ("YourTablename_Or_Queryname_Or_SQLString")

rst.MoveLast
If rst.EOF Then
lngSeq = 1
Else
....

You have already met Recordset because I can see that you managed very well with RecordsetClone. It is simply defining a recordset using either a Table, Query or a SQL statement, whereas in Recordsetclone - as you have done - is referring to a form.

 

[mleinss]

I redid the record set based on the query behind the form, but got the same error. I found out that the query is linked to another table and that was not allowed. I then created a new query without the table link. now my problem is the syntax to use a field in the form to filter the query. I am not sure how to do this. Here is the code I have Set rst = dbs.OpenRecordset("qryFormQuoteDetFrm01_Insert") the form is named quotedetfrm03 which is a subform of quotefrm01. both places have a quoteId field which is what I need to filter the query

thanks
Mary

 

[penguino29]

Hi again Mary,

I have to apologise - I was complete wrong about RecordsetClone.

Using Access 2010 I made a subform with records, and I can change anything there using RecordsetClone.

You were on the right track all along... I am looking for a solution for you... will keep you posted.

Sorry once again - I am doing my best to help