Save All Record On Subform (1 Viewer)

[sinau]

hi, im try to save all record on subform to tabel but only first record saved, an this my code :

Public Sub DetailSaved()

Dim Rs As DAO.Recordset
Dim subFrm As Access.Form

Set subFrm = Me.[FTSLIPSEC].Form
Set Rs = subFrm.RecordsetClone
Do While Not Rs.EOF

CurrentDb.Execute "INSERT INTO [tclmshare] (fscregno,fscaccid,fscslpno,fscsleno,fscproid,fscpronm,fscshare,fscedsec,fscamont,fsctamnt,fscvochr)" & _
                  "select '" & Me!FTSLIPSEC.Form.txt_regno & "',acc_id, slip_no, endt_num, prof_id, prof_name, share, cedsec, '" & Me!FTSLIPSEC.Form.txt_valsc & "',(share * '" &                     Me!FTSLIPSEC.Form.txt_valsc & "' /100 ) AS '" & Me!FTSLIPSEC.Form.txt_tvlsc & "','" & Me!FTSLIPSEC.Form.txt_notsc & "' from QTSLIPSEC " & _
                  "Where slip_no ='" & Me!FTSLIPSEC.Form.slip_no & "' and endt_num =" & Me!FTSLIPSEC.Form.endt_num & ";"
Rs.MoveNext
Loop

End Sub

 

[arnelgp]

use Bound form, so you don't need to use VBA.
saving will be done automatically.

 

[GaP42]

If you are trying to insert multiple records into tclmshare from query QTSLIPSEC (whatever they are), then why not do it from an Append Query? You have criteria to constrain the set of values - based on form values (?), and you could launch from a button on your form, after committing any change to data on the form. (ie. not dirty). Assuming that you are sure that you would not create duplicate records in tclmshare.

 

[Eugene-LS]

"' and endt_num =" & Me!FTSLIPSEC.Form.endt_num & ";"

Wrong!!!
In my opinion you should take the argument from current record of open Recordset - not from current record of subform.

... "' and endt_num =" & Rs!endt_num & ";"

 

[Pat Hartman]

Welcome to AWF

hi, im try to save all record on subform to tabel but only first record saved, an this my code :

You must already be using a bound form if you are seeing multiple records on the subform. For starters, storing data in multiple tables is generally wrong in a relational database. What is the reason for copying the data from the bound form to a different table?

The way an Access form works (and it doesn't matter whether it is single, continuous, or data sheet view), when you move from one record to a new one, the previous record is saved or added as the case may be. Therefore only the CURRENT record is ever dirty. All other records have been saved.

 

[sinau]

thank you for the input and suggestions from everyone in this forum, it turns out that the error that occurred came from me stating the first field was primary while the data I entered was detailed data, so only 1 record entered, after I removed the primary... .all data can be entered, thank you all for the support. case closed