Insert order line numbers automatically by code?

[niuginikiwi]

Hi I have two tables tblOrders and tblOrderDetails. Two forms are based on
these tables called frmOrders and frmOrderDetailsSubform respectively.
The 2nd form is a subform as datasheet view on frmOrders.
I have a text field called LineNo in the subform with a combo box with
RowSourceType = Value List and RowSource = 01;02;03;04 up to 40.

What happens is one an order line is entered a LineNo is selected and that
is selected in the order of 01 to 20 or 30 whatever the last orderline is.
But on some ocassion one OrderLine needs to be deleted. For Example, there
were 10 order lines and line 07 was to be deleted. If deleted it leaves a gap
(01 - 06, no 07 then 08 -10)
Because I would like the LineNo from 01 up to whatever without any gaps like
that, I have a button on the main form called btnRefresh.

Once I click this button I would like the LineNo field to count how many
Order Line records are there and reassign 01 up to whatever the last number
the Order Line record is.

Can any or does any one have any idea on how to code my btnRefresh onclick
event to do just that?

In otherwords, the users can carry on making order lines and not worry about
LineNo field until in the end when they can press btnRefresh which should
fill out the LineNo in sequence for them.

Any help would be much appreciated.

Thanks.

 

[Dirk Goldgar]

Hi I have two tables tblOrders and tblOrderDetails. Two forms are based on
these tables called frmOrders and frmOrderDetailsSubform respectively.
The 2nd form is a subform as datasheet view on frmOrders.
I have a text field called LineNo in the subform with a combo box with
RowSourceType = Value List and RowSource = 01;02;03;04 up to 40.

What happens is one an order line is entered a LineNo is selected and that
is selected in the order of 01 to 20 or 30 whatever the last orderline is.
But on some ocassion one OrderLine needs to be deleted. For Example, there
were 10 order lines and line 07 was to be deleted. If deleted it leaves a
gap
(01 - 06, no 07 then 08 -10)
Because I would like the LineNo from 01 up to whatever without any gaps
like
that, I have a button on the main form called btnRefresh.

Once I click this button I would like the LineNo field to count how many
Order Line records are there and reassign 01 up to whatever the last
number
the Order Line record is.

Can any or does any one have any idea on how to code my btnRefresh onclick
event to do just that?

In otherwords, the users can carry on making order lines and not worry
about
LineNo field until in the end when they can press btnRefresh which should
fill out the LineNo in sequence for them.

Any help would be much appreciated.

Thanks.

Why do you need these order line numbers in the first place? Is there any
special reason you need to refer to a line item by number? Given that you
want to renumber the detail lines when you delete one, it isn't at all clear
that the line number has any essential association to a particular line
number, so why have it?

If you need to show line numbers but want them to be automatically numbered
and renumbered as detail items are added and deleted, you can have them
calculated on the fly, as a caculated field in the subform's recordsource
query. Then you would only have to refresh the subform (using Me.Refresh)
in the form's AfterDelConfirm event.

If you really do need to store these line numbers for some reason, it's
certainly possible to recalculate them when you want, but I'd want to be
sure this is a real need, as so often this perceived need is spurious.

 

[niuginikiwi]

Hi Dirk,

Thank you for asking.
Yes the LineNo field has a reason to be there. These are sales orders and we
supply the markets who pay their accounts by Invoice number and its LineNo so
it needs to be in the DB to later track our accounts and update the status of
payment on each of the LineNo on an Invoice.
Unless, you think I can get by with your suggestion, I at the moment would
like to have reocrds of LineNo.

Hope to hear back from you.

 

[Stuart McCall]

Hi I have two tables tblOrders and tblOrderDetails. Two forms are based on
these tables called frmOrders and frmOrderDetailsSubform respectively.
The 2nd form is a subform as datasheet view on frmOrders.
I have a text field called LineNo in the subform with a combo box with
RowSourceType = Value List and RowSource = 01;02;03;04 up to 40.

What happens is one an order line is entered a LineNo is selected and that
is selected in the order of 01 to 20 or 30 whatever the last orderline is.
But on some ocassion one OrderLine needs to be deleted. For Example, there
were 10 order lines and line 07 was to be deleted. If deleted it leaves a
gap
(01 - 06, no 07 then 08 -10)
Because I would like the LineNo from 01 up to whatever without any gaps
like
that, I have a button on the main form called btnRefresh.

Once I click this button I would like the LineNo field to count how many
Order Line records are there and reassign 01 up to whatever the last
number
the Order Line record is.

Can any or does any one have any idea on how to code my btnRefresh onclick
event to do just that?

In otherwords, the users can carry on making order lines and not worry
about
LineNo field until in the end when they can press btnRefresh which should
fill out the LineNo in sequence for them.

Any help would be much appreciated.

Thanks.

Whatever your reason for wanting line numbers, you could give this a try:

http://www.smccall.demon.co.uk/Downloads.htm#LineRenum

 

[niuginikiwi]

Hi Erez,

I used the code you supplied with one minor change which is the reference to
the LineNo field since the btnRefresh is on the main form.

Which I click, I got this error:

Runtime Error "3020"
Update or CancelUpdate withour AddNew or Edit

Help on that error doesn't mean much to me.

Also one point i got it wrong in my original post is that the subform
Default View is set to Conintuous. Not as datasheet view as I have mentioned.

Have you any more suggestions?

Ta

 

[niuginikiwi]

Hi Stuart,

I have copied over your code to my project and have a field called LineNo in
the tblOrderDetails of numeric type Long Interger. (I used to have that field
LineNo as text before but changed it to suit your example)

Now when trying to enter a new record in my subform and I get a "runtime
error 13 saying : Type Mismatch"
and highlights this code

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNo = NextLineNumber(Me.RecordsetClone)
End Sub

Any suggestions?

 

[Stuart McCall]

Hi Stuart,

I have copied over your code to my project and have a field called LineNo
in
the tblOrderDetails of numeric type Long Interger. (I used to have that
field
LineNo as text before but changed it to suit your example)

Now when trying to enter a new record in my subform and I get a "runtime
error 13 saying : Type Mismatch"
and highlights this code

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.LineNo = NextLineNumber(Me.RecordsetClone)
End Sub

Any suggestions?

<snip>


Thefield should be named LineNumber, not LineNo. Unless you want to alter my
code to use LineNo instead, that is.

 

[niuginikiwi]

Hi Stuart,

I have changed everything to resemble the way you have. LineNo has been
changed to LineNumber.
I imported your modLineNumber from your app and when I compile, I get a
compile error saying "Method or Data member not found"
and then highlights the line " .edit " from the code below:


Public Sub LineRenumber(rs As Recordset, Status As Integer)
'Called from Form_AfterDelConfirm event of each subform
'Renumbers item lines following deletions
Dim i As Long

If Status <> acDeleteOK Then Exit Sub

With rs
.MoveLast
For i = .RecordCount To 1 Step -1
.Edit
!LineNumber = i
.Update
.MovePrevious
Next
End With
End Sub

But when I ignore that and try using the form, thats where I get the type
mismatch error that I have previously mentioned.

Any more suggestions?
PS: im using Access 2007

 

[Stuart McCall]

Hi Stuart,

I have changed everything to resemble the way you have. LineNo has been
changed to LineNumber.
I imported your modLineNumber from your app and when I compile, I get a
compile error saying "Method or Data member not found"
and then highlights the line " .edit " from the code below:


Public Sub LineRenumber(rs As Recordset, Status As Integer)
'Called from Form_AfterDelConfirm event of each subform
'Renumbers item lines following deletions
Dim i As Long

If Status <> acDeleteOK Then Exit Sub

With rs
.MoveLast
For i = .RecordCount To 1 Step -1
.Edit
!LineNumber = i
.Update
.MovePrevious
Next
End With
End Sub

But when I ignore that and try using the form, thats where I get the type
mismatch error that I have previously mentioned.

Any more suggestions?
PS: im using Access 2007

<snip>

Ah. Sounds like you don't have a reference set to the DAO library. In the
VBE, go to Tools->References and check the entry 'Microsoft DAO 3.x Object
Library' where x is the highest number on your system (3.6 is recommended).
Hopefully that will cure both problems.

I haven't tested this on A2007 (don't yet own a copy), but I don't think
that's relevant. There's nothing tricky about the code, just standard DAO
stuff.

 

[Dirk Goldgar]

Ah. Sounds like you don't have a reference set to the DAO library. In the
VBE, go to Tools->References and check the entry 'Microsoft DAO 3.x Object
Library' where x is the highest number on your system (3.6 is
recommended). Hopefully that will cure both problems.

In Access 2007, it's the "Microsoft Office 12.0 Access database engine
Object Library". That's the A07 equivalent of DAO.

 

[Stuart McCall]

In Access 2007, it's the "Microsoft Office 12.0 Access database engine
Object Library". That's the A07 equivalent of DAO.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks Dirk. I wasn't aware of that. (duly noted)

To niuginikiwi: please ignore my comments re DAO and make sure you have a
reference set to the library Dirk mentions.

 

[niuginikiwi]

Hi guys,

That worked fine for me after setting the Ref right.

Thanks a bunch.

 

[Stuart McCall]

Hi guys,

That worked fine for me after setting the Ref right.

Thanks a bunch.

<snip>

Glad that got you going. I'll update my site to inform visitors of the A2007
reference requirement.

 

[Dirk Goldgar]

Thanks Dirk. I wasn't aware of that. (duly noted)

To niuginikiwi: please ignore my comments re DAO and make sure you have a
reference set to the library Dirk mentions.

You can still call it DAO, for most purposes. There are only a couple of
new object types and properties. Even the reference qualifier is still
"DAO", as in:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Those work correctly when the ADE object library is referenced.

 

[Stuart McCall]

You can still call it DAO, for most purposes. There are only a couple of
new object types and properties. Even the reference qualifier is still
"DAO", as in:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Those work correctly when the ADE object library is referenced.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Also duly noted. Thanks v much Dirk.