Adding and deleting a record

[BillBee]

I am looking for some code to assist me automatically enter a new number consecutively in a text box. Example. I enter a class number in a text box and when I press enter a query and a macro puts the consecutive exhibit number in the adjacent text box. To get this to work I had to set all the exhibit numbers in the field to 1. If an error is made when you enter the class I have a command button opening an input box asking for the class to be amended. This then displays a form which will allow the record selector to be used to delete the incorrect entry. Now I must manually change the exhibit number to one less than displayed so that further entries in that class remain consecutive. The code I am looking for hopefully make it easier when deleting an incorrect entry. Sounds a bit messy but what I am trying to do is to improve something I already have. Thanks

 

[gemma-the-husky]

maintaining sequences after deleting items is far from easy

a better technique is to flag deleted items in some way, then you can ignore them. I suppose you could flag deleted items, then you could reuse an item by finding the first deleted item.

 

[BillBee]

I need to maintain the numbers in sequence. Eg Suppose you have a class number assigned to a particular orchid. You make an entry for that class and pressing enter assigns Entry 1 to that class next entry same class assigned Entry 2 and so on. As I said to make this work I must start with 1 in every class and as each entry to a class is made the number showing in the field NumberInClass is always one greater that the actual number of entries. An update query run through a macro does that for me. It is when you make a error in entering a class and an exhibit number is assigned and one wants to reduce the exhibit number by one that must be done manually. I was hoping there may be some way to have your NumberInClass that does just show the the true number.

 

[gemma-the-husky]

you can assign the next number by adding one to the current highest number

so if you have a file

name class ref
orchidname 1 1
orchidname 1 2
orchidname 1 3
orchidname 2 1
orchidname 1 2


then the highest number can be found by a simple line of code

highestnumber = nz(dmax("ref","ordchidtable","class = " & whateverclass),0)

the nz allows for a new class, which will not return any number

now the new number can be set simply by

newnumber = highestnumber + 1

------------
as i say, the problem occurs if you delete orchids from the middle of a sequence. If you are using the ref for something, and have documented the ref, then you won't be able to just the change the number. However, if you arent using it for anything (other than to maintain a sequence) then you dont need to renumber it. it shouldn't matter if the sequence goes 1245 or 1234.

It depends whether you refer to orchids by name, or by class and ref. If you use class and ref then you may previously have said class 1 orchid ref 12 is "Summer Sunshine". If you now change the ref, this is likely to cause a lot of confusion. If however, you just refer to the name, then the ref is unimportant.

If you link the orchid ref to other database entities (eg, growing records, stock records) then changing the ref in the orchid table will also mean you need to change the ref in the associated tables.

the question is, why is it so important to have an intact sequence of numbers in each class?

 

[BillBee]

Gemma. I have tried the code use posted but subsitituted my actual field and table names. I have set this up as an On Enter event procedure. The word 'whateverclass' you have given is returning a Compile Error 'Variable not Defined'. As my knowledge of code is limited I am wondering what I have done wrong. If the intention of whateverclass is meant to be writing in the number of the class this would be inconvenient if say an exhibitor had 5 or 6 different classes one would be rewriting the code for each exhibit.

 

[jal]

Gemma. I have tried the code use posted but subsitituted my actual field and table names. I have set this up as an On Enter event procedure. The word 'whateverclass' you have given is returning a Compile Error 'Variable not Defined'. As my knowledge of code is limited I am wondering what I have done wrong. If the intention of whateverclass is meant to be writing in the number of the class this would be inconvenient if say an exhibitor had 5 or 6 different classes one would be rewriting the code for each exhibit.

I'm glad Gemma is understanding you because I sure the heck am totally lost as to what you are trying to accomplish. Anyway, Gemma intended for you to fill in the code there, that is, instead of

"Class = " & whateverClass

It might be

"Class = " & textBoxClassNumber.Value

assuming you have such a textbox on the form and that the user typed in a class number. (I don't even know if this is what you want).

 

[BillBee]

thanks for reply Jal. Maybe I am having trouble explaining what I want but I think Gemma does understand. I have a text box I enter the class number and when I press enter I want consecutive numbers to be written into an adjacent text box. These numbers represent the number of entries associated with that class. One of the things I use this for is to print a label that can be attached to a show exhibit. I have said I have something in place that allows me to do what I want but with the procedure I am using it is awkward when you make an error when you enter a wrong class and I was hoping to find something that would simplify that.

 

[jal]

Also, if Class is a TEXT datatype, you might need to add a single quote char:

"Class = '" & textBoxClassNumber.Value & "'"

 

[BillBee]

Class is a number field