VBA Question

[Greg]

I'm new to VBA.

I have a form that calls a query to SetSellOrder for my auction database.
On the form I would like a botton which will renumber the sell order. There
must be a real simple way to loop throught the database and reorder - but I
can't program it!

Here's the details:
Table: tblAnimals
Fields: Species, Entry_Order, Sale_Order, In_Auction

The query pulls the the table and puts the animals in Entry_Order sort and
limits the animals to In_Auction = Yes. This Entry_Order field sorts the
entire database into the correct sales order and is already in the database.
The Sale_Order is the order that an individual Species will sell in. So I
have:

Species Entry_Order Sale_order
Beef 1001
Beef 1002
Beef 1003
etc
Sheep 2001
Sheep 2002
Sheep 2003
Sheep 2004
etc
Rabbits 3001
Rabbits 3002
Rabbits 3003
etc

I would like to click a botton and have the Sale_Order filled in
automatically like so:
Species Entry_Order Sale_Order
Beef 1001 1
Beef 1002 2
Beef 1003 3
etc
Sheep 2001 1
Sheep 2002 2
Sheep 2003 3
Sheep 2004 4
etc
Rabbits 3001 1
Rabbits 3002 2
Rabbits 3003 3
etc

Trying to get just a little bit of code to work and progress from there, so
far I have:

Private Sub SetSaleOrder_Click()
Dim i
For i = 1 To 4
[Sale_Order] = i
i = i + 1
DoCmd.RunCommand acCmdRecordsGoToNext

Next

End Sub


I was jsut trying to see if I could get any code to work and realize this
code won't accomplish my goal. The above code updates the first 2 Sale_Order
fields with 1 and 3. I don't konw why.

Ultimately, I'd like the program to go to the top record and parse down the
whole database and renumber the Sale_Order from 1 to the count of that
Species and than start over for the next Species.

Thanks for any help.

 

[dch3]

-What is the specific NEED to have the records numbered 1...2...3...?

-Also, are you in a situation where once everything is numbered, an item
might be added mid-way up that bumps things down? In other words, would have
a case where a new item should be in position #2 so that the current #2
becomes 3 and 3 becomes 4, etc?

-Since records are not necessarily returned in any particular order by the
Database Engine, you have to explicitly sort them. If the items are to be
auctioned in an order that is different from the order in which they're
entered, you may have to manually assign the numbers - since there'd be no
way for Access to identify which record should be auctioned first.

 

[Greg]

The Sale_Order is printed on many of the reports that I generate and also is
used to sort many of the reports. The Entry_Order is only used to get the
auction in correct order before the auction.

The initial Entry_Order is put in and this order will not change. The
Grand Champion and Reserve Grand Champion animals get moved up to positions 1
& 2. I do this now by renumbering the Entry_Order field. Actually, when the
Entry_Order is initial assigned I leave out positions 1 & 2. For Beef in the
example I gave the first number assigned as be 1003. (These Entry_Order
numbers are just the drawing that is done to determine sale order where they
are pulling numberred balls out of a can (numbers 1 & 2 are missing). So,
the entries when they come to me are maybe 3,4,7,10,11,15,16, etc where the
series is not consecutive. I just add the 1003, 1004, 1007, etc to them so
that that species will be first in the sale order. The second speices i
start with 2003, etc. When the Grand Champion is known, I change that
Entry_Order number to 1001 and it sales first and the Reserve Grand Champion
changes to 1002 and sales second. The Entry_Order field is than in the
correct order but I want 1,2,3 in the Sale_Order field to be what prints out
on forms & reports. So the Entry_Order field is always in the correct
overall auction order. Then, even it I have a change of any kind or maybe
even a deletion (because an animal died before the auction) I simple change
the Entry_Order numbers and resort.

Currently, I am typing in 1,2,3,4, etc in the Sale_Order after I get the
auction order set. I was hoping for a program solution becuase of speed and
preventing errors. (I've been known to type 1,2,3,4,5,6,8,9,10 when trying
to go real fast.)

Does that explain things?

-What is the specific NEED to have the records numbered 1...2...3...?

-Also, are you in a situation where once everything is numbered, an item
might be added mid-way up that bumps things down? In other words, would have
a case where a new item should be in position #2 so that the current #2
becomes 3 and 3 becomes 4, etc?

-Since records are not necessarily returned in any particular order by the
Database Engine, you have to explicitly sort them. If the items are to be
auctioned in an order that is different from the order in which they're
entered, you may have to manually assign the numbers - since there'd be no
way for Access to identify which record should be auctioned first.

I'm new to VBA.

I have a form that calls a query to SetSellOrder for my auction database.
On the form I would like a botton which will renumber the sell order. There
must be a real simple way to loop throught the database and reorder - but I
can't program it!

Here's the details:
Table: tblAnimals
Fields: Species, Entry_Order, Sale_Order, In_Auction

The query pulls the the table and puts the animals in Entry_Order sort and
limits the animals to In_Auction = Yes. This Entry_Order field sorts the
entire database into the correct sales order and is already in the database.
The Sale_Order is the order that an individual Species will sell in. So I
have:

Species Entry_Order Sale_order
Beef 1001
Beef 1002
Beef 1003
etc
Sheep 2001
Sheep 2002
Sheep 2003
Sheep 2004
etc
Rabbits 3001
Rabbits 3002
Rabbits 3003
etc

I would like to click a botton and have the Sale_Order filled in
automatically like so:
Species Entry_Order Sale_Order
Beef 1001 1
Beef 1002 2
Beef 1003 3
etc
Sheep 2001 1
Sheep 2002 2
Sheep 2003 3
Sheep 2004 4
etc
Rabbits 3001 1
Rabbits 3002 2
Rabbits 3003 3
etc

Trying to get just a little bit of code to work and progress from there, so
far I have:

Private Sub SetSaleOrder_Click()
Dim i
For i = 1 To 4
[Sale_Order] = i
i = i + 1
DoCmd.RunCommand acCmdRecordsGoToNext

Next

End Sub


I was jsut trying to see if I could get any code to work and realize this
code won't accomplish my goal. The above code updates the first 2 Sale_Order
fields with 1 and 3. I don't konw why.

Ultimately, I'd like the program to go to the top record and parse down the
whole database and renumber the Sale_Order from 1 to the count of that
Species and than start over for the next Species.

Thanks for any help.

 

[Greg]

Playing around I finally have a little code that will run and give me what I
want.
I'd like to replace the number 50 in the for statement with a count of the
number of animals in that species variable (but I can't figure out how). I
also need to test for the last record and not proceed beyond the last record.
If anyone can help me with these two items, I would be most grateful.

Private Sub SetSaleOrder_Click()
Dim i
Dim cspecies

cspecies = [Species]
i = 1

For i = 1 To 50

[Sale_Order] = i

DoCmd.RunCommand acCmdRecordsGoToNext
If [Species] <> cspecies Then
cspecies = [Species]
i = 0
End If

Next

End Sub

The Sale_Order is printed on many of the reports that I generate and also is
used to sort many of the reports. The Entry_Order is only used to get the
auction in correct order before the auction.

The initial Entry_Order is put in and this order will not change. The
Grand Champion and Reserve Grand Champion animals get moved up to positions 1
& 2. I do this now by renumbering the Entry_Order field. Actually, when the
Entry_Order is initial assigned I leave out positions 1 & 2. For Beef in the
example I gave the first number assigned as be 1003. (These Entry_Order
numbers are just the drawing that is done to determine sale order where they
are pulling numberred balls out of a can (numbers 1 & 2 are missing). So,
the entries when they come to me are maybe 3,4,7,10,11,15,16, etc where the
series is not consecutive. I just add the 1003, 1004, 1007, etc to them so
that that species will be first in the sale order. The second speices i
start with 2003, etc. When the Grand Champion is known, I change that
Entry_Order number to 1001 and it sales first and the Reserve Grand Champion
changes to 1002 and sales second. The Entry_Order field is than in the
correct order but I want 1,2,3 in the Sale_Order field to be what prints out
on forms & reports. So the Entry_Order field is always in the correct
overall auction order. Then, even it I have a change of any kind or maybe
even a deletion (because an animal died before the auction) I simple change
the Entry_Order numbers and resort.

Currently, I am typing in 1,2,3,4, etc in the Sale_Order after I get the
auction order set. I was hoping for a program solution becuase of speed and
preventing errors. (I've been known to type 1,2,3,4,5,6,8,9,10 when trying
to go real fast.)

Does that explain things?

-What is the specific NEED to have the records numbered 1...2...3...?

-Also, are you in a situation where once everything is numbered, an item
might be added mid-way up that bumps things down? In other words, would have
a case where a new item should be in position #2 so that the current #2
becomes 3 and 3 becomes 4, etc?

-Since records are not necessarily returned in any particular order by the
Database Engine, you have to explicitly sort them. If the items are to be
auctioned in an order that is different from the order in which they're
entered, you may have to manually assign the numbers - since there'd be no
way for Access to identify which record should be auctioned first.

I'm new to VBA.

I have a form that calls a query to SetSellOrder for my auction database.
On the form I would like a botton which will renumber the sell order. There
must be a real simple way to loop throught the database and reorder - but I
can't program it!

Here's the details:
Table: tblAnimals
Fields: Species, Entry_Order, Sale_Order, In_Auction

The query pulls the the table and puts the animals in Entry_Order sort and
limits the animals to In_Auction = Yes. This Entry_Order field sorts the
entire database into the correct sales order and is already in the database.
The Sale_Order is the order that an individual Species will sell in. So I
have:

Species Entry_Order Sale_order
Beef 1001
Beef 1002
Beef 1003
etc
Sheep 2001
Sheep 2002
Sheep 2003
Sheep 2004
etc
Rabbits 3001
Rabbits 3002
Rabbits 3003
etc

I would like to click a botton and have the Sale_Order filled in
automatically like so:
Species Entry_Order Sale_Order
Beef 1001 1
Beef 1002 2
Beef 1003 3
etc
Sheep 2001 1
Sheep 2002 2
Sheep 2003 3
Sheep 2004 4
etc
Rabbits 3001 1
Rabbits 3002 2
Rabbits 3003 3
etc

Trying to get just a little bit of code to work and progress from there, so
far I have:

Private Sub SetSaleOrder_Click()
Dim i
For i = 1 To 4
[Sale_Order] = i
i = i + 1
DoCmd.RunCommand acCmdRecordsGoToNext

Next

End Sub


I was jsut trying to see if I could get any code to work and realize this
code won't accomplish my goal. The above code updates the first 2 Sale_Order
fields with 1 and 3. I don't konw why.

Ultimately, I'd like the program to go to the top record and parse down the
whole database and renumber the Sale_Order from 1 to the count of that
Species and than start over for the next Species.

Thanks for any help.