Update Subform Record from a given value

[GarrySy]

How do I create a script that when I click the Button, It will execute a script that will get the value (or what ever number inputted) on textbox1, then value on the textbox1 will enforced update the specific field (example "Size") in the subform?

I try to write a code on button_click(), "me.size = me.txtbox1". It update but only first row on the subform. But what If there are ten rows on subform? I want to update all at one click.

Thank you. Your help will be greatly appreciated.

Garry Sy

 

[TheAceMan1]

How are ya GarrySy . . .

Two methods come to mind:
[ol][li]Use an update [blue]query or SQL[/blue] (fastest).[/li]
[li]Loop thru the subforms [blue]recordset[/blue] updating record by record.[/li][/ol]
Question: are textbox1 and the command button on the subform?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[GarrySy]

Hi TheAceMan1

Thanks for you greeting and response.

Yes, both textbox1 and command button are on the subform.

Thank you again

 

[TheAceMan1]

GarrySy . . .

Try the following in the [blue]On Click[/blue] event of the button (you substitute proper names in [purple]purple[/purple]):

[blue]   Dim fld As DAO.Field, rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   Set fld = rst![[purple][B][I]FieldName[/I][/B][/purple]]
   
   If Not rst.BOF Then
      Do
         rst.Edit
         fld = Me![[purple][B][I]Textbox1[/I][/B][/purple]]
         rst.Update
         
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Me.Requery
   Set fld = Nothing
   Set rst = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[GarrySy]

Hi TheAceMan1

Thanks a lot for the script.

It work very well, however, there is a slight error, that after the script execute, then I move to the next record, then execute the script again by clicking again the button, it show an error:

Run-Time error'3021';
No current Record.

When I click debug on the message box, it highlighted the "rst.edit". Any idea?

Thank you again.

 

[PHV]

   Dim rst As DAO.Recordset
   Set rst = Me.RecordsetClone
   rst.MoveFirst
   Do While Not (rst.BOF Or rst.EOF)
      rst.Edit
      rst![FieldName] = Me![Textbox1]
      rst.Update
      rst.MoveNext
   Loop
   Me.Requery
   Set rst = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GarrySy]

Thanks a lot PHV.