Looping Left function has me stumped (1 Viewer)

[pensived]

Hello Everyone,

I have a sub-form in datasheet view with several records, the first two characters of each record make up a product code, that is then copied to a field on the parent form. I can split the first two characters from the first record. I've tried everything I can think of to loop through, but I'm missing something. I get the first two letters of the first line X the number of lines. I get "PM X 4" PMPMPMPM

Private Sub Command84_Click()
Dim rst As DAO.Recordset
Dim sTmp As String
Set rst = Me.[PanelBuild].Form.RecordsetClone

With rst
  Do Until .EOF
    sTmp = sTmp & Left([Forms]![panelbuilder]![PanelBuild]![subCode], 2)
    .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

Me.Text82 = sTmp

End Sub

Any help would greatly appreciated. Thank you

 

[vbaInet]

You're looping through the recordset but you're not using the data in the recordset.

 

[pensived]

I'm sorry, but I don't understand? I'm getting data, but only from the first record over and over again.

 

[vbaInet]

You're getting text from this textbox "subCode" but you're not getting data from the recordset. Think of a recordset like a table. Use the Fields() method (i.e. rst.Fields("FieldName")) or rs!FieldName of the recordset to get the field data you want.

 

[pensived]

I've tried changing Me.[PanelBuild].Form.RecordsetClone to include the field on the subform, but I don't think the syntax is right?

 

[vbaInet]

You misunderstand the use of a recordset. The following link should give you some insight:

http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

 

[MarkK]

With rst
  Do Until .EOF
[COLOR="Green"]    'use a field from your cloned recordset, not the subform . . .[/COLOR]
    sTmp = sTmp & Left(.fields("subCode"), 2)
    .MoveNext
  Loop
End With

 

[pensived]

Oh man, that's why I couldn't get it! I was focused on the subform! Thank you so much. Too many hours staring at a screen, makes for permanent brain farts.

Thank you again.!

 

[vbaInet]

Oh man, that's why I couldn't get it! I was focused on the subform! Thank you so much. Too many hours staring at a screen, makes for permanent brain farts.

Thank you again.!

I would advise that you also look at the link provided. It will give you some basic insight into the use of recordsets.