Loop to open form

[keden]

I have a form with PPL_ID, cust_name, and PPL_date, and numb_of_p. I would like to have the user enter a number in numb_of_p and when a button was clicked, form ppl would open they would enter information on the form the data would update the table, close, and repeat the number of times entered in numb_of p of the first form. I am trying to do it using a loop but have not found anything similar to what I am trying to do. Would I enter the code in the onclick command of the original form? Or would I use it on the onload command of the form? Can anyone assist how to write the code? any help or a point in the right direction would help.

thanks,
Kede

 

[SforSoftware]

Hi keden,

I'd think the only thing you need is to put the code in the AfterUpdate-event of numb_of_p to open the second form (DoCmd.OpenForm "ppl"). After closing the second form you come back in the first. Each time you enter or change the value of numb_of_p, the second form is opened.

Is that what you mean?

 

[keden]

What the db will do is track payments in a payment plan. The user would put the number of payments to customize their customers payment plans....If the user sets up 4 payments, I want them to be able to schedule 4 payments populating the amount of the payment and the scheduled receipt date. This information along with a payment plan ID (tying to that particular customer) and a payment id (specific to the individual scheduled payments) to update a table with a line item for each payment.

I have thought of other ways to attempt to have the payment come across as individual line items but to no avail. Any other ideas as to how I might do this would work.

 

[SforSoftware]

Why don't you use a subform in your current form with the individual payments?
After updating the count of payments you can run an APPEND-query to ad the given count of records into the table and then you take a Requery on the subform te refresh it.
When you do it so, everything is in one form. The end-user will appreciate that.

 

[keden]

Thank you. I will give this a try and let you know how it works.

Kede

 

[keden]

I attempted the loop and was having issues. This is the first time I have ever attempted loop procedures. I was trying to get it to load the form x amount of times as the user inputed the amounts. I am postponing the work on this issue to finish the rest of the dB. When I get back to that portion, I am sure I will be back with more questions. I do appreciate your help though.

Kede

 

[keden]

My attempt

here is what I have tried. What I am shooting for, is to have a loop that will open the payment sub form the number of times that the user chooses on the main form. This would then create a separate line item on the individual payment table that will be tied to the main table by the payment plan id. If anyone can help me with this or point me in the right direction (another way of doing this) your help would be appreciated.

Thanks,
Keden

 

[keden]

I am now going a different direction. I am attempting to build control boxes on the form that represent the 3 needed fields. One field for amount, one for date_sched, and one with a field for the PPL_ID with a default that equals the randomly generated number from the top of the form that identifies the ppl_main. I am wanting to build a function off a submit button to add each record (one at a time) to the ppl_sub table as individual records. Is this possible?

Thanks,
Keden

 

[WayneRyan]

keden,

The following should work:

Dim dbs As DAO.Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Table1")
rst.AddNew
rst!SomeField = Me.SomeControl
rst.Update

Set rst = dbs.OpenRecordset("Table2")
rst.AddNew
rst!SomeOtherField = Me.SomeOtherControl
rst.Update

That's the general idea, substitute the names
of your tables, fields and form controls.

Wayne

 

[keden]

Below is what I put together. I am wanting the first table to house the payment Plan information specific to that payment plan, the second table will detail scheduled payments. Am I just not getting this? Any help is appreciated.

Thanks,



Private Sub Submit_cust_PPL_Click()
On Error GoTo err_submit_cust_PPL_click

Dim dbs As DAO.Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Payment_plan_main_form_table")
rst.AddNew
rst!SPID = Me.cust_ID
rst!PPL_ID = Me.PPL_ID
rst!NumberOfPayments = Me.NumberOfPayments
rst!date_pppl_made = Me.Date_PPL_made
rst!Date_PPL_signed = Me.Date_payment_plan_signed
rst!Approver_name = "N/A"
rst.Update

Set rst = dbs.OpenRecordset("Payment_plan_sub")
rst.AddNew
rst!PPlan_ID = Me.PPlan_ID
rst!Sched_PPL_amount = Me.PPL_Pmnt1_amnt
rst!Sched_ppl_date = Me.PPL_Pmnt1_date
rst.Update

Set rst = dbs.OpenRecordset("Payment_plan_sub")
rst.AddNew
rst!PPlan_ID = Me.PPlan_ID
rst!Sched_PPL_amount = Me.PPL_Pmnt2_amnt
rst!Sched_ppl_date = Me.PPL_Pmnt2_date
rst.Update

Set rst = dbs.OpenRecordset("Payment_plan_sub")
rst.AddNew
rst!PPlan_ID = Me.PPlan_ID
rst!Sched_PPL_amount = Me.PPL_Pmnt3_amnt
rst!Sched_ppl_date = Me.PPL_Pmnt3_date
rst.Update
Exit_Submit_cust_PPL_Click:


Set rst = dbs.OpenRecordset("Payment_plan_sub")
rst.AddNew
rst!PPlan_ID = Me.PPlan_ID
rst!Sched_PPL_amount = Me.PPL_Pmnt4_amnt
rst!Sched_ppl_date = Me.PPL_Pmnt4_date
rst.Update
Exit Sub

err_submit_cust_PPL_click:
MsgBox Err.Description
Resume Exit_Submit_cust_PPL_Click
End Sub

 

[WayneRyan]

keden,

If you have all of those fields on your form, and
they map to the tables as you say, then you're OK.
I did make a typo: Dim rst As DAO.Recordset

However, instead of adding records with code, this
should be a main form and several subforms.

Wayne

 

[keden]

I am getting a "User-defined type not defined" compile error and the debugger points to the dim dbs as dao.database line.

Keden

 

[WayneRyan]

keden,

Get your code in the design window, choose Tools --> References
then scroll down and check Microsoft DAO 3.6 and click the
up arrow to put its priority as high as possible.

Wayne

 

[keden]

Thank you...thank you...thank you. I really appreciate all the help. It is now doing what I had in mind. Is there anyway to provide ratings for users of the message board that provide assistance?


Kedent

 

[WayneRyan]

keden,

Yeah, just keep coming back! People here are glad to help.

Wayne

 

[keden]

Will most definitely do. I appreciate it. I will also throw in advice where my limited knowledge provides. Again, I apreciate your help.

Keden