Create Special Autonumber

[Jefferey Simmons]

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

what

I
need it to do?

Please help.

TIA,

Jefferey


.



.

.

 

[Graeme Richardson]

Hi Jeff

Here are my questions: My Answers are in there

1.) Why am I deleting everything from my orders table
(dbanum.Execute...) -
SCARY!!

Because dbanum.Execute "DELETE * FROM Orders;" says delete everything from
the Orders table. Use a where clause to restrict the records that are
deleted.

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.

You can spread a SQL clause over several lines without problem (I've
corrceted syntax so that it [should] work.
Set rsanum = db.OpenRecordset("Insert into temptable ([Max]) " & _
"SELECT Max([orders].[number])+1 AS Max " & _
"FROM Orders " & _
"WHERE (((sale_type)= 'A'))", dbOpenDynaset)
However, you can't use OpenRecordset with an action query.
To run an action query, use dbanum.Execute as in 1).
Also, db is not declared. Turn on Option Explicit, it will help you coding
immensely (you're going to have to trust me on this ).

3.) Am I supposed to create a table called temptable in the DB. I don't
see
a "create table" appearing here.

Sorry, I'm late on this thread. I think that your after changing the query
type from an Append to a Make Table query
dbanum.Execute "SELECT Max([orders].[number])+1 AS [Max] INTO template " &
_
"FROM Orders " & _
"WHERE ((([sale_type])='A')); "

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).

Sorry, I've come in late on this thread.

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?

You've declared the variable but not assigned a database object to it.
Dim dbanum As DAO.Database: set = DbEngine.Workspaces(0).Databases(0)
Also, for completeness,
Dim rsanum As DAO.Recordset

Graeme.