Block Sequence Nos

  • Thread starter Thread starter ChoonBoy
  • Start date Start date
C

ChoonBoy

I am using Access 2003.

I have a datasheet view of a form with 2 columns.

ItemID / SeqNos.

ItemID will only contain 2 types of description INVxxxxx and PRDxxxx

EG.

ItemID / SeqNos
INVxxx1 / This is an Invoice
PRDxxx1 / 1
PRDxxx2 / 2
PRDxxx3 / 3
PRDxxx4 / 4
INVxxx2 / This is an Invoice
PRDxxx5 / 1
PRDxxx6 / 2
PRDxxx7 / 3
etc. etc...

I want SeqNos to restart at 1 immediately after an INVxxxx. I hope this can
be done.

Appreciate any help in this.

Thanks in advance.
 
I think you can use a static variable in a function to come up with this
numbering system. Assume controls called ctlItemID and ctlSeqNo....

(aircode.. untested)
Private Sub Form_Current()
If Not Me.NewRecord Then Me.ctlSeqNo = pfGetNum()
End Sub

Private Function pfGetNum()
Static i As Integer
If Left(Me.ctlItemID, 3) = "INV" Then i = 0
i = i + 1
pfGetNum = i
End Function



A Static variable will hold it's value from one calling to the next (unless
the app is reset or shut down). Therefore the above *should* reset the var
to 0 if "INV", or increment by 1 otherwise.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Actually it is not related to anything.

INVxxx1 is actually an Invoice number.
PRDxxx1 is actually product tracking code.

The objective is to track Product distribution. Eg if a PRDxxx? is found in
the Northern territory when it should not be, it can easily be linked back to
the Invoice and the customer responsible for cross distribution.

The seq nos is required for exact count of the number of PRDxxxxs trailing
an invoice.

Thanks
 
With no relation you have a real problem. Access stores records like a
barrel of bricks. You got to sort to guarantee that they come out in the
order you want.
What is to determine that RDxxx2 and RDxxx5 are not in the same block
sequence?
 
I tried the code you provided and it works, thanks.

There is one problem, if I click on ctlItemID the number will change to
something random. Is there a way to prevent it from changing?

Regards

AY
 
Thanks for you quick response.

There is actually a 3rd column in the datasheet. It is an Autonumber
(Accending) that will determine that it stays in the same block.

Regards
 
ChoonBoy said:
Actually it is not related to anything.

INVxxx1 is actually an Invoice number.
PRDxxx1 is actually product tracking code.

The objective is to track Product distribution. Eg if a PRDxxx? is
found in the Northern territory when it should not be, it can easily
be linked back to the Invoice and the customer responsible for cross
distribution.
Click to expand...
"linked back to the Invoice " does not seem possible ith the information
given.
IN any event there should be at least two tables for this.
One carries the Invoice numbers and the other carries the product ID
They are related through the Invoice number key in one field and the invoice
number as a foreign key in the product order table.
It would be worth writing code to import these records into the proper
tables if you are getting information from another location.
 
I agree with the two tables or at least a separate field for the invoice.
Then the regular old 'Ranking in a group' query would number the items.
Example --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Products] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Products AS Q
ORDER BY Q.Group, Q.Points;
Group -- invoice
Points - items
Item_no - other fields to include in output.
 
The Current event is causing the number to be recalculated when you click on
any of the records (change of record).

You could put this in a public function and set the control source of the
control to =TheFunctionName() to avoid that problem.

On a deeper note though, Karl and Mike's suggestions on the data structure
amendment would be a much better route to go. Well normalized data generally
gets rid of the need for clunky workarounds such as this... it is a far more
recommended method.
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Thanks for the reply.

The problem I am facing is not invoicing to customers. It is a distribution
issue.

We are a bit old fashion in this part of the world. The scenario goes like
this.

1) Printed invoices passed to warehouse
2) Invoice number is Keyed into ctlItemID eg. INVxxxx1
3) It is followed immediately with scanning in the product tracking code
PRDxxx1...

Only ctlItemID is enabled (to prevent accidental scanned into other fields).
ctlSeqNo should auto field so that at one glance the warehouse worker knows
how many are scanned.

It is supposed to be straight forward but unfortunately the warehouse
workers do make mistakes and sometimes scanned in more than the necessary
products. This is why the seq nos need to start from 1 following each invoice.

Thanks.
 
I tried the Function name call but nothing happens (I could have done it
wrongly)

But when I placed the following codes below, it seems to work ok with no
changes when I reenter the control. The only problem is when I edit the
content, the number changes and run off its sequent. It would be ideal if the
sequence is retained on edit.

Private Sub ctlItemID_AfterUpdate()
ctlSeqNo = pfGetNum()
End Sub

Private Sub ctlItemID_Dirty(Cancel As Integer)
If Not Me.NewRecord Then Me.ctlSeqNo = pfGetNum()
End Sub

Public Function pfGetNum()
Static i As Integer
If Left(Me.ctlItemID, 3) = "INV" Then i = 0
i = i + 1
pfGetNum = i
End Function
 
Back
Top