fields in Recordset

[radek225]

I have a table with many yes\no fields. Now I need to use some code in Recordset which can check that field is -1 or not and if is -1 then get the name of this field

So the problem is use some properties for every fields in my Recordset no matter what is called to check -1 or not, and take name of field where field = -1

while not rst.eof
if field = -1 then
'now I need to get name of this filed

....
end if
wend
rst.close

 

[namliam]

This may help:
https://msdn.microsoft.com/en-us/library/office/ff193530.aspx

 

[radek225]

This may help:
https://msdn.microsoft.com/en-us/library/office/ff193530.aspx

Still don't know how can i get name of the columns:/

 

[Galaxiom]

Having the requirement of returning the field name from the value means the the structure is backwards. You are encoding too much in the names of the fields when the information should lie in the record itself.

This is a really important relational database concept. Better to structure your data using a related table. Let's call it "Characteristics".

In this table there is a key to join it to the record in the parent table. Another field holds the ID of the particular characteristic that you are currently encoding in the field names. Each characteristic is a single record so a main record will have several child records in the Characteristics table. The existence of a record in this table signifies True for that characteristic.

A lookup table (CharacteristicNames) stores the ID along with of the characteristic name which is used in the display.

A huge advantage of this structure is that new characteristic can be added without changing the design of the tables, queries or forms because new characteristics are added by adding records rather than fields. Moreover, the display name of a characteristic can be globally edited by simply changing the name in the lookup table.

A subform can be used to enter the Characteristic data.

 

[namliam]

Still don't know how can i get name of the columns:/

Have you tried the code in the link?
I am looking at this line in particular:

Debug.Print " " & prpLoop.Name & " = " & _ 
 prpLoop.Value

 

[arnelgp]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim prp As DAO.Property
dim strFieldName as string
Set db = CurrentDb
Set rs = db.OpenRecordset("yourTableName", dbOpenDynaset)
On Error Resume Next
With rs
For Each fld In rs.Fields
For Each prp In fld.Properties
If prp.Name ="Type" And prp.Value = 1 Then
If fld.Value = -1 then
strFieldName = fld.Name
End If
End if
Next
Next
End With
Set rs = Nothing
Set db = Nothing