create subset list for payments 1

[guayabita]

What is the best solution for this?

I would like to create a list of organizations paid for in a payment. Each parent organization pays for at least one sponsored organization. I can create a list of sponsored organizations for each parent org. I must then let the user select from the list and I have to create a sub list that indicates who was paid for with each payment.

1) Is it best to create a combo box or checkbox list of the sponsored organizations for the user to make the selection?

2) How do I create the append query using the above list (selected combo box or checkbox list)? I'm assuming I have to create a linked table that lists the payment_ID and the sponsoredOrg_ID.

Thanks for helping a newbie!!!

 

[roppetech]

A couple questions for you:

1. Can each sponsored org have more than one parent company, or does each parent company have a seperate list of sponsored orgs?

2. Will each payment be made to one sponsored org, or could a payment be made to many sponsored orgs?

3. Around how many sponsored orgs will there be for each parent company to choose from?



- RoppeTech

 

[guayabita]

A couple questions for you:

1. Can each sponsored org have more than one parent company, or does each parent company have a seperate list of sponsored orgs?
Only one parent org but many sponsored organizations. Each parent org has it's own list of sponsored orgs.

2. Will each payment be made to one sponsored org, or could a payment be made to many sponsored orgs?
Payment in smade to third party for services (that's me) paying for parent org and its sponsored orgs.

3. Around how many sponsored orgs will there be for each parent company to choose from?
The most I've got now is 6 sponsored orgs but could grow.

Thanks RoppeTech!

 

[roppetech]

Ok now for your original questions-

1) Is it best to create a combo box or checkbox list of the sponsored organizations for the user to make the selection?

I would use a listbox and set the Multi Select property to extended. It is the easiest way to have a dynamic list of options that allows for multiple selections. Set the source of your listbox to SELECT sponsoredOrg_ID, sponsoredOrg_Name FROM tblSponsoredOrg WHERE sponsoredOrg_parentOrgID = your current parentOrg_ID. (try storing the parentOrg_ID in a hidden textbox on the form)

2) How do I create the append query using the above list (selected combo box or checkbox list)? I'm assuming I have to create a linked table that lists the payment_ID and the sponsoredOrg_ID.

This part is a little trickier, and there are several ways you could do it. One way is to use a For...Next loop to append a seperate payment record for each sponsored org selected in your listbox. Example:

Dim id As Variant, strSQL As String, intAmount as Double
For Each id In List0.ItemsSelected
strSQL = "INSERT into tblPayment " & _
"(PAYsponsoredOrg_ID,PAYamount,PAYdate)" & _
"VALUES (" & id & "," & intAmount & ",'" & Now() & "')"
CurrentProject.Connection.Execute strSQL
Next

I'm not sure how the payments work in your system so you'll have to set intAmmount somehow. Each time a parent makes a payment, is it a different amount for each sponsored org, the total ammount divided evenly among them, or what??

Another option is make a seperate table for sponsored payments that lists only sponsored_ID and payment_id, that would only show which sponsored orgs were selected for each payment, and have no way to track seperate amounts for each sponsored org.

Good luck!

- RoppeTech

 

[guayabita]

Thank you RoppeTech. I appreciate your help and patience. ; )