Reading all values from datasheet subform

[paulw4]

I have a mail form that has a datasheet subform, there can be up to sixty items in the datasheet. The data sheet has three fields: AttID, AttName, AttYN

The object is for the enduser to select attributes, that relate to the contact on the main form.
Below is the code I have been working with to read all the attributes checked "Yes". The intent is then update/append the cntID from the main form, the attID and attYN value into a linked table. (Second part of this module)

The initial issues are:
1) When using an IF statement the only recongizable value in the subform
is "-1".The following are not recognized by the IF statment: "0","<> -1", Null, or <> Null. What I am after is to select all attYN <> -1
2) When running the debug.print for the values, as the shortened loop runs, the value for the CategoryID stays the same, but the counter increments

The code is:
Private Sub Command4_Click()
Dim aconID, aattID, asel, cnter

For cnter = 0 To 6
If (Forms!ztestatt2!zsfrmattrib.Form!consel.Value = -1) Then
aattID = Forms!ztestatt2!zsfrmattrib.Form!categoryID.Value
asel = Forms!ztestatt2!zsfrmattrib.Form!consel.Value

Debug.Print cnter, Me.CntID, aattID, asel

End If

The results in the debug.print is
0 2 1 -1
1 2 1 -1
2 2 1 -1
3 2 1 -1
4 2 1 -1
5 2 1 -1
6 2 1 -1

It looks as if the cnter is incrementing but I am not incrementing through the datasheet. The values are only for the first record.
Looks like the next question is how do I increment through the datasheet?



Note: The Me.CntID is from the main form for this contact

[paulw4]

OK, I have been researching the record set clone and movenext features and have the following code

Set frm1 = Forms("ztestatt2")
Set ctl1 = frm1.zsfrmattrib

Debug.Print frm1.RecordSource
Debug.Print ctl1.Form.RecordSource
Debug.Print ctl1.Form.RecordsetClone.RecordCount
Debug.Print

For int2 = 0 To ctl1.Form.RecordsetClone.RecordCount - 1


Debug.Print int2, ctl1.Controls!CategoryID _
, ctl1.Controls!Category _
& " " & ctl1.Controls!consel
' ctl1.Form.RecordsetClone.MoveNext

Next int2

The results are:
qryContacts (RECORD SOURCE FOR MAIN FORM)
qryconattrib (RECORD SOURCE FOR SUBFORM)
54 (RECORD COUNT)

0 59 Colleges and Universities 0
1 59 Colleges and Universities 0
2 59 Colleges and Universities 0
.
.
.
51 59 Colleges and Universities 0
52 59 Colleges and Universities 0
53 59 Colleges and Universities 0


Many thing about this have me baffled
1. How do I get it to start at 0 record for the loop
2. Why is the last record the record that is being read

Thanks for the help!
PW

[FishVal]

OK, I have been researching the record set clone and movenext features and have the following code

Set frm1 = Forms("ztestatt2")
Set ctl1 = frm1.zsfrmattrib

Debug.Print frm1.RecordSource
Debug.Print ctl1.Form.RecordSource
Debug.Print ctl1.Form.RecordsetClone.RecordCount
Debug.Print

For int2 = 0 To ctl1.Form.RecordsetClone.RecordCount - 1


Debug.Print int2, ctl1.Controls!CategoryID _
, ctl1.Controls!Category _
& " " & ctl1.Controls!consel
' ctl1.Form.RecordsetClone.MoveNext

Next int2

The results are:
qryContacts (RECORD SOURCE FOR MAIN FORM)
qryconattrib (RECORD SOURCE FOR SUBFORM)
54 (RECORD COUNT)

0 59 Colleges and Universities 0
1 59 Colleges and Universities 0
2 59 Colleges and Universities 0
.
.
.
51 59 Colleges and Universities 0
52 59 Colleges and Universities 0
53 59 Colleges and Universities 0


Many thing about this have me baffled
1. How do I get it to start at 0 record for the loop
2. Why is the last record the record that is being read

Thanks for the help!
PW

Hi, there.

Try this.

................
Dim RS as DAO.Recordset
.............................
Set RS = ctl1.Form.RecordsetClone
 
With RS
    .MoveFirst
    While Not .EOF
        Debug.Print ![Field1Name];" "; ![Field2Name] ...............
    Wend
End With
................
Set RS = Nothing
 

[paulw4]

Thanks for all the help to date.
I thought the next step would be easy.....

After reading the datasheet, the next event is to append AND/OR update a table with all the values, even for the False results.
Can someone help with the structure of the correct process...

Thanks
PW

[FishVal]

Thanks for all the help to date.
I thought the next step would be easy.....

After reading the datasheet, the next event is to append AND/OR update a table with all the values, even for the False results.
Can someone help with the structure of the correct process...

Thanks
PW

Hi, PW.

Please include the MetaData for main form and subform RecordSource and Master/Child link fields, as well as MetaData of the table you want to append to.


Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time