Entering many new records at once

[ptlm65]

I need to create a database where a user can create several new record
at once.

I work for a police department and they need to have a database fo
traffic summons. They need to be able to have one user enter severa
summons numbers at one time, that are assigned to one officer.

for instance, entering Summons #'s 05-1 thru 05-25 assigned to Office
A. I know I need to create seperate tables for the summons numbers an
the officer's name. Is it possible to enter several summons number
automaticaly at once to one particular officer? Like having summon
05-1 thru 05-25 entered with one click assigned to one officer? An
help would be great. I have been banging my head over it for a while
Thank

 

[Rob Oldfield]

There would be various ways of doing it, but the first that comes to my mind
would be something like...

Form with a combo (allowing you to pick an officer's ID), one text box for
the '05' prefix, and another two for start and end numbers. A go button
would then run something like...

dim db as database
dim rs as recordset
set db=currentdb
set rs=db.openrecordset("YourTable")
dim i as integer
for i=me.StartNum to me.EndNum
rs.addnew
rs!Officer=me.cboOfficer
rs!SummonsNumber=me.prefix+"-"+cstr(i)
rs.update
next
rs.close
set db=nothing

 

[ptlm65]

How would I set up the two text boxes, one for the start summons numbe
and the second for the end summons number?

Also, when I enter the code below after *Private Sub Command6_Click()
then i get an error message stating "User-difined type not defined" Ca
anyone help? Thanks

 

[Rob Oldfield]

http://www.mvps.org/access/bugs/bugs0031.htm is the 'not defined' thing.

The two text boxes are, I'm afraid, something that you really should already
know before you start doing this kind of thing. Do you have someone who
could give you a quick runthrough of Access coding basics?

 

[ptlm65]

I know how to create the text boxes, but I am not sure how to set the
up with regards to the code below. For instance, what control source d
I link to the "start" number box and which one for the "end" number box
Do they both have the same control source, being the "summons" table?
created the combo box for the "officer" table but do I add a text bo
with the control source being the "officer" table as well? Basically,
have two tables, "Summons" and "Officer" Any help would be great
Thanks

 

[Rob Oldfield]

My apologies. I misunderstood your question. None of the controls needs to
be bound at all. They're just there to provide the parameters necessary to
write information into the tables.

 

[ptlm65]

No problem. Thanks for clarifying. The problem I am having when enterin
the code below is that it highlights "me.cboOfficer" and shows an erro
message of "unknown method or data member" Can anyone clarify what
may be doing wrong? I created two unbound text boxes naming the
"Start" and "End" then I created a combo box linked to the "Officer
table. Then a command button with the code below. Then I get that erro
message.

My apologies. I misunderstood your question. None of the control
needs to
be bound at all. They're just there to provide the parameter
necessary to
write information into the tables.

 

[Rob Oldfield]

Sounds like you haven't named the combo correctly. Select it (in design
view of the form), then call up its properties (View, Properties). On the
Other tab there's a Name option which should be set to cboOfficer.

 

[ptlm65]

Great, I got it. but now what does the user do? Do they enter the firs
summons number in the "start" text box and the last summons number i
the "end" box, choose an officer from the combo box and then click th
command button to add all the numbers from start to end into th
summons table?
Also, I changed the combo box name to cboOfficer and now I am getting
new error message unknown method or data member and it highlights th
".prefix" on line 9 of the code below. What am I doing wrong?

Sounds like you haven't named the combo correctly. Select it (i
design
view of the form), then call up its properties (View, Properties). O
the
Other tab there's a Name option which should be set to cboOfficer.

 

[Rob Oldfield]

Yes, you have it right. The user would enter the parameters and then hit
the button to tell it to go. The 'Prefix' thing should be another text
box... in your example it would be the one that you would enter the 05 in
to.

 

[ptlm65]

Ah, now I get it. Do I need to have a field in the "Summons" table fo
the prefix and is it set as a default number "05"? One last thing, s
the three text boxes have no control source? I wanted to make sure tha
I understand that completely. Thanks so much for all this help. You ar
a life saver.

Yes, you have it right. The user would enter the parameters and the
hit
the button to tell it to go. The 'Prefix' thing should be anothe
text
box... in your example it would be the one that you would enter the 0
in
to.

 

[Rob Oldfield]

How you store the numbers is pretty much up to you. I've assumed (in the
sample code) that you'd put the entire thing into a field SummonsNumber by
doing this bit...

rs!SummonsNumber=me.prefix+"-"+cstr(i)

....but it would also be possible to store the two parts of the summons
number (the 05 and the 1) as two separate fields, and just join them back
together again for display purposes. You might want to do that if, for
example, the ability to sort purely by the last numeric part was going to be
useful.

And yes, you're right. No control source required. And you can set (or
choose not to set) the default value of the prefix box to whatever you want.
Again, that would be decided by how you wanted the overall thing to work.
You could, for example, set the default value to be

=Format(Date(),"yy")

if you always it to start out as the year.