creating loop

[akrylik]

Im trying to create a loop that searches through a table and increments a variable every time it finds a record with a specific field set to a specific value. Below is the code but its not working. I then use the variable for another field. can you see anything wrong? This is used in a form btw.

Dim counter As Integer
counter = 0
Set rst = CurrentDb.OpenRecordset("TableName")
Do Until rst.EOF
If rst(10) = "True" Then
counter = counter + 1
End If

rst.MoveNext
Loop


Me.Text24 = counter

[pbaldy]

It's not how I old do it, but it should work. If that's a yes/no field you wouldn't want unites around True.

[akrylik]

the field is a combo box w/ a value list with values "True","False","Undefined". How would you have done it?

[pbaldy]

What does "not working" mean then?

It is very inefficient to loop through an entire table to get a count. One option would be a DCount with the appropriate criteria:

DLookup Usage Samples

To use a recordset, I'd use an SQL statement that did the count:

Set rst = CurrentDb.OpenRecordset("SELECT Count(*) As HowMany FROM TableName WHERE FieldName = 'True'")

Me.Text24 = rst!HowMany

[akrylik]

that sql statement worked perfectly, but when i tried this:
Set rst = CurrentDb.OpenRecordset("SELECT Count(*) As HowMany FROM TableName WHERE Last_Name = me.txtLastName and Headcount = 'True'")

it gave me this error : run-time error '3061' : Too few parameters. expected 2.

me.txtLastName is a combo box in a form. i am trying to count how many records in the table that has the last name that is in the form. did i enter the fields wrong?

[pbaldy]

You have to concatenate the last name:

Set rst = CurrentDb.OpenRecordset("SELECT Count(*) As HowMany FROM TableName WHERE Last_Name = '" & me.txtLastName & "' and Headcount = 'True'")

[akrylik]

i tried doing it but i had it coded wrong, forgot the " " . Ill give that a try and let you know.

[akrylik]

its still throwing me an error. but now its saying expected 1 instead of 2. i even tried deleting what i had and copied yours. Should i try me.txtLastName.Column(1) since it is a combo box?

[pbaldy]

You need to use the column that contains the data for the field you specified. If you're still getting that error, double check the spelling of the table and field names.

[akrylik]

Got it to work. This is just stage 1 of this functionality so ill probably be posting on this thread again once it gets more advanced. Thanks for the help.

[pbaldy]

Happy to help.

[akrylik]

i cant find the error in the code. says theres an else without an if.

If Me.Text24 = 1 Then Headcount = "True"
ElseIf Me.Text24 > 1 Then
If Me.Percent_Utilization = 1 Then Headcount = "True"
ElseIf Me.Percent_Utilization < 1 Then Headcount = "False"
End If
End If

you see it?

[pbaldy]

You're mixing the one-line and block formats of If/Then. Try

  If Me.Text24 = 1 Then
    Headcount = "True"
  ElseIf Me.Text24 > 1 Then
    If Me.Percent_Utilization = 1 Then
      Headcount = "True"
    ElseIf Me.Percent_Utilization < 1 Then
      Headcount = "False"
    End If
  End If