Inserting a line??

[JamesSF]

A2k/A2k2

I have a detail table for charges on an invoice linked to a header table.

The users keep saying they want to be able to insert late charges like in
Excel so that they print in the correct order on a report/invoice.

How can i do this? i use an autonumber on the tables. users enter a date
of the charge and a billing code.

the only way i can think of is to have a column called Order than is
auotmatically sequenced - but the user changable. Would this work.

For example, each new bill gets a line 1, 2, 3 etc after line 3 - i need to
add a new line2 that needs to show under line 1 on the invoice. the user
would manually (or with some function) re-number 2 to 3 and 3 to 4.

Thanks
JamesSF

 

[Tim Ferguson]

For example, each new bill gets a line 1, 2, 3 etc after line 3 - i
need to add a new line2 that needs to show under line 1 on the
invoice. the user would manually (or with some function) re-number 2
to 3 and 3 to 4.

It should be manageable with a bit of code:

' get the number from the form or something
dwInsertNewNumber = 1

' a query to get the line numbers for the particular
' order.
strSQL = "SELECT ALL LineNumber FROM OrderLines " & _
"WHERE LineNumber >= " & dwInsertNewNumber & _
"ORDER BY LineNumber DESC;"

' check the parameters: I made up the locking option
Set rs = db.OpenRecordSet(strSQL, dbOpenDynaset, dbLockEverything)

' go down through the numbers
Do While Not rs.EOF
rs.Edit
' move it down; i.e. add one to the number
' note the sorting DESC command earlier means
' that this goes the right way. You could if you
' wanted sort ASC, then do a MoveLast and go up
' the recorset until BOF.
rs!LineNumber = rs!LineNumber +1
rs.Update

rs.MoveNext

Loop

' then add the missing number: this will fail
' if you have any required fields without
' default values, but you get the general idea
rs.AddNew
rs!LineNumber = dwInsertNewNumber
rs.Update

' close everything up, because you have to unlock
' the table
rs.Close

' and put the new info into the other fields
strSQL = "UPDATE OrderLines " & _
"SET ProductID = " & dwNewProductID & ", " & _
" Quantity = " & dwNewQuantity & ", " & _
" AgreedPrice = " & currNewAgreedPrice & _
"WHERE OrderNumber = " & dwCurrentOrderNumber & _
" AND LineNumber = " & dwInsertNewNumber & ";"

' this needs to be surrounded by suitable error handlers
db.Execute strSQL, dbFailOnError


This is untested air code, so you'll need to fix it for your particular
situation, but it should give you an idea of where to go.

All the best


Tim F