Re: Command Button to modify a table

There are a couple of ways to do that.

One is create a table with a record from 1 to the highest number you could ever need. You can then use this table in an Append query statement to create the desired records. The code will look like this:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
You can mock up a query based on this Counter table, turn it into an Append query (Append on Query menu), and switch to SQL View (View menu) to get an example of the query string you need to create.

The other approach is to write some code to OpenRecordset, AddNew, assign the value, Update, and repeat in a loop until you have created the 43 records. This MakeData() code snippet could serve as an example:
http://allenbrowne.com/ser-39.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"fulller" <fulller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A6FB95A5-4626-4F8E-A7F3-171E80D0179B@xxxxxxxxxxxxxxxx
Thanks Allen. This was a great now. Now my needs have changed a little.

I need to create a form wherein my users can type in a number...for example
"43." Then, this number would be used to create 43 unique codes. I would
like for these codes to be built by using various parts of other field...for
instance the last 2 digits of the year, the first letter of their last name,
ect....whenever someone types in this number they would get 43 different
codes that could then be printed on labels and associated with other
information.

For the start, I'd settle with just being able to generate a certain number
of AutoNumbered codes when the users put in a number. Later I can develop
the codes so that they are built using peices of informaiton from various
fields.

Can you or anyone help with his. Thanks again


"Allen Browne" wrote:

Does "add a numeric value to a field in a table" mean:
a) create a new record, with the value in the field, or
b) increase the value in the field of an existing record in the table?

If (a), your button needs to execute an Append query.
If (b), execute an Update query

For (a) the code would look like this:
Dim strSql As String
strSql = "INSERT INTO MyTable ( MyField ) SELECT 42 AS MeaningOfLife;"
dbEngine(0)(0).Execute strSql, dbFailOnError

For (b), the middle line would be:
strsql = "UPDATE MyTable SET MyField = MyField + 42 WHERE MyTable.ID = 999;"

If you are not sure how to get the query statement right, you can mock up a
query, and then switch it to SQL View (View menu in query design.)

An alternative approach would be to OpenRecordset, and Edit/AddNew followed
by Update.

"fulller" <fulller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7AD9FC97-8FDF-4297-A49C-5131E6D63A5F@xxxxxxxxxxxxxxxx
> Hello,
> I'm a novice with Access. What I am looking to do is create a button
> that,
> when clicked, adds a numeric value to a field in a table. Can anyone > help
> with this?
> Thanks,
> fuller

.