Insert into table values from main form and subform

[SAM256]

Hi Experts,

I have a table called payments with the following fields;

Customer_ID,
Loan_ID,
Payment_Type,
Schedule_ID,
Gross_Paid,
Net_Paid,
Payment_Date

And i would like to insert data into the table and the data is in both the main form and sub form as follows;

Main form data;
Customer_ID,
Payment_Type,
Payment_Date

Sub form data;
Loan_ID,
Schedule_ID,
Gross_Paid,
Net_Paid,

Please help with the SQL script.
Also note the sub form is a continuous form and i would like all the required data to move from it to the payments table.

Thanks in advance for all the help.

 

[theDBguy]

Hi. It sounds like you’re trying to store duplicate data in multiple tables. Have a look at Allen Browne’s website for an example code.

 

[Dreamweaver]

You could use a query to link both together and use that as it would contain all the info in one place not sure if you wish to total the payments or not.


hope it helped mick

 

[SAM256]

Thank you both for your suggestions.

I really just need the sql insert statement. I have tried several on my own and failed.

P.S. both forms are unbound, so the data is gathered from both and stored in one table for further use.

Thanks in advance.

 

[SAM256]

Hi Experts,

I have been able to figure out a portion of the sql script. See below for just 2 data fields;

DoCmd.RunSQL "INSERT INTO Payments (Customer_ID, Schedule_ID) VALUES ([Customer_ID], Forms.MainFormName.[Subform1 subform].Form.Schedule_ID);"

However, the SQL only picks the data in the first row of the Sub form and does not pick the data from the remaining rows.

Please help with writing a script that copies all data from all rows in the sub form, together with the data from the fields in the main form and inserts it into the table.

Thanks in advance.

 

[jdraw]

Please post a graphic of your tables (png/jpg).
You need to ensure your database design can accommodate your needs before dealing with the specifics of an Insert statement.
It would also help if you gave an overview of the business involved. Who does what etc.
It's rare for a well designed database to have 1 table to collect data from form and subform. If you provide more info, I'm sure you'll get more focused responses.

Good luck.

 

[MajP]

You say this

Also note the sub form is a continuous form and i would like all the required data to move from it to the payments

P.S. both forms are unbound, so the data is gathered from both and stored in one table for further use.

I am a little confused. There is no such thing as a continuous unbound form that I know of. If it is bound then you can loop the subform's recordset and perform multiple inserts. If it is unbound then say you have 5 rows each row would have to have different control names. So which is it?

 

[SAM256]

Hi MajP,

Apologies, the specifics are, the main form is unbound, but the sub form is bound to a query that generates several rows of data.

What I am trying to achieve is to is to take some data from the main form and the rest from the sub form and post it to the payments form.

As opposed to posting each entry manually.

I have been able to generate the SQL script needed (see earlier threads), but the loop has failed me.

Can you help?

 

[SAM256]

Hi MajP,

Attached are screenshots of my tables and the scripts and forms in design view to further illustrate what i am trying to achieve.

Hope you can help.

 

[MajP]

If you say the SQL works for one record then something like this, but i doubt as written that works because you never resolve customer_ID.

dim subFrm as access.form
dim custId as long
dim schedID as long
dim rs as dao.recordset
dim strSql as string
set subfrm = me.[Subform1 subform].Form
set RS = subfrm.recordsetclone
custID = me.Customer_ID
do while not RS.eoff
  schedID = RS!Schedule_ID
  strSql = "INSERT INTO Payments (Customer_ID, Schedule_ID) VALUES (" & CustID & ", " & schedID & ")"
  currentdb.execute strSql
  rs.movenext
loop

 

[SAM256]

Hi MajP,

when i use the code i get the following error;

"method or data member not found" pointing to the rs.eoff part of the code.

 

[SAM256]

Hi MajP,

I have been able to push all the necessary data fields to the sub form i.e. the customer_ID, Payment_Type, and Payment_Date and using your code below is what i run.

Private Sub Command60_Click()
Dim subFrm As Access.Form
Dim rs As DAO.Recordset
Dim strSql As String
Set subFrm = Me.[Principal_Settlements subform].Form
Set rs = subFrm.RecordsetClone

Do While Not rs.eoff
Client_ID = rs!Client_ID
Loan_ID = rs!Loan_ID
Schedule_ID = rs!Schedule_ID
Principal_Owed = rs!Principal_Owed
Principal_Extended = rs!Principal_Extended
Product_Code = rs!Product_Code
Settlement_Date = rs!Settlement_Date

strSql = "INSERT INTO Payments (Customer_ID, Loan_ID, Payment_Type, Schedule_ID, Gross_Paid, Net_Paid, Payment_Date) VALUES (" & Client_ID & ", " & Loan_ID & ", " & Product_Code & ", " & Schedule_ID & ", " & Principal_Owed & ", " & Principal_Extended & ", " & Settlement_Date & ")"
CurrentDb.Execute strSql
rs.MoveNext
Loop
End Sub

Do let me know if i missed out anything.

 

[Gasman]

There was a typo with MajP's post
Should be

Do While Not rs.eof

Also please use the code tags as MajP did, so the code can be read more easily.?
Also those new variables should be dimmed with correct type?

Code tag icon is #

 

[SAM256]

Hi Gasman

Unfortunately I am no pro at writing macros, so kindly simplify the advise, so i can compile and execute it properly.

If possible please point me to where i went wrong.

 

[Pat Hartman]

1. If you are using bound forms as you should be, no code is required.
2. If there is a separation in what data belongs to the "parent" and what belongs to the "child", you should be using TWO tables rather than one.
3. If you elect to not follow the "best practices" advice, then, you need only three lines of code in the subform's BeforeUpdate event.

Me.Customer_ID = Me.Parent!Customer_ID
Me.Payment_Type = Me.Parent!Payment_Type
Me.Payment_Date = Me.Parent!Payment_Date

The three attributes you are getting from the unbound main form MUST be in the query that the subform is bound to but they do not need to be bound to controls on the subform.

 

[SAM256]

Hi Pat Hartman,

Thanks for chipping in. Indeed, i took the approach you just detailed in the preceding email. Now the only challenge left is to insert the following data columns into the "payments" table from the sub form.

The sub form has multiple rows of data and the simple insert into sql script i built does not suffice (it only copies 1 row of data) (i would need a loop to complete the db successfully). Below are the data fields. The sub form name is "Principal_Settlements subform"

The data fields are;
Customer_ID,
Loan_ID,
Product_Code,
Schedule_ID,
Principal_Owed,
Principal_Extended,
Settlement_Date

And the payments form has the exact column headers ("naming not being an issue")

 

[MajP]

(it only copies 1 row of data) (i would need a loop to complete the db successfully)

Ugh, did I not show this?

 

[SAM256]

Hi MajP,

Yes you need and i thank you for thank,

Below is what i could put together from your code; it returns zeros(0) in the table columns instead of the values;

Private Sub Command60_Click()
Dim subFrm As Access.Form
Dim Customer_ID As Long
Dim Loan_ID As Long
Dim Product_Code As Long
Dim Schedule_ID As Long
Dim Principal_Owed As Long
Dim Principal_Extended As Long
Dim Settlement_Date As Date
Dim i As Integer
Dim rs As DAO.Recordset
Dim strSql As String
Set subFrm = Me.[Principal_Settlements subform].Form
Set rs = subFrm.RecordsetClone
Do While Not rs.EOF
strSql = "INSERT INTO Payments (Customer_ID, Loan_ID, Payment_Type, Schedule_ID, Gross_Paid, Net_Paid, Payment_Date) VALUES (" & Customer_ID & ", " & Loan_ID & ", " & Product_Code & ", " & Schedule_ID & ", " & Principal_Owed & ", " & Principal_Extended & ", #" & Settlement_Date & "#);"
CurrentDb.Execute strSql
rs.MoveNext
Loop
End Sub

what could be the problem?

 

[Gasman]

Are these really Long?

Put Debug.Print strSql before the execute line and inspect in the debug window ?

Dim Principal_Owed As Long
Dim Principal_Extended As Long

 

[SAM256]

Hi Gasman,

In the originating table the property of the fields is long integer. However, in the sql script that is bound to the sub form they are further formatted using Format(xxxxxx,"#,##0")