Loop and set form field value (1 Viewer)

[KoskeKos]

Hi!
I need to loop through table and check client field [answer].
If answer is "yes" then put value on form (SEC) 1, otherwise put 0.
I hope i explained well.

 

[plog]

The simple way to achieve what you asked about is to create a query based on your table, create a calculated field that converts the Yes/No to 1/0 and use that as the basis for your form:

SEC: Iif([answer], 1,0)

But why? Why 1/0 instead of a checkbox? What's the big picture--how is this form to be used, what does 1/0 allow you to achieve?

Also, that assumes [answer] is a yes/no field. Is that its data type? Or is it a text field where you literally have "yes" and "no" values?

 

[KoskeKos]

Thanx @plog.

1. But why? Why 1/0 instead of a checkbox?
--> Actually it doesn matter where its yes/no or 1/0. I get data from excel sheet.
2. What's the big picture--how is this form to be used, what does 1/0 allow you to achieve?
--> I work on financial risk algorithm. I have 130 evaluations to calculate. Many of them depends on previous ones.
--> Ill need to use this form and values for check and after for more evaluations.
3. Also, that assumes [answer] is a yes/no field....
It is a text field where you literally have "yes" and "no" values
So i started to put one at the time.

What will be VBA version of this?
I have this loop but cant get for every Client the right answer in form field: I get all 1's or 0's.

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        '*********** SEC ***********
        If rs!InitExp_Category = "YES" Then ---this is actually field name in table
            Me!txtSEC = 1
        Else
            Me!txtSEC = 0
        End If
        rs.MoveNext
    Loop
Else
   MsgBox "There are no records in the recordset."
Exit Sub
End If
DoCmd.Hourglass False
   MsgBox "Finished looping through records."
rs.Close
Set rs = Nothing
Set Db = Nothing

 

[Gasman]

That is likely as you are processing a recordset, but setting a form control?

 

[plog]

I get data from excel sheet.

Sounds like you're focused on a sub-optimal solution for one step and can't see the big picture better way.

You should simply fix your data so it's in the table correctly. This may mean staging the data from Excel in a temporary table then moving it to a table with the correct struture (with a numeric field for this text field). Or simply using an UPDATE query to convert those yes/no values to numbers.

VBA isn't the right tool and getting this to work in a form is focusing on the wrong thing. Simply fix your data and get it into the correct data types in a table.