I am developing a membership database. At years beginning, I need to process several payments at a time .
I found a "contacts" sample online a long time ago, so I don't know where the origional file comes from...but it had an example of using an unbound form.

I want to use an unbound form with a listbox to select several members at a time, and enter the payment information, then click the "Add Payments" button to add records to the Payments table for each member selected.

I currently have a form that I can use to add an individual payment, but it doesn't close the connection correctly and I get errors.

I have included a copy of this "contacts.mdb" file. You'll have to change the db path in the code....
The form in it isn't pretty... I will fix that later. At this point I have no idea how to get the code to see the multiple selections and add all the records for those selected.

any help is deeply appreciated!
Oh heck, while we're at it, if using a BOUND form would make it easier, I could live with that... but use a query, not the table directly. (eventually I will be handing this to a user to enter the payments)

Welcome to Utter Access!

You will need to loop through the selected items in the list of members. I have added the necessary code to your form. The revised DB is attached.

Thank you soooo much!!! I knew it had to loop, I just didn't know how to make it do that. (programming nubee).
What should I add on the "Clear Form" button code to make the NameList unselect everything?

DoCmd.RepaintObject "NameList" ?????

I just tried the following with no luck:


Private Sub Command17_Click()
'Clear the form data and refresh the NameList listbox

Me.NameList.SetFocus
Me.NameList.Requery
txtAmountPaid.SetFocus
txtAmountPaid.Text = ""
txtPDStatus.SetFocus
txtPDStatus.Text = ""
txtPaymentType.SetFocus
txtPaymentType.Text = ""

End Sub

To clear the previous entries, the following should work:

Me.Me.NameList= Null
Me.txtAmountPaid = Null
Me.txtPDStatus= Null
etc.

I found a piece of code to clear the listbox online and this works better:

Private Sub Command17_Click()
'Clear the form data and refresh the NameList box

Dim i As Long

With Me.NameList
For i = 0 To .ListCount - 1
.Selected(i) = False
Next
End With

' Lesson learned: You can't assign a Null value to a field that has a default value
' assigned in the properties or by code. Access will choke on it.

' this line doesn't work at all for me Me.Me.NameList = Null

Me.txtAmountPaid = Null
Me.txtPDStatus = Null
Me.txtAmountPaid = Null
Me.txtPaymentType = Null

End Sub

See the new attached file. This solution works great for what I need to do.
Thanks everyone!!
Presto.

You're welcome. Glad we could help.

There should have only been one "me" in the me.me.NameList=null. As a clarification, the me.NameList=null will work if the multi select property of the list box is set to Extended, but it will not work if the multi select property is set to Simple. I did not catch that in your application, sorry.

I'm glad you caught the issue regarding the controls with the default values; I guess my brain was =null (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sleeping.gif) when I posted that.

Edited by: jzwp11 on Sun Oct 19 19:11:20 EDT 2008.

jzwp11 wrote: "There should have only been one "me" in the me.me.NameList=null. As a clarification, the me.NameList=null will work if the multi select property of the list box is set to Extended, but it will not work if the multi select property is set to Simple. I did not catch that in your application, sorry. "

Yeah, I couldn't understand that one for a while. I thought I was going nuts...

I have one more question. As part of the payment process, I also have to set values in the Status table.
For each member selected, add the following values (these are updates to current records not NEW ones.)
1. Look for the CID(everyone has ONE Status record). 2. Edit the following fields with values:

PDStatus = "PD"
Paid = -1

When I try this I get all kinds of errors. I think I have to close the connection to the Payments table first, then open the Status table.

Why would you even need to have a paid field in another table? You already have the information in the payment table, just use a query to get which people have paid.

I'm moving the PDStatus field to the Status table. It makes more sense to have it in there.
The payment table should only have the payment information. (I guess I've learned a little about normalization from this group)

The Status table shows if the member is Paid, and what their status is and if they're active -Essential info for all my queries.
Here's the fields for the Status table :

StatusID (PK)
CID (FK)
PDStatus (PD = Paid Member , PDO=Paid Officer, PDT=Paid Trustee, PDH=Honorary etc.. This field determines what the members privilges are in the club. )
Active ( Every year end, anyone not paid has their Active status changed to NO All queries have Active=Yes. This allows me to reactive any member if they rejoin just by changing this one field.)
Paid

Heres the Payments Table:
PaymentID (PK)
CID (FK)
PaymentDate
PaymentType
AmountPaid
MembershipType
PDStatus ( which is moving to the Status table where it belongs )
OfficerType
OfficerPriority
PrintCode
PaymentNotes
KeyCard ( this will also eventually be in it's own KeyCards table which at year end will be purged)
National$
District$

So, those are the tables I need to update. PDStatus , Active, and Paid are essential to update when I process these payments. I was thinking of just making a new button on the form if necessary, but if it can be done on a one shot deal in the code, that would be better.

You can make the updates within the same code as the payment, but you still do not need to mark the status as paid since it would be redundant (and violates normalizaton rules) with what's in the payment table. Your available status should be : Member, Officer, Trustee, Honorary