VBA Question

[Greg]

I have a little program that consecutively numbers the Sale_Order field
within each Species:

Private Sub SetSaleOrder_Click()
Dim i
Dim cspecies

cspecies = [Species]
i = 1

For i = 1 To 95
[Sale_Order] = i

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

Next

I run this on a form: frmSetSaleOrder
which calls a query: qrySetSaleOrder
which is made from my main table: tblAnimals

I'd like to be able to stop the code when I get to the last record - so it
doesn't write new records. Any suggestions?

Ultimately, I'd like to be able to replace the 95 above (which is the
largest number for any of the species) with a variable which counts the
members of that species - but I haven't figured that out either.

Also, I'd like to not allow records to be added while this form is open.
How do I make the data updateable but prevent new records to be added?

Any help is appreciated. I've order a couple of books on VBA . . . so my
education will commence soon.

 

[Greg]

Bonnie,

Thanks for your reply. I figured out the Allow Additions soon after I sent
my question by comparing to another form I had that wasn't updatable. Do you
know the code I could use to test for last record and end sub if at the last
record?

Thanks!

Greg

Hi Greg -

In the form's properties, click on the Data tab and change "Allow Additions"
to "NO." Then you won't be able to add any records. To get the count of
species I think the best way would be, when the species is selected (in the
OnExit event of whatever control), run a parameter query (refer to the
control on the form for the parameter) that counts and passes that number as
a variable to your sub below.

Bonnie
http://www.dataplus-svc.com

I have a little program that consecutively numbers the Sale_Order field
within each Species:

Private Sub SetSaleOrder_Click()
Dim i
Dim cspecies

cspecies = [Species]
i = 1

For i = 1 To 95
[Sale_Order] = i

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

Next

I run this on a form: frmSetSaleOrder
which calls a query: qrySetSaleOrder
which is made from my main table: tblAnimals

I'd like to be able to stop the code when I get to the last record - so it
doesn't write new records. Any suggestions?

Ultimately, I'd like to be able to replace the 95 above (which is the
largest number for any of the species) with a variable which counts the
members of that species - but I haven't figured that out either.

Also, I'd like to not allow records to be added while this form is open.
How do I make the data updateable but prevent new records to be added?

Any help is appreciated. I've order a couple of books on VBA . . . so my
education will commence soon.