Database to count how many records to create

[paulcraigdainty]

I have a form which a user selects an activity from a combo box. On some occasions a user may need to enter the same activity a number of times. I want to add a text box/combo box that a user can enter or select a number. When the user hits the submit button i want the records created in the database to match the number specied fied in the text/combo boxes.

I think i may need to use an INSERT statement but totally confused how to do this.

 

[krunalprajapati]

U can use DO... WHILE loop

say u enter number in a text box
then u can write the code below on submit button

dim a, b as integer
a = text1

if a > 1 then
b = 1
do
write ur insert statement here
b = b+1
while b> a
endif

 

[Mile-O]

May I ask why you would wish to do this?

 

[paulcraigdainty]

Stressed

I'm struggling with this code. Can somebody tell me where I have gone wrong incorprating the code.



Private Sub cmdSubmit_Click()
Dim a, b As Integer
Dim strsql As String




If IsNull([cboTeam]) Or IsNull([cboName]) Or IsNull([cboActivity]) Then


MsgBox "You have not selected all details, please try again."


Else



If a > 1 Then
b = 1
Do

strsql = "INSERT INTO tblLog (Date, Time, AgentName, Team, Activity, RefNo) " & _
"Values ('" & Me.txtDate & "', '" & _
Me.txtTime & "', '" & _
Me.cboName & "', '" & _
Me.cboTeam & "', '" & _
Me.cboActivity & "', '" & _
Me.txtRefno & "');"
b = b + 1
While b > a



Me.txtBox1 = Me.cboName
Me.txtBox2 = Me.cboTeam
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Me.cboName = Me.txtBox1
Me.cboTeam = Me.txtBox2

cboActivity.Value = Not Null
txtRefno.Value = Not Null
txtRefno.Enabled = False




Wend

End Sub

 

[godofhell]

You forgot to declare the initial value of a as shown here:
dim a, b as integer
a = text1

text1 being the value in yoiur text box

 

[paulcraigdainty]

Code stops Access responding.

I have now declared the varible. When the code is executed it appears at first glance to work (no error msg's are displayed) however Access stop's responding and I have to 'End Task' to recover. I have tested it on other machines so it's not machine specifc. Has anybody any ideas why this may be happening?

Private Sub cmdSubmit_Click()
Dim a, b As Integer
Dim strsql As String
a = txtNum


If IsNull([cboTeam]) Or IsNull([cboName]) Or IsNull([cboActivity]) Then
MsgBox "You have not selected all details, please try again."


Else

If a > 1 Then
b = 1

Do

Loop

strsql = "INSERT INTO tblLog (Date, Time, AgentName, Team, Activity, RefNo) " & _
"Values ('" & Me.txtDate & "', '" & _
Me.txtTime & "', '" & _
Me.cboName & "', '" & _
Me.cboTeam & "', '" & _
Me.cboActivity & "', '" & _
Me.txtRefno & "');"
b = b + 1

While b > a




Me.txtBox1 = Me.cboName
Me.txtBox2 = Me.cboTeam
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Me.cboName = Me.txtBox1
Me.cboTeam = Me.txtBox2

cboActivity.Value = Not Null
txtRefno.Value = Not Null
txtRefno.Enabled = False




Wend

End If
End If
End Sub

 

[godofhell]

Looking at your original code I see your loop is not properly defined. See the new code below.
Assuming you name your text box txtNumbertoCreate, the following code should work:

Private Sub cmdSubmit_Click()
Dim A As Integer
Dim strsql As String

If IsNull(cboTeam) Or IsNull(cboName) Or IsNull(cboActivity) Then
MsgBox "You have not selected all details, please try again."

Else

‘This line converts the value entered on the textbox to interger, necessary in order to meet the defined A as integer criteria.
A = Cint(txtNumbertoCreate.value)
‘Checks to see if the converted value is greater than Zero
If A > 0 Then
'Start the loop and continue until the value of A = 0
Do Until A = 0

‘This section was left unchanged, make sure to test it separately to ensure the statement is valid.
strsql = "INSERT INTO tblLog (Date, Time, AgentName, Team, Activity, RefNo) " & _
"Values ('" & Me.txtDate & "', '" & _
Me.txtTime & "', '" & _
Me.cboName & "', '" & _
Me.cboTeam & "', '" & _
Me.cboActivity & "', '" & _
Me.txtRefno & "');"

Me.txtBox1 = Me.cboName
Me.txtBox2 = Me.cboTeam
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Me.cboName = Me.txtBox1
Me.cboTeam = Me.txtBox2

cboActivity.Value = Not Null
txtRefno.Value = Not Null
txtRefno.Enabled = False
'End of un changed section

’Subtracts 1 from the original value of A and loops
A = A - 1
Loop

End If
End If

End Sub