Save All Record On Subform (1 Viewer)

sinau

New member
Local time
Today, 10:53
Joined
Jul 26, 2023
Messages
3
hi, im try to save all record on subform to tabel but only first record saved, an this my code :

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
 
Last edited by a moderator:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:53
Joined
May 7, 2009
Messages
19,249
use Bound form, so you don't need to use VBA.
saving will be done automatically.
 

GaP42

Active member
Local time
Today, 13:53
Joined
Apr 27, 2020
Messages
406
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

Registered User.
Local time
Today, 06:53
Joined
Dec 7, 2018
Messages
484
"' 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.
SQL:
... "' and endt_num =" & Rs!endt_num & ";"
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:53
Joined
Feb 19, 2002
Messages
43,720
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

New member
Local time
Today, 10:53
Joined
Jul 26, 2023
Messages
3
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
 

Users who are viewing this thread

Top Bottom