Addnew method for similar field name

[abduhsuryadi]

I have table with more then 50 field with similar name: answer1, answer2, answer3, answer4.....answer 50.
i want to make it simple with for next function. but my code doesn't accepted by the VBA. Why?

dim rst as adodb.recordset
set rst=new adodb.recordset
 
for i=1 to 50
    rst.addnew
    rst!answer & i="my answer"&i
next

[zmbd]

i want to make it simple with for next function. but my code doesn't accepted by the VBA. Why?

Very non-specific question there...
You need to actually tell us what it was that you wanted to have happen and then what really happened. Provide in errors with the EXACT error number AND text and if needed, a little example of the data... generic data is just fine, so long as it reflects the actual data one would be using

With that said:

Your database sounds like it is not normalized. Please take a look at the following:
A Tutorial for Access
Database Normalization and Table Structures.

I get this impression because this is what your table sounds like

{tbl_name
[table_primarykey]
[answer1] text(some limit)
[answer2] text(some limit)
[answer3] text(some limit)
[answer4] text(some limit)
(...)
[answer50] text(some limit)}
(note these are not RECORD entries, these are fields)

Your question appears to ask how to add
[answer51] text(some limit)

Your code is an attempt to add a new record:
[table_primarykey]; [answer1]; ...; [answer50]
(some current value); "Some question1"; ...; ""Some question50"
(some current value +1); "Some question1a"; ...; ""Some question50a"
(...)
(some current value +50); "Some question1zz"; ...; ""Some question50zz"

Your missing an .update just before the next at end of the loop too in order to save the record.
(Note: I've not actually vetted the syntax of your code. I've only taken a quick glance as I'm on may way out the door to the lab)


Please work thru the links and provide more information if I have misunderstood your question.

[NeoPa]

Please have the courtesy not to post air-code. This code has certainly not been copied from an Access project as it is not formatted in a way that indicates it even might be. See Before Posting (VBA or SQL) Code, and please understand that we will expect you to comply with these very basic requirements before we will be willing to spend time trying to help you properly.

A very short answer to your why is that the syntax for your code is wrong in a large number of places. These would be found by a simple compile and should not be wasting any human time thinking about them.

[zmbd]

I'm with Neopa on the posted code.

Please go to your vba editor in MS Access
Then go to the Ribbon/toolbar:> Tools :> Options
Editor tab:
Uncheck "Auto Syntax Check" all this does is add an annoying pop-up that says... you goofed... click ok... the line will already turn red when you mis-form the code.
Check Mark "Require Variable Declaration" (this will place the option explicate at the top of every new code module)
checkmark all of the remaining options.
I like an auto indent of 4; however, I wouldn't go less than 3 or more than 5 just as a personal preference.
The remaining stuff can be left as default.

Now go thru all of your form modules, standard modules, and class modules and make sure that the following two lines are the VERY first two lines in every one of these modules

Option Compare Database
Option Explicit

Now compile your project.
Fix any errors
Re-compile your project
Repeat the above three lines until you can compile the project without an error.

As for your posted code:
Line 6 has so many things wrong with it that I should have caught it with a 5 second read. I can only attribute my miss to the several gulps of EggNogg before bed last night (luvs-me-nogg-n-egg) and the 5 hours of sleep... getting old... seems I need at least 6 or so to function now. Amazing that I used to be able to work on 15 minute "cat-naps" in my 20s.

Also line 6 re-affirming that you need to read thru the links I gave you.

[ADezii]

I'm assuming that your Question is how to I Add Data to a Table with sequentially numbered Fields (same Base Name). Assuming you have a Table named tblTest, with 50 Answer Fields sequentially numbered Answer1, Answer2, Answer3...Answer50, then the Logic would be as follows:

Dim rst As ADODB.Recordset
Dim intFldCtr As Integer
 
Set rst = New ADODB.Recordset
 
With rst
  .Source = "tblTest"
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
    .Open
 
  .AddNew
    For intFldCtr = 1 To 50
      .Fields("Answer" & CStr(intFldCtr)) = (intFldCtr ^ 2)
    Next
  .Update
End With
 
rst.Close
Set rst = Nothing