Button That Adds As Many Records As You Want

[Kriis]

I want to create a button that adds as many records to a table as you want. So I would have
- a text field where the user would input the number of records
- the code of the button having a for loop or while statement to add that many records as the user typed in

This is my code of a button that adds one record at a time. My VB is not good so could someone tell me how to put in a for loop or while statement that would do the trick? Thanks.

Private Sub addbutton_Click()

If IsNull(Combo0) Then
    MsgBox "Please select an Oil to add data (Top left corner)"
Else
 Me![Property Subform].Form.AllowAdditions = True
 Forms![Values Form]![Property Subform].SetFocus

 DoCmd.GoToRecord , , acNewRec
 Me![Property Subform].Form![TV_O_ID] = Combo88
 Me![Property Subform].Form![T_TT_ID] = Combo86
 'Weathering Info
 Me![Property Subform].Form![WEATHERTYPE] = Text106
 Me![Property Subform].Form![WEATHERUNIT] = Combo104
 Me![Property Subform].Form![WEATHER%Num] = Combo102
 Me![Property Subform].Form![WEATHER%Text] = Combo100
 'Parameter Info
 Me![Property Subform].Form![PARAMTYPE] = Combo114
 Me![Property Subform].Form![PARAMUNIT] = Combo120
 Me![Property Subform].Form![PARAMVALNum] = Combo112
 Me![Property Subform].Form![PARAMVALText] = Combo110
 Me![Property Subform].Form.AllowAdditions = False

 End If
    
Me![Property Subform].Requery

End Sub

 

[Jibbadiah]

Think that you are better off leaving the detail on a form and giving the user a submit button.
They change the parts that they need to update and then click submit.
The submit button just sends one record at a time to the table based on the form details.
The detail stays on the form if they want to repeat it for the next record.
You shouldn't add loads of duplicate records to a table just to update them later... it doesn't sound like it would serve any useful purpose?!

 

[Kriis]

Please, if you know how to help, please do so. I wouldn't be asking if it wasn't for a useful purpose.

 

[Jibbadiah]

Sorry Kriis, thought I was helping?!

- Create a copy of your existing form.
- Make all of the controls on the form unbound (combo boxes and text boxes)
- Add a command button with the word submit.
- Add some code to the on-click event of the command button that runs a sql statement that inserts the values on the form to the required table.
The code would look something like the following:
dim strSQL as string
strSQL = "insert into table (field1, field2) VALUES ('" & me.control1 & "',' " & me.control2 & "')"
docmd.runsql(strSQL)

If you use this technique users can essentially reuse existing selections/entries if they remain unchanged. I presume that this is kind of what you are after?

 

[Kriis]

That's essentially what it does already. My combo boxes are all unbound.

What I need is when the user clicks 'Add Record', instead of adding the record one at a time, it would add lets say 30 of the same record.

Any clue how to modify my existing code? IE. Where to add a while loop?

 

[Jibbadiah]

Kriis - that is my whole point... WHY?!

A table is pointless if it contains 30 instances of exactly the same record.

You could add a single field with the value 30... then you don't need to copy the actual record 30 times?

Can you explain why you want to do this... it doesn't make any sense to me?!

 

[Kriis]

Can anyone tell me if this is possible and how to do it?

Thanks.

 

[Jibbadiah]

You're not very friendly or forthcoming are you!!

I don't know why, but I have questioned enough... if someone asks a question in this forum it is generally because they are trying to understand your problem and help in the best way possible.

You seem to want something like the following...

dim counter as integer
dim strSQL as string

counter = 0

Do While count < Me.FormControlThatHasNumber

strSQL = "insert into table ...etc..."
docmd.runsql(strSQL)

counter = counter + 1

.MoveNext
Loop

 

[Kriis]

You're not very friendly or forthcoming are you!!

I don't know why, but I have questioned enough... if someone asks a question in this forum it is generally because they are trying to understand your problem and help in the best way possible.

You seem to want something like the following...

dim counter as integer
dim strSQL as string

counter = 0

Do While count < Me.FormControlThatHasNumber

strSQL = "insert into table ...etc..."
docmd.runsql(strSQL)

counter = counter + 1

.MoveNext
Loop

Sorry if I came off unfriendly. It's just the frustration talking. It's a long story to explain why I need this done, but I do.

I'll give your code a shot. Thanks.

 

[Kriis]

You have to put a recordset infront on the movenext method.

There is a problem with

.MoveNext.....I think you have to put something infront of it.

 

[Kriis]

Ok I figured that part out...but it gives me the following pop up:

"You can't go to the specified record"