Quotes and Syntax

[ASQ]

Need help with the following. I am trying to make a loop and make it add record1, record2… to 8. It counts fine but it is having issues (like me) updating a DB. I need help with correct syntax.

Do While not loopcount < 9

Varx1 = “rst.Record” & loopcount & “text1”
Varx2 = “Me!Record” & loopcount & “text1”

rst.AddNew
Varx1 = Varx2
rst.Update
'instead of passing the value of the form Me!record1text it
' pass's the vale &quot;Me!record1text&quot;, which does not help me?

Loopcount = loopcount + 1
Loop

It adds 8 records great but with no data?
Thanks

 

[ToeShot]

Your code is wrong. But are you trying to add one piece of data to a knew record or add 8 pieces of data to a knew record?

If I knew exactly what your are trying to acheive I could help you

 

[ASQ]

I have a form that has 20 rows of fields.
In each row there is 18 fields

When they save a record I would like it to update a table. Did I mention all of this is unbound? I would like to create an update event that would work with a loop and a counter.

In the first row, first field = row1field1
In the second row, first field = row2field1


dim loopxx, update1,update2

So i created -- (I tested the code when it was hard coded and it works great for the first row, But I don't want to repeat the code 19 more times with the only changes being row1 to row2. Below is the loop part, I left the rest off, if you need it I can paste it in?)

loopxx = 1
do while loop < 21
update1 = &quot;Me!row&quot; & loopxx & &quot;field1&quot;
update2 = &quot;rst.field1&quot;

'adds the form field value of me!row1field1 to the table field1
'(which produces rst.field1 = Me!row1field1)
update2 = update1

rst.update
rst = nothing

loopxx = loopxx + 1
loop

But what happens, instead of getting the value in formX,row1field.value it pass this in quotes &quot;rst.field1 = Me!row1field1&quot;.

So do you know if it can be done.
(Can I make a fuction and pass the fuction the creted values ie the &quot;Me!row1field1&quot; )

Any help will be greatly appreciated

 

[ToeShot]

The Code Below will give you what your are looking for.
Notes:
1) You need to have the table with all the required fields
2) All data types must match (i.e. table field 1 data type is date then form!form_name!text_box1_name must be date
3) You can use Me.Text_Box1_Name instead of the Form![Form_Name]![Text_Box1_Name] if you put this code behind the form

Dim rst As DAO.Recordset
Dim intRecCount As Integer

Set rst = CurrentDb.OpenRecordset(&quot;name of table&quot;, dbOpenDynaSet)

For intRecCount = 1 to 20
rst.AddNew

With rst
![Field1_Name] = Form![Form_Name]![Text_Box1_Name].Value
![Field2_Name] = Form![Form_Name]![Text_Box2_Name].Value
![Field3_Name] = Form![Form_Name]![Text_Box3_Name].Value
![Field4_Name] = Form![Form_Name]![Text_Box4_Name].Value
![Field5_Name] = Form![Form_Name]![Text_Box5_Name].Value
![Field6_Name] = Form![Form_Name]![Text_Box6_Name].Value
![Field7_Name] = Form![Form_Name]![Text_Box7_Name].Value
![Field8_Name] = Form![Form_Name]![Text_Box8_Name].Value
![Field9_Name] = Form![Form_Name]![Text_Box9_Name].Value
![Field10_Name] = Form![Form_Name]![Text_Box10_Name].Value
![Field11_Name] = Form![Form_Name]![Text_Box11_Name].Value
![Field12_Name] = Form![Form_Name]![Text_Box12_Name].Value
![Field13_Name] = Form![Form_Name]![Text_Box13_Name].Value
![Field14_Name] = Form![Form_Name]![Text_Box14_Name].Value
![Field15_Name] = Form![Form_Name]![Text_Box15_Name].Value
![Field16_Name] = Form![Form_Name]![Text_Box16_Name].Value
![Field17_Name] = Form![Form_Name]![Text_Box17_Name].Value
![Field18_Name] = Form![Form_Name]![Text_Box18_Name].Value
End With

rst.Update

Next intRecCount

rst.Close
Set rst = Nothing

HTH

 

[ASQ]

You are close but I need to exsplain myself some more. I have the above already(yours is cleaner) and it will work for the first row. Think of it this way. You have a a grid of 18 rows and 20 colums. You can use the above code for the first row but you can't use it for the second, the form names are wrong. Because all of the rows are on one form, hard coded (stupid but it has to be this way for another reason) so what I am tring to accomplish , using you code is to have the form names change using your intreccount as the row indicator.

For intRecCount = 1 to 20
rst.AddNew

With rst
![Field1_Name] = Form![Form_Name]![Text_Box1_Name]&[intRecCount].Value
![Field2_Name] = Form![Form_Name]![Text_Box2_Name]&[intRecCount].Value
![Field3_Name] = Form![Form_Name]![Text_Box3_Name]&[intRecCount].Value
![Field4_Name] = Form![Form_Name]![Text_Box4_Name]&[intRecCount].Value
![Field5_Name] = Form![Form_Name]![Text_Box5_Name]&[intRecCount].Value
![Field6_Name] = Form![Form_Name]![Text_Box6_Name]&[intRecCount].Value
![Field7_Name] = Form![Form_Name]![Text_Box7_Name]&[intRecCount].Value
![Field8_Name] = Form![Form_Name]![Text_Box8_Name]&[intRecCount].Value
![Field9_Name] = Form![Form_Name]![Text_Box9_Name]&[intRecCount].Value
![Field10_Name] = Form![Form_Name]![Text_Box10_Name]&[intRecCount].Value
![Field11_Name] = Form![Form_Name]![Text_Box11_Name]&[intRecCount].Value
![Field12_Name] = Form![Form_Name]![Text_Box12_Name]&[intRecCount].Value
![Field13_Name] = Form![Form_Name]![Text_Box13_Name]&[intRecCount].Value
![Field14_Name] = Form![Form_Name]![Text_Box14_Name]&[intRecCount].Value
![Field15_Name] = Form![Form_Name]![Text_Box15_Name]&[intRecCount].Value
![Field16_Name] = Form![Form_Name]![Text_Box16_Name]&[intRecCount].Value
![Field17_Name] = Form![Form_Name]![Text_Box17_Name]&[intRecCount].Value
![Field18_Name] = Form![Form_Name]![Text_Box18_Name]&[intRecCount].Value
End With

rst.Update

Next intRecCount

So what it did was Text_Box18_Name1 or field and row 1

I named all of the form text boxes + Row , thinking I could add the row number on the end and loop it to update row1,row2...

Thank again

 

[ToeShot]

Are you using a continuous form? or is All your data in one Row.
Sorry I am not understanding what you are trying to get across to me