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

 

[Imhoteb]

Hi,

The double quotes make me!record and text1 simple text and not the values in the record itself

&quot;Me!Record&quot; & loopcount & &quot;text1&quot;

you should write something like this

Me!Record & &quot; &quot; & Loopcount & &quot; &quot; & Text1

Hope this helps
Good luck

 

[ASQ]

I just tried that and it errored out.

Varx2 = Me!Record & &quot; &quot; & Loopcount & &quot; &quot; & Text1

It said that It could not find Record (Me!record).

What I have is a long list of fields on my form called
record1text1
record2text1
record3text1
...
record80text1

And I don't want to type out the update code for all 80 records. There has to be a way to set a counter value , loop, and add it to a string. This will create the records, but access will not read the new string name as Me!recordXtext1 but takes it at face value &quot;Me!recordxtext1&quot;?
Sorry if I am confusing you?

 

[Imhoteb]

did you try to let the space (&quot; &quot out of the string?

Me!Record & Loopcount & Text1

 

[kevinclark]

I don't know if I am even close but perhaps this may help.



'In my example I have created a form with 5 textbox controls. I have named
'each textbox by concatenating the word &quot;Address&quot; with a number that will
'correspond to a field ID with in my table.
'For example
' TEXTBOXES FIELDS
' Address0 Field(0) = FirstName
' Address1 Field(1) = LastName
' Address2 Field(2) = StreetAddress
' Address3 Field(3) = City
' Address4 Field(4) = Country

'If I only wish to add data to a few fields, say 5 out of 30 then I only
'need 5 TextBoxes on my form. I need to name each Textbox with a common
'name concatenated with the Index of the field it is to supply data to.



Private Sub Command10_Click()
Dim dbTest As DAO.Database
Dim rst As DAO.Recordset
Dim ctl As Control
Dim ctlName As String 'example &quot;Address&quot;
Dim ctlID As String 'example 2
Dim curCtl As String 'Concatenate(ctlName & ctlID)


ctlName = &quot;Address&quot; 'Change this to suite
Set dbTest = CurrentDb
Set rst = dbTest.OpenRecordset(&quot;Addresses&quot


rst.AddNew
'here are the loops
'Looks at each Control on the form
For Each ctl In Me.Controls
'If the Control is a Textbox
If ctl.ControlType = acTextBox Then
'And if the name of the TextBox is like the ctlName value
If ctl.Properties(&quot;Name&quot Like ctlName & &quot;*&quot; Then
'Then get the ctlID that matches the textbox to a field
ctlID = Right(ctl.Properties(&quot;Name&quot, Len(ctl.Properties(&quot;Name&quot) - Len(ctlName))
curCtl = ctlName & ctlID
'Give focus to the control
Me.Controls(curCtl).SetFocus
'populate the field
rst.Fields(CInt(ctlID)).Value = ctl.Properties(&quot;text&quot
End If
End If
Next 'Loop to the next Control
rst.Update

End Sub