Accessing a sub form

[MikeEller]

Hello,
I need to access the records in a subform in visual basic.
The subform shows a table.
I can set the focus to the subform but when I try to set the focus to a field in the table, it does not work.

I am doing something like this:

Me.sub_form.SetFocus
subform_fieldname.SetFocus
variable = fieldname.Text

I have been away from this too long...I am sure there is a simple solution...I just cannot find it.

Any and all assistance is greatly appreciated.

Thanks,
Mike

 

[RuralGuy]

You need:
Me.sub_form.Form.SetFocus
Me.sub_form.Form.subform_controlname.SetFocus

 

[MikeEller]

RuralGuy,
Hey thanks...that did the trick
The problem was I was expecting the automatic completion popups to function....they don't....but the data is still accessed.

Thank you,
Mike

 

[MikeEller]

OK, next problem...

The sub form just displays a table.
Based on the code and guidance previously posted, I am able to access the subform and each field.
However, when I try to use the DoCmd.GoToRecord, , acNext, I get an error message telling me i cannot do this.
I have tried several different ways...like resetting the focus to the subform again as this is how I got to the first record to begin with.

I need to iterate over each field of each record in the subform.

Again Thanks,
Mike

 

[jaydwest]

Do you need to read all the fields in all the records, or update the data in the subform?

 

[MikeEller]

I need to read the data in each field of each record.

Thanks,
Mike

 

[jaydwest]

Have you tried using a recordsetclone to get access to the data?

Dim rstSubform as DAO.Recordset

Set rstSubform = Me![SubformFieldName].Form.RecordsetClone

With rstSubform
Do Until .EOF
Value1 = !Field1
Value2 = !Field2
.MoveNext
Loop
End With

rstSubform.Close: Set rstSubform = nothing

PLEASE NOTE THAT THE ABOVE SNIPPET HAS NOT BEEN COMPILED IN ACCESS AND MAY HAVE SOME TYPOS AND SYNTAX ERRORS. IF YOU GET AN ISSUE THAT YOU CANNOT RESOLVE, SEND ME AN EMAIL.

 

[jaydwest]

Have you tried using a recordsetclone to get access to the data?

Dim rstSubform as DAO.Recordset

Set rstSubform = Me![SubformFieldName].Form.RecordsetClone

With rstSubform
Do Until .EOF
Value1 = !Field1
Value2 = !Field2
.MoveNext
Loop
End With

rstSubform.Close: Set rstSubform = nothing

PLEASE NOTE THAT THE ABOVE SNIPPET HAS NOT BEEN COMPILED IN ACCESS AND MAY HAVE SOME TYPOS AND SYNTAX ERRORS. IF YOU GET AN ISSUE THAT YOU CANNOT RESOLVE, SEND ME AN EMAIL.

 

[RuralGuy]

Hi Mike,
I agree with Jay about using the RecordSetClone rather than the SubForm reference.

Hi JayW,
To set the record straight, the RecordSetClone is *always* available and ready. You do not need to "Set" it and it is *not* a good idea to "close" it either. Your code then becomes:

With Me.SubFormControl.Form.RecordSetClone
   .MoveFirst
   Do Until .EOF
      Value1 = !Field1
      Value2 = !Field2
      .MoveNext
   Loop
End With

No Set = Nothing required. Just thought you'd like to know.

 

[MikeEller]

OK,
Maybe I am doing this wrong.

The recordsetclone worked somewhat....It iterated the correct number of times...so progress is being made.

However, the data is not changing. Meaning as I iterate over a field for all the records, I should get the data from each field..but I am not. I get the same (first field) data each iteration.
The code forced me to set the focus on the control each time...maybe that is causing a problem.

Here is my code and explanation:

Dim theText

With Me.sub_support.Form.RecordsetClone
.MoveFirst
Do Until .EOF
Me.sub_support.Form.Subject.SetFocus
theText = Me.sub_support.Form.Subject.Text
MsgBox "The text is " + theText
Loop
End With

sub_support is the subform
Subject is a text field on the form.
The subform is based on a table...there was no form creation except what the
wizard did.
The MsgBox prints the content of the the Subject field....each time it prints the same thing????

I appreciate the attention...
I like solving things like this,
Mike

 

[RuralGuy]

Hi Mike,
You are mixing the modes. When you use the RecordSetClone there are *no* controls involved, just fields in the RecordSetClone. Let's say you have two fields and [Field1] is a text field and [Field2] is a numeric field. Your code would look like:

With Me.SubFormControl.Form.RecordSetClone
   .MoveFirst
   Do Until .EOF
      TextValue1 = TextValue1 & !Field1
      NumValue2 = NumValue2 + !Field2
      .MoveNext
   Loop
End With

 

[MikeEller]

RuralGuy,

You are the man...

I was using Field1.Text....and kept getting error messages.
Took that out...as you code showed...and it works.

Thank you,

Mike

 

[RuralGuy]

Glad to help. Good luck with the rest of your project.