Add a New Record based on Inputbox with Loop

[Only4Access]

Hello Smart Users,

I am a newbie in access. Still trying to learn access.

I have a table with 2 field named tblNumber. ID (PK, Autonumber) Number (Number, Long Integer)

I have a form (frmMy) with a text box (text1) default_value used as DMAX to get MAX value of tblNumber =Nz(DMax("Number","tblNumber"),0) & one command button

This is what i would like to achieve, when i click on button;

1. Pop up an Input Box asking for "How many records want to insert?"
2. Check the input box response, if it >0 then
3. Insert text1+response numbers in table tblNumber. Of Course need to check in tblNumber before add records for duplicates. SImply can exclude duplicates & insert only non duplicates numbers.
4. In a another text box ( I can create in this form, ie text2) showing, "The following Accounts are created"

eg:

let's say MAX value in text1 is 1000, when prompt for input box, I entered 5



So in tblNumber need to INSERT new records as 1001, 1002, 1003, 1004, 1005 & text2 showing "The following Accounts are created: 1001, 1002, 1003, 1004, 1005")

Also, how can i force text1 to update when i open form frmMy?

I really appreciate any help.

[June7]

Instead of popup box, suggest a textbox on form. Use Validation Rule to assure the input is a number and/or use code to check value.

Something like:

Dim i As Integer
Dim strNumbers As String
Dim intNumber As Integer
intNumber = Nz(DMax("Number","tblNumber"),0)
For i = 1 to Me.text1
intNumber = intNumber + 1
CurrentDb.Execute "INSERT INTO tblNumber(Number) VALUES(" & intNumber & ")"
strNumbers = strNumbers & intNumber & ',"
Next
Me.text2 = strNumbers

Why would there be duplicates?

Is Text1 bound to a field? It should not be. Use DMax expression in ControlSource property.

[Only4Access]

Thank you June7 for your reply.

Please disregard about duplicates. Sorry for that. As you said, I have created text3 for input.

When I click on button, "Run-time error '3134'", syntax error in INSERT INTO.

This is what i have;

Private Sub cmdClick_Click()
Dim i As Integer
Dim strNumbers As String
Dim intNumber As Integer
intNumber = Nz(DMax("Number", "tblNumber"), 0)
For i = 1 To Me.Text3
intNumber = intNumber + 1
CurrentDb.Execute "INSERT INTO tblNumber(Number) VALUES(" & intNumber & ")"
strNumbers = strNumbers & intNumber & ","
Next
Me.Text2 = strNumbers
End Sub

Thank you for your help.

[June7]

The syntax is correct, it works for me. I tested with a Long Integer field. Something else is the real issue. Maybe the DMax. Test with a hard-coded value in place of the DMax and see what happens.

Review link at bottom of my post for debugging guidelines. Step debug. Follow the code as it executes. Do the variables get populated as expected?

[Missinglinq]

Everything else aside, I have to wonder about a Field named 'Number,' which is a Reserved Word in Access. Your say your code is choking on the line

CurrentDb.Execute "INSERT INTO tblNumber(Number) VALUES(" & intNumber & ")"

and that Field name may be the choking point. The name needs to be changed, regardless.

Linq ;0)>

[June7]

I wondered if it might be a reserved word but creating the field doesn't trigger message like using Name does. I thought I successfully tested with Number as field name but just tried again and it does fail. Enclose in [] then it does work.


CurrentDb.Execute "INSERT INTO tblNumber([Number]) VALUES(" & intNumber & ")"

[Only4Access]

Sorry for the late response.

Thank you June for your help, as well Linq letting about reserved word.

I changed integer to Long in Dim intNumber As Integer to deal with large numbers. When I tested As Integer; with numbers >40000 shows overflow error.

Thanks again for all your help.