Create My own Autonumber


S

stfcTerryA

Hi

I have a form called PurchaseOrders and a subform called POItems

subform POitems is bound to fields in a table

POItemID - Autonumber (Hidden)
POID - link from PurchaseOrderID (Hidden)
LineItem - Number - The field i wish to autonumber
Component - Text
Qty - Number
Price - Number
SubTotal - Number (qty * price)

What i'm trying to do is in the subform generate my own autonumber for the
line item, as in for every PO it restarts from 1 (default value) then as i
select another component it automatically adds 1 to the previous line item.
For example:

1, 12K Resistor, 100, £0.12, £12.00
2, 10K Resistor, 50, £0.15, £7.50

So when i select the 10K Resistor the after update populates the line item
with "2"

Cheers



Terry
 
Ad

Advertisements

A

Allen Browne

Is this a multi-user database? If so, you need to consider what happens if 2
users are adding records at the same time. (That may be unlikely in the same
PO, but is possible.)

For that reason, you might want to consider using the BeforeUpdate event of
the Form to assign this number. That's the last event before the record gets
saved.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
strWhere = "POItemID = " & Nz(Me.Parent!POItemID,0)
Me.LineItem = Nz(DMax("LineItem", "Table2", strWhere),0)+1
End Sub
 
S

stfcTerryA

Hi Allen

Thanks for responding but have a few problems: In trying to resolve i've
changed the tables and field names around, so appologies for that.

What i'm understanding - please correct me cause i must be going wrong
somewhere:

strWhere = "POItemID = " & Nz(Me.Parent!POItemID,0)

This is designed to create a string "POItemID = S4604" where S4604 is the
Purchase Order number that is found in "Table2" which should link the Parent
Form with the Sub Form.

Me.LineItem = Nz(DMax("LineItem", "Table2", strWhere),0)+1

This section of code looks at Table2, Finds the highest value in the
LineItem field where POItemsID matches any records with the value = S4604. If
Null as in first record returns the value 0 which you add 1 to and that
becomes the value in Me.LineItem.

Unfortunately i get the response "The Expression you entered as a query
parameter produced this error: S4604

So somewhere i must be going wrong and prob don't understand what it is i'm
trying to do: Anyway so you may have a better understanding here are the
tables names etc.

tblPurchaseOrders
f: POrderID - Text

tblPurchaseOrderItems
f: POrderItemsID - Autonumber
f: POrderID - Text (Linked to tblPurchaseOrders)
f: LineItems - Number
f: Component - Number (Linked to tblComponents)

frmPOrder
tb: POrderID (control source to tblPurchaseOrders.POrderID)

frmPOrderItems
tb: LineItem (control source to tblPurchaseOrderItems.LineItems)
cbo: Component (control source to tblComponents)

Private Sub Component_BeforeUpdate(Cancel As Integer)

Dim strWhere As String
strWhere = "POrderID = " & Nz(Me.Parent!POrderID, 0)
Me.LineItem = Nz(DMax("LineItem", "tblPurchaseOrderItems", strWhere), 0)
+ 1

End Sub

Any help greatly appreciated plus wish i was in Perth right now, once apon a
time i lived in freo and worked in diving ventures - wish i was there now. In
fact a friend of mine is of there in early feb to go see her sister and see
the simply red concert, wish i was going could buy you a pint.

Cheers



Terry
 
S

stfcTerryA

Hi Allen

Works perfectly!! - Many thanks for your guidence.

I have noticed that if a mistake is made in selecting the component hence
the user reselects, the lineitem goes up by 1 - Initially i changed DMax to
DCount - thinking i was cleaver (we know the answer to that one) but this
still means i have a count 1 higher than the number of rows, to be honest i
can live with it and just let the user retype the correct line item number
but if you have any suggestions it would be greatly appreciated. Once again
many thanks

Cheers


Terry
 
A

Allen Browne

Perhaps you want to do this only if it is a new record, i.e.:
If Me.NewRecord Then
'put your code in here
End If
 
Ad

Advertisements

S

stfcTerryA

Hi Allen

Works a treat - i sit here laughing at myself, so simple yet to me so
difficult i had no idea.

Maybe by the time i finish designing this database i will know 1% of what
access can do, well on to then next part "goods in" which i have no idea how
to link them all together, but guess i'll find out eventually.

Well thanks once again for all your assistance very much appreciated and i'm
sure you've saved me a few grey hairs.

Cheers


Terry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top