Autonumber two diferent fields in same table

[Guest]

Ok, what I need is an anwser for the following problem:
I would like to make a table for several stores, for each store I have a ID
wich the user chooses for the respective store. For each sale I would like
the table to autonumber that sale, but I would lilke it to know at wich
number it is for each store. It should look something like this:

STORE SALE Nº

X1 1
X1 2
X2 1
X1 3
X2 2

Is this possible or do I've to make a table for each store? Thanks in
advanced.

 

[John W. Vinson]

Ok, what I need is an anwser for the following problem:
I would like to make a table for several stores, for each store I have a ID
wich the user chooses for the respective store. For each sale I would like
the table to autonumber that sale, but I would lilke it to know at wich
number it is for each store. It should look something like this:

STORE SALE Nº

X1 1
X1 2
X2 1
X1 3
X2 2

Is this possible or do I've to make a table for each store? Thanks in
advanced.

An Autonumber WON'T work for this - it doesn't (bugs excepted) let you create
duplicate values anywhere in the entire table.

Instead, you can use a Long Integer field for SaleNo and use VBA code to
assign the value. Do you want this to be a dynamic value changing with the
query, or do you want it to be a permanent value for that sale? What do you
want to happen if you realize that a sale was entered erroneously, and it
needs to be deleted or assigned to a different store?

John W. Vinson [MVP]

 

[Guest]

Lets assume I want it to be permanent. If someone entered a sale erroneously
there will be another field like "observations" were employee will write
something like void or null.

 

[John W. Vinson]

Lets assume I want it to be permanent. If someone entered a sale erroneously
there will be another field like "observations" were employee will write
something like void or null.

Ok, you must use a Form to enter the data - tables have no usable events.
Let's say you have a combo box cboStore to pick the store, and a textbox
txtSaleNo bound to the SaleNo field (I'd avoid using blanks or superscripts in
fieldnames!). You could use code in cboStore's AfterUpdate event:

Private Sub cboStore_AfterUpdate()
Me!txtSaleNo = NZ(DMax("[SaleNo]", "[tblSales]", _
"[Store] = '" & Me!cboStore & "'")) + 1
End Sub

This will look up the largest existing value of SaleNo for the selected store,
if there is one; if there isn't, the NZ() function will return 0. In either
case the value found will be incremented by 1 and stored in the txtSaleNo box.

John W. Vinson [MVP]

 

[AaronKempff]

An Autonumber WON'T work for this - it doesn't (bugs excepted) let you create
duplicate values anywhere in the entire table.

Baloney.

Using table:

ID (Autonumber)
Name (Text, primary key)
Phone (Number)

Add record Name="John", Phone=12345
Add record Name="Rick", Phone=12345

There are now duplicates somewhere in the table (Phone).

Add record ID=3, Name="David", Phone=23456
Add record ID=3, Name="Larry", Phone=34567

There are now duplicates in the autonumber field.

Repeat with the autonumber set to increment in steps of 1,073,741,824
(the answer's ADO - "Aunty Em! Aunty Em!"). After the forth records is
added there are autogenerated duplicates in the autonumber field.

Now repeat after me: THERE IS NOTHING INHERENT ABOUT AUTONUMBER THAT
SAYS YOU WILL NOT HAVE DUPLICATES.

Admit it. You were thinking of PRIMARY KEY.

 

[Guest]

John thanks a lot. It is working fine.
Now I've another doubt, is there a way to complicate it a bit more: I would
like add a new columm to the other two, "type of sale", (money or plastic) it
should look something like this:

STORE SALE Nº Type

X1 1 Money
X1 2 Money
X1 1 Plastic
X1 3 Money
X2 1 Money
X2 1 Plastic
X1 3 Money

Is this possible? Basicaly it would be like introducing a new condition.
Could youo help me? Thanks

Lets assume I want it to be permanent. If someone entered a sale erroneously
there will be another field like "observations" were employee will write
something like void or null.

Ok, you must use a Form to enter the data - tables have no usable events.
Let's say you have a combo box cboStore to pick the store, and a textbox
txtSaleNo bound to the SaleNo field (I'd avoid using blanks or superscripts in
fieldnames!). You could use code in cboStore's AfterUpdate event:

Private Sub cboStore_AfterUpdate()
Me!txtSaleNo = NZ(DMax("[SaleNo]", "[tblSales]", _
"[Store] = '" & Me!cboStore & "'")) + 1
End Sub

This will look up the largest existing value of SaleNo for the selected store,
if there is one; if there isn't, the NZ() function will return 0. In either
case the value found will be incremented by 1 and stored in the txtSaleNo box.

John W. Vinson [MVP]

 

[John W. Vinson]

John thanks a lot. It is working fine.
Now I've another doubt, is there a way to complicate it a bit more: I would
like add a new columm to the other two, "type of sale", (money or plastic) it
should look something like this:

STORE SALE Nº Type

X1 1 Money
X1 2 Money
X1 1 Plastic
X1 3 Money
X2 1 Money
X2 1 Plastic
X1 3 Money

Is this possible? Basicaly it would be like introducing a new condition.
Could youo help me? Thanks

The third argument of DLookUp is just a SQL WHERE clause without the word
WHERE, and it can contain multiple components. Try

Private Sub cboStore_AfterUpdate()
Me!txtSaleNo = NZ(DMax("[SaleNo]", "[tblSales]", _
"[Store] = '" & Me!cboStore & "' AND [Type] = '" _
& Me!cboType & "'")) + 1
End Sub

This will require that BOTH the type and store have been selected first and
will probably give an error if they haven't - you may want to put the code in
the afterupdate events of both combo boxes, and check to see if the other
combo is NULL prior to setting txtSaleNo. Left as an exercise for the student!
<g>

John W. Vinson [MVP]