Dim problem

F

Fred

I have the following Loop.
The [Test] is a field with numbers in the field
1 to 20.

The txtTest are fields on a form
txtTest1, txtTest2, txtTest3 upto txtTest20.

The "Y" is just text. I am just using y in place
of information I can't post.


The problem I have is I get a dbug that points to
the txtTesti = "Y" line.
I don't know from here what to do.


Private Sub Counter_Click()
Dim i As Integer
Dim tot As Integer
For i = 1 To [Test]
txtTesti = "Y"
Next i
Me.txtTotal = tot

End Sub
 
O

Ofer

Try that
Private Sub Counter_Click()
Dim i As Integer
Dim tot As Integer
For i = 1 To [Test]
me("txtTest" & i) = "Y"
Next i
Me.txtTotal = tot

End Sub
 
F

Fred

Fred said:
I have the following Loop.
The [Test] is a field with numbers in the field
1 to 20.

The txtTest are fields on a form
txtTest1, txtTest2, txtTest3 upto txtTest20.

The "Y" is just text. I am just using y in place
of information I can't post.


The problem I have is I get a dbug that points to
the txtTesti = "Y" line.
I don't know from here what to do.


Private Sub Counter_Click()
Dim i As Integer
Dim tot As Integer
For i = 1 To [Test]
txtTesti = "Y"
Next i
Me.txtTotal = tot

End Sub
Click to expand...






I have no response to my question above. Maybe
this will help make it clearer.
I would like to to know how to loop 10 times through
10 fields and and be able to do calculations on those
fields. I don't know how to make the i variable work
with a field name as in "Fieldi". The i being a Dim
variable. I should be able to loop through Field1 to Field10.
I tried brackets and parenthesis and I get the error at
the Fieldi line.
Is there another way to loop and use a variable?
I would like help on this problem.



Private Sub Counter_Click()
 Dim i As Integer
 Dim tot As Integer
 For i = 1 To 10
 [Fieldi] = "Y"
 Next i
Me.txtTotal = tot

End Sub
 
G

Gary Walter

Hi Fred,

I don't understand "Y" (nor "tot"),
but if I had a *non-continuous* form
whose recordsource included 10 fields
whose fieldnames were Field1, Field2, etc.,
one way to Debug.Print the value of those
fields when a command button "Counter"
was clicked on, might be (untested):

Private Sub Counter_Click()
Dim i As Integer
Dim strFieldName As String
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 10
strFieldName = "Field" & i
Debug.Print .Fields(strFieldName)
Next i

End With
End Sub

to total them and assign to me!txtTotal:

Private Sub Counter_Click()
Dim i As Integer
Dim tot As Long
Dim strFieldName As String
tot = 0
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 10
strFieldName = "Field" & i
tot = tot + .Fields(strFieldName)
Next i
End With
Me!txtTotal = tot
End Sub

Fred said:
Fred said:
I have the following Loop.
The [Test] is a field with numbers in the field
1 to 20.

The txtTest are fields on a form
txtTest1, txtTest2, txtTest3 upto txtTest20.

The "Y" is just text. I am just using y in place
of information I can't post.


The problem I have is I get a dbug that points to
the txtTesti = "Y" line.
I don't know from here what to do.


Private Sub Counter_Click()
Dim i As Integer
Dim tot As Integer
For i = 1 To [Test]
txtTesti = "Y"
Next i
Me.txtTotal = tot

End Sub
Click to expand...






I have no response to my question above. Maybe
this will help make it clearer.
I would like to to know how to loop 10 times through
10 fields and and be able to do calculations on those
fields. I don't know how to make the i variable work
with a field name as in "Fieldi". The i being a Dim
variable. I should be able to loop through Field1 to Field10.
I tried brackets and parenthesis and I get the error at
the Fieldi line.
Is there another way to loop and use a variable?
I would like help on this problem.



Private Sub Counter_Click()
Dim i As Integer
Dim tot As Integer
For i = 1 To 10
[Fieldi] = "Y"
Next i
Me.txtTotal = tot

End Sub
Click to expand...
 
F

Fred

Gary said:
Hi Fred,

I don't understand "Y" (nor "tot"),
but if I had a *non-continuous* form
whose recordsource included 10 fields
whose fieldnames were Field1, Field2, etc.,
one way to Debug.Print the value of those
fields when a command button "Counter"
was clicked on, might be (untested):

Private Sub Counter_Click()
Dim i As Integer
Dim strFieldName As String
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 10
strFieldName = "Field" & i
Click to expand...


I got a Compile error at the strFieldName = "Field" & i line.
It is a syntex error
I removed the quotes from the Field and it past it.




Debug.Print .Fields(strFieldName)
Next i
Click to expand...

I got a Compile error here at Next i
It is a sub or function not defined.

I don't know what to do from this point.
 
G

Gary Walter

Hi Fred,

Your first Compile error
doesn't make sense to me
unless you need to cast i
as a string (but I don't know why
you would have to) ....nothing further
will work in the code if you
remove quotes around Field!

Does this *exact* code work?
(copy and paste so no typos)

Private Sub Counter_Click()
Dim i As Integer
Dim strFieldName As String
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 10
strFieldName = "Field" & CStr(i)
On Error Resume Next
Debug.Print .Fields(strFieldName)
Next i

End With
End Sub

What do you get in immediate window
from Debug.Print?

If the recordsource of your form
does not contain fields whose names
are "Field1","Field2", etc, then the above
isn't going to work obviously.

If you have added other code and it does
not "work," then it would make sense to
copy and paste your entire code into a
reply message, along with exact recordsource
of form and all field names of the recordsource.

gary
 
F

Fred

Gary said:
Hi Fred,

Your first Compile error
doesn't make sense to me
unless you need to cast i
as a string (but I don't know why
you would have to) ....nothing further
will work in the code if you
remove quotes around Field!

Does this *exact* code work?
(copy and paste so no typos)

Private Sub Counter_Click()
Dim i As Integer
Dim strFieldName As String
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 10
strFieldName = "Field" & CStr(i)
On Error Resume Next
Debug.Print .Fields(strFieldName)
Next i

End With
End Sub

What do you get in immediate window
from Debug.Print?

If the recordsource of your form
does not contain fields whose names
are "Field1","Field2", etc, then the above
isn't going to work obviously.

If you have added other code and it does
not "work," then it would make sense to
copy and paste your entire code into a
reply message, along with exact recordsource
of form and all field names of the recordsource.

gary
Click to expand...





Ok. I will do as you say.
Be back later. Thank You.
 
F

Fred

Fred said:
Ok. I will do as you say.
Be back later. Thank You.
Click to expand...






It is not working.
I get a compile error: Syntax error at the following line:

strFieldName = "Field" & CStr(i)



I made a new database file with a new table1, query1, and form.
I put field1 to field10 in the table1 and one record of data.
I made the record source table1 in the form and tried it and then I made the
record source the query1 and it failed to.
I don't see what could be causing the syntax error.

There is no other code that I can see that would be in the way.
 
G

Gary Walter

It is not working.
I get a compile error: Syntax error at the following line:

strFieldName = "Field" & CStr(i)



I made a new database file with a new table1, query1, and form.
I put field1 to field10 in the table1 and one record of data.
I made the record source table1 in the form and tried it and then I made
the
record source the query1 and it failed to.
I don't see what could be causing the syntax error.

There is no other code that I can see that would be in the way.
Click to expand...

Hi Fred,

I set up the same thing yesterday morning
to make sure I wasn't failing to see something,
and ended up zipping it to you at your Yahoo
account. Did you get it (and try it)?

It worked great here.

Do you want me to send to some other account?

good luck,

gary
 
F

Fred

Gary said:
Hi Fred,

I set up the same thing yesterday morning
to make sure I wasn't failing to see something,
and ended up zipping it to you at your Yahoo
account. Did you get it (and try it)?

It worked great here.

Do you want me to send to some other account?

good luck,

gary
Click to expand...


Cool. It is working. Now I have a dim that can loop. Thanks.

One more question.

For i = 1 to 10

Is there away to be able to choose the fields?

For i = 1 to [Field5]

or For i = 1 to "Field5"


I tried with both and it debugs on the For line.
 
F

Fred

Fred said:
Gary said:
Hi Fred,

I set up the same thing yesterday morning
to make sure I wasn't failing to see something,
and ended up zipping it to you at your Yahoo
account. Did you get it (and try it)?

It worked great here.

Do you want me to send to some other account?

good luck,

gary
Click to expand...


Cool. It is working. Now I have a dim that can loop. Thanks.

One more question.

For i = 1 to 10

Is there away to be able to choose the fields?

For i = 1 to [Field5]

or For i = 1 to "Field5"


I tried with both and it debugs on the For line.
Click to expand...



I know the answer to the above. Just change the 10 to whatever.
It will do the same instead of using a Field name.
 
F

Fred

Fred said:
Fred said:
Gary said:
It is not working.
I get a compile error: Syntax error at the following line:

strFieldName = "Field" & CStr(i)



I made a new database file with a new table1, query1, and form.
I put field1 to field10 in the table1 and one record of data.
I made the record source table1 in the form and tried it and then I
made the
record source the query1 and it failed to.
I don't see what could be causing the syntax error.

There is no other code that I can see that would be in the way.

Hi Fred,

I set up the same thing yesterday morning
to make sure I wasn't failing to see something,
and ended up zipping it to you at your Yahoo
account. Did you get it (and try it)?

It worked great here.

Do you want me to send to some other account?

good luck,

gary
Click to expand...


Cool. It is working. Now I have a dim that can loop. Thanks.

One more question.

For i = 1 to 10

Is there away to be able to choose the fields?

For i = 1 to [Field5]

or For i = 1 to "Field5"


I tried with both and it debugs on the For line.
Click to expand...



I know the answer to the above. Just change the 10 to whatever.
It will do the same instead of using a Field name.
Click to expand...



I need help one more time.



Dim strDate As String
With Me.Recordset
.Bookmark = Me.Bookmark

For i = 1 To 31
strFieldName = "Field" & i
strDate = Format(Date, "mm/ i /yy")


strDate = Format(Date, "mm/ i /yy") fails and goes to debug.

I tried i with this formated current date and I get 6/i/05

Is it possible to get 6/1/05, 6/2/05, and so on using the dim variable?
 
Top