Hi Steve,
you're welcome
"I'm afraid that I haven't tried SQL"
this is not SQL (Structured Query Language), it is VBA (Visual Basic for
Applications) <smile> ...
To run this subroutine, I am assuming you will:
have a form with a textbox (so the user can fill in how many records to
create) and a command button to create the record. the code I gave you
would go into a general (standard) module*. Then, in the [Event
Procedure] code behind the form, you would:
1. make sure the textbox for how many records is filled out
2. call the code to create the records.
Assuming your textbox has the Name txtHowMany, here would be the code to
put on the Click event of the command button:
'~~~~~~~~~~~~~
if isnull(me.txtHowMany) then
me.txtHowMany.SetFocus
msgbox "You must specify how many records to create" _
,, "Need number of records to create"
exit sub
end if
CreateNewRecords me.txtHowMany
msgbox "Done"
'~~~~~~~~~~~~~
what is the purpose of creating these records automatically? So they
will show up in a form or subform so you can fill the rest of the
information? Will there be a foreign key that also needs to be filled?
for better understanding of the basics of Access, read this:
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
after you are done reading, then post back with questions...
*** How to Create a Standard (General) Module***
1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in
once the code is in the module sheet, from the menu, do -->
Debug,Compile
if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>
Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc
Warm Regards,
Crystal
*
have an awesome day
*
Crystal,
Thanks for your response. It looks promising but I'm afraid that I haven't
tried SQL (I know how to view SQL window) and just use query grids and
macros. I substituted in my tablename & fields and then pasted the code into
a module. But now I'm lost. e.g. Sub CreateNewRecords(pHowMany as integer);
does the text betwn () remain?
Help? Tks.
mname=Steve
strive4peace said:
Hi datasam (what is your name?)
It would be best to create the new records with VBA code
I will assume you want the new series to begin with the next hundred
'~~~~~~~~~~~~~~
Sub CreateNewRecords(pHowMany as integer)
dim mLastNum as long _
, mNextNum as long _
, mHundreds as long _
, i as integer _
, strSQL as string
mLastNum = nz(dMax("[Fieldname]","[Tablename]"),0)
mHundreds = mLastNum\100
mNextNum = (mHundreds + 1) * 100 + 1
for i = 1 to pHowMany
strSQL = "INSERT INTO [Tablename] " _
& " ([Fieldname]) " _
& " SELECT " & mNextNum & ";"
'remove next statement after debugged
Debug.print strSQL
currentdb.execute strSQL
mNextNum = mNextNum + 1
next i
End Sub
'~~~~~~~~~~~~~~
WHERE
Fieldname is the name of the field containing your field value
Tablename is the name of the table
** debug.print ***
debug.print strSQL
--> this prints a copy of the SQL statement to the debug window (CTRL-G)
After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement
If the SQL statement has an error
1. Make a new query (design view)
2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window
(select, CTRL-X)
4. paste into the SQL window of the Query
(CTRL-V)
5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
I need to assign new records a [FieldValue] something like Max[FieldValue]
+1; Except, the next step must begin with the nearest new series. For
example, if Max[FieldValue]=589, then new records should take on values like
[FieldValue]=601, then 602, and so on. The new records are added in groups
via an append query, so I know the Max[]. Tks for any help.
