By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,796 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,796 IT Pros & Developers. It's quick & easy.

Reading all values from datasheet subform

P: 8
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
Sep 3 '07 #1
Share this Question
Share on Google+
4 Replies


P: 8
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
Sep 3 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. ................
  2. Dim RS as DAO.Recordset
  3. .............................
  4. Set RS = ctl1.Form.RecordsetClone
  5.  
  6. With RS
  7.     .MoveFirst
  8.     While Not .EOF
  9.         Debug.Print ![Field1Name];" "; ![Field2Name] ...............
  10.     Wend
  11. End With
  12. ................
  13. Set RS = Nothing
  14.  
Sep 4 '07 #3

P: 8
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
Sep 5 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.