Search GoTo Filter

[ablatnik via AccessMonster.com]

I have a workorder database that while entering new information, became
corrupt and than restarted and all is fine now except the autonumber skipped
one. This is not a problem, but where I need the help is;

Is it possible to turn off the GoTo Search filter built into MS Forms. I am
using the autonumber the same as the work order number. Since the error, the
numbers in First/Last Add new record are off by one. I can use the CTRL+F to
find a record or build a custom search box,

I just don't need to see record # of record #.

 

[John W. Vinson]

I have a workorder database that while entering new information, became
corrupt and than restarted and all is fine now except the autonumber skipped
one. This is not a problem, but where I need the help is;

Is it possible to turn off the GoTo Search filter built into MS Forms. I am
using the autonumber the same as the work order number. Since the error, the
numbers in First/Last Add new record are off by one. I can use the CTRL+F to
find a record or build a custom search box,

I just don't need to see record # of record #.

This is a good example of why it's a Bad Idea to use an Autonumber field for
*ANYTHING* other than a meaningless unique key. It's not guaranteed to be
sequential, it's not guaranteed to be gapless, and it certainly will not (and
should not!!) match the record count in a form.

If you want a human-meaningful ID number, don't use an autonumber; instead use
a Long Integer and maintain it yourself, either manually or with VBA code.

You can turn off the the "record n of m" display on the form by setting the
form's Navigation Buttons property to No; you'll want to program your own
navigation buttons if you do so.

 

[Danny J. Lesandrini]

I don't know what the Go To Search filter built into MS Forms is (and I've been programming
Access for over a dozen years), but I can help you perpetuate your improper use of Autonumbers,
if that's what you really want to do.

If the table is tblWorkOrder and the Autonumber field is WorkorderID and the missing number
is 1234, then simply execute this INSERT SQL from a new query:

INSERT INTO tblWorkOrder (WorkorderID) VALUES (1234 )

Of course, it's not really this easy, because there are probably other required fields, but you can
replace previously deleted or missing rows in a table with an Autonumber by doing an explicit
INSERT like this.

Keep this script around, though, because you'll be needing it again if you plan to attribute
meaning to the Autonumber field.

 

[ablatnik via AccessMonster.com]

Thank you, what you provided work...sorta. I inserted the record, and my
record count ended up messing up. My autonumber was indexed with no
duplicates. After I serted this record (1526), I would start a new work
order. The number that should up was (1534) I used this record 2 days prior.
I would cancel and start another new and it would give me (1535). After that,
I figured, missing one record is not so bad. I just restored the database.
Thanks for your time and lesson learned. The previous responder mentioned to
use a long integer and VBA code. I know not VBA and am going to see if he
might be able to direct me along these lines.

I don't know what the Go To Search filter built into MS Forms is (and I've been programming
Access for over a dozen years), but I can help you perpetuate your improper use of Autonumbers,
if that's what you really want to do.

If the table is tblWorkOrder and the Autonumber field is WorkorderID and the missing number
is 1234, then simply execute this INSERT SQL from a new query:

INSERT INTO tblWorkOrder (WorkorderID) VALUES (1234 )

Of course, it's not really this easy, because there are probably other required fields, but you can
replace previously deleted or missing rows in a table with an Autonumber by doing an explicit
INSERT like this.

Keep this script around, though, because you'll be needing it again if you plan to attribute
meaning to the Autonumber field.

I have a workorder database that while entering new information, became
corrupt and than restarted and all is fine now except the autonumber skipped

[quoted text clipped - 6 lines]

I just don't need to see record # of record #.

 

[ablatnik via AccessMonster.com]

Thank you. My lesson is learned. I used the code that Danny provided and it
really secured what you were talking about. I restored the database and left
skipped record alone now and removed the navigation buttons.

I can not write VBA but understand some of it. You mentioned to use a long
integer and VBA code. Can you give me an example or some direction into
creating this?

I have a workorder database that while entering new information, became
corrupt and than restarted and all is fine now except the autonumber skipped

[quoted text clipped - 6 lines]

I just don't need to see record # of record #.

This is a good example of why it's a Bad Idea to use an Autonumber field for
*ANYTHING* other than a meaningless unique key. It's not guaranteed to be
sequential, it's not guaranteed to be gapless, and it certainly will not (and
should not!!) match the record count in a form.

If you want a human-meaningful ID number, don't use an autonumber; instead use
a Long Integer and maintain it yourself, either manually or with VBA code.

You can turn off the the "record n of m" display on the form by setting the
form's Navigation Buttons property to No; you'll want to program your own
navigation buttons if you do so.

 

[John W. Vinson]

Thank you. My lesson is learned. I used the code that Danny provided and it
really secured what you were talking about. I restored the database and left
skipped record alone now and removed the navigation buttons.

I can not write VBA but understand some of it. You mentioned to use a long
integer and VBA code. Can you give me an example or some direction into
creating this?

The code depends somewhat on your situation. If you are the only one entering
new records - or if there will only be one person at a time doing so - you can
use code in the form's Before Insert event. Assuming that the field is named
InvoiceNo and that it's a Long Integer (not an autonumber!!) you can use:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!InvoiceNo = DMax("InvoiceNo", "tablename") + 1
End Sub

This does asssume that there are already records in the table. Deleted records
will, of course, leave a gap and will leave the InvoiceNo out of synch with
the record count; that's a good reason to instead have a yes/no field
Cancelled in the table, and simply set it to True to "logically" delete an
invoice. You'ld keep the invoice number with an indication that it is no
longer valid - this keeps auditors happy (or at least as happy as an auditor
can ever be, which isn't saying much).

 

[ablatnik via AccessMonster.com]

Thanks. I will try something along these lines.

Thank you. My lesson is learned. I used the code that Danny provided and it
really secured what you were talking about. I restored the database and left

[quoted text clipped - 3 lines]

integer and VBA code. Can you give me an example or some direction into
creating this?

The code depends somewhat on your situation. If you are the only one entering
new records - or if there will only be one person at a time doing so - you can
use code in the form's Before Insert event. Assuming that the field is named
InvoiceNo and that it's a Long Integer (not an autonumber!!) you can use:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!InvoiceNo = DMax("InvoiceNo", "tablename") + 1
End Sub

This does asssume that there are already records in the table. Deleted records
will, of course, leave a gap and will leave the InvoiceNo out of synch with
the record count; that's a good reason to instead have a yes/no field
Cancelled in the table, and simply set it to True to "logically" delete an
invoice. You'ld keep the invoice number with an indication that it is no
longer valid - this keeps auditors happy (or at least as happy as an auditor
can ever be, which isn't saying much).