Search results

Hi Pat Hartman, Thanks for the advice.

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")

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...

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...

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.

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 =...

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.

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.

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...

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...

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.

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...

Hi arnelgp, It worked very well. Thanks again.

Hi arnelgp, Please see attached what i mean exactly. when i add the following criteria to the FinalQuery under Loan_Schedules.Loan_ID = [Forms]![Form1]![ID] And input the respective Loan_ID in the ID field within Form1, an error is generated in the macro when the query is executed using the...

Hi arnelgp, How would i go about filtering the SQL query by a value input in a text field on a specific form i.e. where an additional criteria is added to filter the Loan_Schedules table using the loan id input in a text field on a form Loan_Schedules.Loan_ID=Forms!FormX!Loan_ID whenever i...

arnelgp, How many times am I going to say thank you. Thanks a lot. It worked like a charm. Just brilliant coding, that is what it is.

Hi Pat Hartman, That is some brilliant coding. For future purposes might need to borrow it to generate an amortization schedule. My challenge currently is to incorporate the repayments in the amortization schedule to show a "schedule of repayments" against the amortization schedule. For...

Hi arnelgp, I have been able to add the first 2 columns i.e. Interest Paid and Principal Paid to the SQL script. However, the final column i.e. the Outstanding_Balance has failed me. Can you kindly assist with the final column i.e. Column Name: Outstanding_Balance Column Logic: For...

Thanks June7. However arnelgp was able to assist with the exact SQL script required on the following forum. https://www.access-programmers.co.uk...d.php?t=305946 Thank you once again for your assistance.

Hi arnelgp, Is it possible to add the following columns to SQL script with the following logic; Column Name: Interest_Paid Column Logic: IF(TotalPayment < Scheduled_Interest, TotalPayment, Scheduled_Interest) Column Name: Principal_Paid Column Logic: IF(TotalPayment = Interest_Paid, 0...