Create special autonumber.

  • Thread starter Thread starter Jefferey Simmons
  • Start date Start date
J

Jefferey Simmons

Hi,

I have a table called Orders that is used to log orders. The order can be
either sale_typeA or sale_typeB. So one field in the table is called
Sale_type and is used to specify the type of sale; either A or B. There is
a second field, Number, that gives that order a number. Both fields are
primary keys (multiple-field primary key), indexed -duplicates OK. The
number in the second field is to be automatically generated after the
sale_type has been updated (AfterUpdate). Now this is when I get lost. I
need to make the numbering of sale_typeA independent of saletypeB. So what
I want it to do is query the table to find the Max entry of Number where
sale_type=A or sale_type=B, add 1 to that value and insert it into Number of
the current field. So in a SQL statement it might look something like this:

SELECT Max(Number) as Max
FROM Orders
WHERE sale_type='A'

Then somehow that the value of Max, add 1 to it and update Number of the new
and current record in Orders with that value.

I can't find a way to do this using update or SQL statements and the only
other thing I have come across is record sets. Does anyone know how I would
declare something like this in VB and get it to do what I need it to do?

Please help.

TIA,

Jefferey
 
Hi,

Thanks for that. Now how do I get that into the VB code of my form so that
whenever I set a value for the sale_type control it updates the number
control with the value from the select statement below. Thanks again.

Jefferey
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE sale_type='A';
-----Original Message-----
Hi,

I have a table called Orders that is used to log orders. The order can be
either sale_typeA or sale_typeB. So one field in the table is called
Sale_type and is used to specify the type of sale; either A or B. There is
a second field, Number, that gives that order a number. Both fields are
primary keys (multiple-field primary key), indexed - duplicates OK. The
number in the second field is to be automatically generated after the
sale_type has been updated (AfterUpdate). Now this is when I get lost. I
need to make the numbering of sale_typeA independent of saletypeB. So what
I want it to do is query the table to find the Max entry of Number where
sale_type=A or sale_type=B, add 1 to that value and insert it into Number of
the current field. So in a SQL statement it might look something like this:

SELECT Max(Number) as Max
FROM Orders
WHERE sale_type='A'

Then somehow that the value of Max, add 1 to it and update Number of the new
and current record in Orders with that value.

I can't find a way to do this using update or SQL statements and the only
other thing I have come across is record sets. Does anyone know how I would
declare something like this in VB and get it to do what I need it to do?

Please help.

TIA,

Jefferey


.
Click to expand...
Click to expand...
 
dim dbanum as dao.database
dim rsanum as recordset
dbanum.Execute "DELETE * FROM Orders;"
Set rsanum = db.OpenRecordset("Insert into temptable Max
([orders].[number])+1 AS number
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE (((sale_type)= "A"))", dbOpenDynaset)
me!txtcontrolnumber = Dlookup("[number]","temptable","")

temptable is a 1 record, 1 field table that hold your auto
number untill you need a new one.

-----Original Message-----
Hi,

Thanks for that. Now how do I get that into the VB code of my form so that
whenever I set a value for the sale_type control it updates the number
control with the value from the select statement below. Thanks again.

Jefferey
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE sale_type='A';
-----Original Message-----
Hi,

I have a table called Orders that is used to log orders. The order can be
either sale_typeA or sale_typeB. So one field in the table is called
Sale_type and is used to specify the type of sale;
Click to expand...
either
A or B. There is
a second field, Number, that gives that order a number. Both fields are
primary keys (multiple-field primary key), indexed - duplicates OK. The
number in the second field is to be automatically generated after the
sale_type has been updated (AfterUpdate). Now this is when I get lost. I
need to make the numbering of sale_typeA independent of saletypeB. So what
I want it to do is query the table to find the Max entry of Number where
sale_type=A or sale_type=B, add 1 to that value and insert it into Number of
the current field. So in a SQL statement it might look something like this:

SELECT Max(Number) as Max
FROM Orders
WHERE sale_type='A'

Then somehow that the value of Max, add 1 to it and update Number of the new
and current record in Orders with that value.

I can't find a way to do this using update or SQL statements and the only
other thing I have come across is record sets. Does anyone know how I would
declare something like this in VB and get it to do what
Click to expand...
I
need it to do?
Please help.

TIA,

Jefferey


.
Click to expand...
Click to expand...


.
Click to expand...
 
hi again,
I click the send button too soon
put the code in the sub called newrecord()
you can fire it with a button.
-----Original Message-----

dim dbanum as dao.database
dim rsanum as recordset
dbanum.Execute "DELETE * FROM Orders;"
Set rsanum = db.OpenRecordset("Insert into temptable Max
([orders].[number])+1 AS number
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE (((sale_type)= "A"))", dbOpenDynaset)
me!txtcontrolnumber = Dlookup("[number]","temptable","")

temptable is a 1 record, 1 field table that hold your auto
number untill you need a new one.

-----Original Message-----
Hi,

Thanks for that. Now how do I get that into the VB code of my form so that
whenever I set a value for the sale_type control it updates the number
control with the value from the select statement below. Thanks again.

Jefferey
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE sale_type='A';

-----Original Message-----
Hi,

I have a table called Orders that is used to log orders.
The order can be
either sale_typeA or sale_typeB. So one field in the
table is called
Sale_type and is used to specify the type of sale; either
A or B. There is
a second field, Number, that gives that order a number.
Both fields are
primary keys (multiple-field primary key), indexed -
duplicates OK. The
number in the second field is to be automatically
generated after the
sale_type has been updated (AfterUpdate). Now this is
when I get lost. I
need to make the numbering of sale_typeA independent of
saletypeB. So what
I want it to do is query the table to find the Max entry
of Number where
sale_type=A or sale_type=B, add 1 to that value and
insert it into Number of
the current field. So in a SQL statement it might look
something like this:

SELECT Max(Number) as Max
FROM Orders
WHERE sale_type='A'

Then somehow that the value of Max, add 1 to it and
update Number of the new
and current record in Orders with that value.

I can't find a way to do this using update or SQL
statements and the only
other thing I have come across is record sets. Does
anyone know how I would
declare something like this in VB and get it to do
Click to expand...
Click to expand...
what
I


.
Click to expand...
.
Click to expand...
 
Hello,

This is just not working for me. What I have is this:

Private Sub cboSales_Type_AfterUpdate()

If cboSales_Type = "TRP" Then
Dim dbanum As DAO.Database
Dim rsanum As Recordset
dbanum.Execute "DELETE * FROM Orders;"
Set rsanum = db.OpenRecordset("Insert into temptable
Max([orders].[numbers])+1 AS Max SELECT Max([orders].[number])+1 AS Max FROM
Orders WHERE (((sale_type)= 'A'))", dbOpenDynaset)
Me!txtcontrolnumber = DLookup("[number]", "temptable", "")
End If

End Sub

Here are my questions:

1.) Why am I deleting everything from my orders table (dbanum.Execute...) -
SCARY!!
2.) Is the line "set rsanum..." supposed to be on one line. When I try to
run it over several lines I end up with all kinds of debugging to do, even
when I use underscores.
3.) Am I supposed to create a table called temptable in the DB. I don't see
a "create table" appearing here.
4.) Why do you suggest that I fire it with a button? I want to run it as an
AfterUpdate event of a particular control (see code above).
5.) Lastly, when I run the code it stops on "dbanum.Execute..." and I get
the following error message:
Run-time error '91':
Object varaible or With block variable not set
Why? If I comment that line out it stops on the next line, "set rsanum =
...." with error:
Run-time Error '424'
Object required
Again, Why?

Please help? I don't understand why it is so difficult to insert a bit of
data that is so easy to extract. It's ridiculous! Do you know of any good
links that explains fully, for dummies!!, how to define, manipulate, and
extract data from a recordset into a variable or field?

TIA,

Jeff


hi again,
I click the send button too soon
put the code in the sub called newrecord()
you can fire it with a button.
-----Original Message-----

dim dbanum as dao.database
dim rsanum as recordset
dbanum.Execute "DELETE * FROM Orders;"
Set rsanum = db.OpenRecordset("Insert into temptable Max
([orders].[number])+1 AS number
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE (((sale_type)= "A"))", dbOpenDynaset)
me!txtcontrolnumber = Dlookup("[number]","temptable","")

temptable is a 1 record, 1 field table that hold your auto
number untill you need a new one.

-----Original Message-----
Hi,

Thanks for that. Now how do I get that into the VB code of my form so that
whenever I set a value for the sale_type control it updates the number
control with the value from the select statement below. Thanks again.

Jefferey
SELECT Max([orders].[number])+1 AS number
FROM Orders
WHERE sale_type='A';

-----Original Message-----
Hi,

I have a table called Orders that is used to log orders.
The order can be
either sale_typeA or sale_typeB. So one field in the
table is called
Sale_type and is used to specify the type of sale; either
A or B. There is
a second field, Number, that gives that order a number.
Both fields are
primary keys (multiple-field primary key), indexed -
duplicates OK. The
number in the second field is to be automatically
generated after the
sale_type has been updated (AfterUpdate). Now this is
when I get lost. I
need to make the numbering of sale_typeA independent of
saletypeB. So what
I want it to do is query the table to find the Max entry
of Number where
sale_type=A or sale_type=B, add 1 to that value and
insert it into Number of
the current field. So in a SQL statement it might look
something like this:

SELECT Max(Number) as Max
FROM Orders
WHERE sale_type='A'

Then somehow that the value of Max, add 1 to it and
update Number of the new
and current record in Orders with that value.

I can't find a way to do this using update or SQL
statements and the only
other thing I have come across is record sets. Does
anyone know how I would
declare something like this in VB and get it to do
Click to expand...
what
I
need it to do?

Please help.

TIA,

Jefferey


.



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top