Command Button press multiple times

[Symon]

Hi All,

Is there any way to press a command button a certain amount of times based on a value in a text box.

i.e.

text box value = 5

button gets pressed 5 times with one click

Thanks

Symon

 

[Uncle Gizmo]

You could probably write some VBA code that would give you this facility but the real question is why? If you can answer that, then there may be some better way to achieve the result you are trying to achieve.

 

[Symon]

You could probably write some VBA code that would give you this facility but the real question is why? If you can answer that, then there may be some better way to achieve the result you are trying to achieve.

Thanks for your reply Uncle Gizmo,

I need to generate duplicates of the same record, and sometimes this could be up to 100 times...
This is just a way to get round my last post: "Creating Multiple Records using button and text box to say how many to create"

I have changed the serial number to auto number and removed the serial number prefix (AD-Oracle-) and placed this into another field. which would create the next serial number automatically.

 

[Uncle Gizmo]

Did you achieve this in your last post using a text box to provide the number?

 

[Symon]

No, Unfortunately this is a little too complex for me.

I am now using a duplicate button, with the serial number now changed to auto number. so just need to get over the duplicate 'X' amount of times with a click of a button.

As said in my last thread, it would be great to have a message box stating the first and last number that has just been created, but this is not a necessity.

 

[Uncle Gizmo]

Assuming you have the necessary code to create a duplicate record then you need to place this code inside a for next loop and run it for the number of times you want.

 

[Symon]

The duplicate button is running using a macro...

How would I go about running a loop based on a value in a text box?

 

[Uncle Gizmo]

Sorry I can't help you with macros, I know very little about them as I always convert them to VBA.

 

[Symon]

here is the VB for the duplicate button...

Could this be looped based on a value put in a textbox?

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click_Err

    On Error Resume Next
    DoCmd.RunCommand acCmdSelectRecord
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdCopy
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdSelectRecord
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdPaste
    End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


AddRecord_Click_Exit:
    Exit Sub

AddRecord_Click_Err:
    MsgBox Error$
    Resume AddRecord_Click_Exit

End Sub

 

[Uncle Gizmo]

I'm not on a PC, so this might need some love,

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click_Err

Dim x As Integer 
        For x = 0 To 4
    
    DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdCopy
        DoCmd.RunCommand acCmdRecordsGoToNew
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdPaste

Next x 

AddRecord_Click_Exit:
    Exit Sub

AddRecord_Click_Err:
    MsgBox Error$
    Resume AddRecord_Click_Exit

End Sub

 

[Symon]

Thanks Unlce Gizmo,

Ill give it a go...

 

[Uncle Gizmo]

Somehow I got dim x as int in there twice! I will edit it...

 

[Uncle Gizmo]

Right, I've corrected my typo!

 

[Symon]

Thanks,

Works a treat...

Made 5 new records with one click...

Just need to get it to run the amount of times stated in a text box now.

 

[Uncle Gizmo]

If you look at the code you should see that it iterates through the for next loop from 0 to 4 giving you 5 iterations.

It would make more sense in your situation to change that code from 0 to 4 to 1 to 5.

 

[Uncle Gizmo]

Now if you add a text box to your form, let's call it txtCopies. Open the text box property sheet and update its default value to 1.

 

[Uncle Gizmo]

Now you need to feed the value of that text box into the code.

 

[Symon]

Brilliant,

Cant thanks you enough for your help today Uncle Gizmo.

Here is the finished working VB

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click_Err

Dim x As Integer
        For x = 1 To (Me.Amount.Value - 1)
    
    DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdCopy
        DoCmd.RunCommand acCmdRecordsGoToNew
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdPaste

Next x

AddRecord_Click_Exit:
    Exit Sub

AddRecord_Click_Err:
    MsgBox Error$
    Resume AddRecord_Click_Exit

End Sub

 

[Uncle Gizmo]

That's good, you've got it!

 

[Uncle Gizmo]

I would recommend you change your textbox name from Amount to txtAmount for two reasons. If you use a real word like "Amount" it could turn out to be a reserved word, one used by MS Access itself and this could cause you no end of problems. By adding the prefix you totally eliminate this possibility. Do the same for all of your controls, you can use one of the recommended naming schemes or you can use your own scheme.

The second reason is when you are reading through your VBA code you immediately know the nature of the control and it can help you spot problems quickly and easily.