How to insert a range of forms with same data?

[Guest]

I've got a database that we use to track our documents through our office,
serialized with a simple auto-generated number field and occasionally we need
to assign a block of numbers to another department.

So, a functionality we want to incorporate is basically, have a button to a
macro that will pop up a box with fields where we can input the range of
serials, enter a string into the 'subject' field (e.g. "assigned to ... ")
and have a yes/no check box on the form checked, then click 'ok' or whatever
and have a group of records created with that range and data.

How possible is this and am I even making sense?

 

[Larry Linson]

Yes, you can (even if you mean "Access AutoNumber", provided you don't tell
it to create an already-existing key). You aren't inserting any "forms",
but are creating "records".

You'll create an unbound Form, into which you can enter the starting number
(or you can default to starting at the next number), number to create, and
the text for the Subject, then in the Click event of a Command Button use
VBA DAO code to write those Records to your Table.

Larry Linson
Microsoft Access MVP



"Aaron Slater, Navy Region Northwest" <Aaron Slater, Navy Region
(e-mail address removed)> wrote in message
news:[email protected]...

 

[John W. Vinson]

I've got a database that we use to track our documents through our office,
serialized with a simple auto-generated number field and occasionally we need
to assign a block of numbers to another department.

So, a functionality we want to incorporate is basically, have a button to a
macro that will pop up a box with fields where we can input the range of
serials, enter a string into the 'subject' field (e.g. "assigned to ... ")
and have a yes/no check box on the form checked, then click 'ok' or whatever
and have a group of records created with that range and data.

How possible is this and am I even making sense?

You can use a handy little auxiliary table to do this. Create a table named
Num, with one Long Integer field N: fill it with values from 0 through 10000
or so (more than you'll ever need).

Create a little unbound form, frmFill maybe, with a textbox txtStart and
txtEnd, and a command button to launch a query.

Create a Query

INSERT INTO yourtable (ID, Subject)
SELECT N + [Forms]![frmFill]![txtStart]
WHERE N < [Forms]![frmFill]![txtEnd] - [Forms]![frmFill]![txtStart];

Save this query and run it from the command button.

John W. Vinson [MVP]

 

[Guest]

I should note, before a week ago, I'd never really touched access beyond
simple data entry. Of course, since I'm the electronics technician in the
office, I was tasked to redesign their database. so I understand what you're
trying to tell me, I just don't know how to do it...

Heck, if there's somebody local who can meet me at a Starbucks or whatever
somewhere around Bremerton/Seattle/Tacoma to collaborate with, or another way
to show you what I have and where I want to go, I would be unbeleivably
grateful!

Barring that, a step-by step would help, just let me know what info you
need...

 

[Larry Linson]

"Aaron Slater, Navy Region Northwest"

Heck, if there's somebody local who can meet me
at a Starbucks or whatever somewhere around
Bremerton/Seattle/Tacoma to collaborate with, or
another way to show you what I have and where I
want to go, I would be unbeleivably grateful!

There are two Access user groups in the Seattle area. Both meet at Microsoft
facilities in Redmond. You can find information about them by visiting
http://www.microsoft.com/mindshare/ and following the link "find a user
group". I am acquainted with some of them and know you will find the leader
and members helpful.

Barring that, a step-by step would help, just let me
know what info you need...

There'd be a lot of time and effort involved in creating a step-by-step
tutorial -- more than most of us can afford to expend on newsgroup
responses, but I have posted an example database at
http://accdevel.tripod.com, in the "Other Examples" section, entitled Add
Records to Table with AutoNumber. It consists of one Table, and one Form for
the user to enter the desired records to be added/allocated/assigned. The
code that "does the work" is in the Click event of the Command Button on the
Form.

Please note that it does not include all the validation and error checking
that you'll need. I'd suggest, too, that the valid values for Subject be
stored in a Table and the user select the appropriate one with a Combo Box,
instead of typing in a value as the example now provides. Among other
things, the example does not (1) validate that a number entered as starting
value for the key is greater than the highest existing AutoNumber key, (2)
that the number of records requested is "reasonable" {you should decide what
is a reasonable number, whether that be 2, or 10, or 100, and take some
action if the user requests more than your reasonable number}.

Larry Linson
Microsoft Access MVP