Add multiple sequential records in Access

[Guest]

O wise ones, please help a non-programming novice.
I want to have sequential records automatically added to a table prior to
executing a find unmatched query instead of manually updating the table.
Something along the lines of input box prompting for last value, then code
to insert records from last current to last specified in input...
Suggestions??
Thnx

 

[Larry Daugherty]

Slow down, Suze!

In order to help you get to where you want to be we have to know a
bunch more about where you are.

What sequence? What table, field name, ??

Are you generating sequences now? If yes, how?

What is your real-world application?

HTH

 

[Guest]

Okay, more details...

The application is a basic customized order entry/management system where
each employee logs in information about the files he/she is working, then
other employees update with tracking and billing events. Orders are assigned
a sequential file number by a separate system (that generates shipping
documents) with no EDI (very old system).

I need a simple way to make a validation report to show that every file
number generated through the previous day by the stand-alone system is
accounted for in the database orders table.

Short answer to your question, Orders Table, Field "File Number", data
format is FLB#######, with the sequence being incremented by last digit for
each file, eg. FLB060000001, FLB06000002, etc.

As noted, I am not a programmer...I can usually fight my way through macros
and VBA code to find a way (probably not the simplest way) to do what I need,
but I am past my knowlege and abilities on this...

Thanks,

 

[Larry Daugherty]

I'm too tired and grumpy to be taking this on now and I never like to
play 20 questions.

How do you know what numbers were issues yesterday? About how many
numbers are in a day's run?

Your explanation of the number doesn't compute: there's a 6 in there
so probably not a typo.

If the record has more than one field, please list them all as you did
the first one.

HTH

 

[Guest]

Sorry, Larry...I don't mean to be thick or make you play 20 questions...and I
definitely do not want to add to tired or grumpy, I have enough of that on my
own... If I knew more about what I am doing, I would know what you need to
know to help me out, but I really am that far out of my depth...so thanks
much for your time and knowledge and patience...

Current method: once a month I check the stand-alone system to seewhat the
last number issued was, then I drag from the last cell I entered in an excel
spreadsheet down to that number to create all the entries of the
sequence...update in the db using the linked table manager, then run a find
unmatched query.

I only know what numbers where issued yesterday by looking at the last
number issued in the stand-alone before operations begin today. A day can
vary from 1 to 20 file numbers. The format of the field is set by the other
system - FLB is a standard prefix for every file, the next two digits are YY
(thus the 06), then the actual variable is ###### - it is stored as one field
in the record, though I can certainly split it to simplify incrementing.

Thanks,

 

[Larry Daugherty]

I asked for the names and datatypes of the remaining fields in the
records in your database just in case that information might be useful
to us. Please send it along.

With your explanation I now understand why you were looking for some
algorithm that included a elimination of duplicates. We could do that
so long as field FileNumber is not the primary key for that table.
But, there's a better way:

The following, in broad strokes, is my suggested plan of attack.

1 You'll create an unbound form named something like
frmSeedFileNumbers. On the form will be two text boxes and a couple
of command buttons. The first textbox should be labeled Starting File
Number. The second textbox should be labeled Last File Number. The
range of file numbers including and between those two will be searched
and the missing ones will be added. Missing file numbers will have
the word "Empty" entered in one of your as yet unnamed text fields.
You need only enter from 1 to 6 digits in the textboxes. The code
will prefix the leading zeros.

2. When the above is finished a report will be run to Show Missing
File Numbers

3. The first command button causes all of the above to happen. The
second command button is to simply close the form when you're done.

In the Click event for cmdSeekandSet

check that both textbox controls have text. If not, send the user
a message and cease operation of this code and cancel the event and
exit.

I started to code things but realized I still don't know if
tblOrders might be in a relationship or much of anything else about
your application. I'm going to bed. I hope someone picks this one
up for you during the day.

HTH