Multiple table numbering as one

[kholm]

The problem is I have 48 different species of bettas that each need to have their own set of numbers. After a fish is bred, that species will not have 100 offspring that need numbering in that particular species. For one species it should start at 1 and go to, say, 54; another species should start at 1 and go to,say, 98. Follow me so far....
Now in order to number this way we need to either have some sort of auto number system (48 different tables somehow searchable as one) or one table that only brings up one species so the next consecutive number can be assigend for that species.

Yikes.

Kristin

 

[Pat Hartman]

If you search the archives for autonumber and DMax(), you'll find numerous examples of code generated number sequences.

 

[kholm]

Looked for information

I looked for much information regarding DMax and found a lot. It looks like what I need to do is set up a query and in the Field enter, Fish Number: Dmax(Exp, Domain, Criteria) +1. Does this look right and what is Exp, Domain (I figure criteria is the species, but is domain the table name?) Thank you for all your help.

 

[Pat Hartman]

I'll take a stab at what your DMax() should look like. The code needs to be placed in the BeforeInsert Event of your form.

Me.YourSeqNumField = DMax("YourSeqNumField","YourTableOrQueryName","SpeciesName = '" & Me.SpeciesName & "'") + 1

Replace - YourSeqNumField, YourTableOrQueryName, SpeciesName with the names from your table.

Set the properties of the YourSeqNumField control to enabled = no and locked = yes to prevent modifications by the user.

The warning associated with this method is that in a multi-user environment, it is possible to generate a duplicate "next" number if two people are trying to add a new record at the same time. This is only a serious concern in a high-volume application. So, just be aware, the second person will end up with an unrecoverable error should this happen unless you add error trapping.

 

[kholm]

I feel so close I can taste it

I know I am close, but when I get to that Fish# field, it opens the debugger and highlights me.fish# (the destinationa field). I don't get any numbers in the field. Thanks

 

[Pat Hartman]

Access is very lax in how it allows you to name tables and columns. In contrast, VBA is very rigid. Once you start writing code to do things for you, you need to surround all your "bad" (from VBA's perspective) names with square brackets. Object names should not contain special characters or embedded spaces and they should always start with a letter.

Me.[fish#]

 

[kholm]

Still in need

You have been very helpful, and I would never ask you to do my work for me, but...

I have attached the database so you can see what is going on. Go to the form Fish and look at the number and the code I put in for On Enter. What do you think?

 

[Pat Hartman]

You didn't attach the db.

 

[kholm]

Can't attache file

I have a slimed down version of the database, but it is still to large to attach. Is there a way to get you the database to look at? Thanks
Kristin

 

[Pat Hartman]

Post the code where the error is occuring.

 

[kholm]

Error Code

Here is the code where I am experiencing the problem:

Private Sub Number_Before Enter
Me.Fish# = DMax("Fish#", "Fish", "Species = '" & Me.Species & "'") + 1

Thanks

 

[Pat Hartman]

If you re-read my post from 11/27, you'll see that I told you your field name was causing the problem. Try the following:

Me.[Fish#] = DMax("[Fish#]", "Fish", "Species = '" & Me.Species & "'") + 1

 

[kholm]

Tried

I tried placing the bracets in and still no luck.

 

[Pat Hartman]

If species is numeric, you need to remove the single quotes surrounding it. If that's not the problem, you'll need to post the code you are using and tell us exactly what the error message is.

 

[kholm]

Here it goes

I have a form which is based on a table. With in this form is VB code (just trying to explain the best I can). I need to have an autonumber produced for each species, but they must start at 1 for each new species. The form goes like this, Species (which is a drop down choice box), Number (which should be automaticlly inserted), Father# and Mother # (which is inserted by entry), Wild or domestic, and Notes. Here is what I have as the VB: Private Sub Number_Enter()
Me.[Fish#] = DMax("[Fish#]", "Fish", "Species = '" & Me.Species & "'") + 1

AT this point, when I tab through the form, nothing happens in the Number space, it just goes through and lets me enter whatever I want. Number is not numeric, but text.

 

[Pat Hartman]

Put the statement in the BeforeInsert event of the form. It does not belong in an event associated with a column. Also, set the locked property to yes for the [Fish#] control. That will prevent someone from overtyping the number you generated.

 

[kholm]

I could just scream!

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.[Fish#] = DMax("[Fish#]", "Fish", "Species = '" & Me.Species & "'") + 1

End Sub

This is what I have now, and nothing happens, I could just scream!

Kristin

 

[Pat Hartman]

I could also. If the db will fit, post it here and I'll look at it.

 

[kholm]

Doesn't work

I am unable to attach even a shell version of the database. Is there another way.

 

[Pat Hartman]

Delete all but a few rows of data in each table. Compact the db. Then zip it.