Need VBA Help for updating a table

[kevinscomp]

A little background about my database, it has been created for a club i belong to and I am working on creating a dialog box to invoice everyone on the dues table by selecting all dues paying members, the current year, and the amount to be paid.


Tables:
Calendar Year
Membership Fees
Member Personal Info
Member Dues History

I created a form that is called Invoice Active Members. In the form i have the Calendar year that is in a drop down, Membership fees in a drop down has the amount for the current year, and a unbound text box with three columns(MemberID, First Name, and Last Name) from the Member Personal info Table sorted by criteria stating that the member pays dues and is active.

I have the form built with a select all button for all of the members and a close button, but i am trying to create another button that will allow me to click on the Invoice (a button created) and invoice the selected members. What i mean by invoice is to log into the Member Dues History table the following criteria: Member Id, Dues Year (which is the calendar year), Dues Amount (chosen from membership fees drop down). In the unbound text box i have the three columns which i can change to one column and the member id would be the same one needed for the Member Dues History.

In the end what i am trying to do is make it so i do not have to go into 165 members individually and manually enter and every persons dues to be paid for each year.

Please help. I

[ItsMe]

What method do you use to display the MemberId and name in the unbound TextBox? Is this form a datasheet view or a continuous form, or....

[kevinscomp]

i am unsure, does this help...

[ItsMe]

Yes, actually it does help. However, it does not answer how the unbound controls know what data to retrieve and display. Is there a lot of code behind the form? Can you post the code here? The code is telling the unbound controls what to do and this is the other part of my question.

[kevinscomp]

Hope the below helps... this is what is behind the display of members info.... If that helps...

Option Compare Database

Private Sub ChkColHeads_Click()

End Sub

Private Sub CmdAll_Click()
'Select all records in list
For X = 0 To Me.LstLinkedMembers.ListCount
Me.LstLinkedMembers.Selected(X) = True
Next
'Enable the necessary button controls
Me.CmdClipBoard.Enabled = True
Me.CmdNone.Enabled = True
Me.CmdSwap.Enabled = True
End Sub


Dim sData As String
Dim X As Integer

'Has the user ticked the use col heads tick box?
If Me.ChkColHeads = True Then
sData = sData & "Heading 1" & vbTab & "Heading 2" & vbTab & "Heading 3" & vbTab & "Heading 4" & vbCrLf
End If

'x would be set to row 1 if using column heading in list
For X = 0 To Me.LstLinkedMembers.ListCount
If Me.LstLinkedMembers.Selected(X) = True Then
sData = sData & Me.LstLinkedMembers.Column(0, X) & vbTab & Me.LstLinkedMembers.Column(1, X) & vbTab & Me.LstLinkedMembers.Column(2, X) & vbTab & Me.LstLinkedMembers.Column(3, X) & vbCrLf
End If
Next
'Copy the data to the clipboard

ClipBoard_SetData (sData)

'Let user know that the procedure has been completed

MsgBox "Data copied to clipboard", vbInformation + vbOKOnly, "Demo"



End Sub

Private Sub CmdClose_Click()
On Error GoTo Err_CmdClose_Click
DoCmd.Close
Exit_CmdClose_Click:
Exit Sub

Err_CmdClose_Click:
MsgBox Err.Description
Resume Exit_CmdClose_Click

End Sub

Private Sub CmdNone_Click()
'Deselect all records in list
For X = 0 To Me.LstLinkedMembers.ListCount
Me.LstLinkedMembers.Selected(X) = False
Next
Me.CmdClipBoard.Enabled = False

End Sub

Private Sub CmdSwap_Click()
'Reverse the current settings
For X = 0 To Me.LstLinkedMembers.ListCount
Me.LstLinkedMembers.Selected(X) = Not Me.LstLinkedMembers.Selected(X)
Next
Me.CmdClipBoard.Enabled = True

End Sub

Private Sub LstLinkedMembers_Click()
'Purposenly enable the copy button if at least one item has been selected in the list (Optional)

Dim bFlag As Boolean

For X = 0 To Me.LstLinkedMembers.ListCount
If Me.LstLinkedMembers.Selected(X) = True Then
bFlag = True
Exit For
End If
Next

Me.CmdClipBoard.Enabled = bFlag
End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


If Me.Dirty Then Me.Dirty = False
DoCmd.Quit

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

[kevinscomp]

I used some coding from another dialog box that i created that copies the data in the box to the clipboard.... and i got stuck....

[ItsMe]

I am begining to understand what functionality you are tyring to get from your form. Do I understand correctly that;

You need to invoice members that owe money.
You want to view, at a glance, all members that are not current.
You want to communicate to the individual what they owe and when.

[kevinscomp]

close...

I need to invoice members that owe money (technically no print out or mailer will be sent to them)
I need to view, at a glance, all members that are active that pay dues.
I need to record what that individual owes and when.

[ItsMe]

I need to invoice members that owe money (technically no print out or mailer will be sent to them)

So, by you simply viewing the information in say, a data sheet view, or report this will be satisfied?

I need to view, at a glance, all members that are active that pay dues.

My first question covers this, Yes?

I need to record what that individual owes and when.

If you are viewing what is currently owed is it not already recorded? Do you need an input form and THEN review the data?


Just trying to fully understand your first post so we don't work harder than we have to. Am I understanding so far?

[kevinscomp]

I have added some screen captures below to hopefully help. I am sorry if i am not explaining it right, I am having some writers block on explaining it correctly.

In response to your questions, Question #1 -- I already have a report below ( i will explain in a short statement after), I am looking to be able to record an amount due in a table without manually inputting it into each record. I print a report out for dues and it shows everyones amount due for the year and how much they have paid. This will help for me to see who still owes the club money.

Question #3, It is currently recorded for this year but i manually inputted the amount due in 165 active members records. Thinking an input form will help to review the data to see who owes the club money.

In a nutshell I am working on creating this database for a club that has been operating without computers doing everything by hand. I am trying to make it at the point that i can hand this database over with locking out the navigation pane so no one messes up the original information.

When you open the program it goes into the Main page (see Pic 1 below), and then to access the member info the user goes to Member Edit/Add info Form (pic 2). When they are in this form and click on the dues tab it shows up with the current information. The current information is in a datasheet sub form. The datasheet subform reflects back to the Member Dues History Table (last picture). When the user is looking to see the current record of who has paid overall and who is delinquent they click on the main pages report Member Dues History by year ( Pic # 3 Members Dues History Report). This is the way i have for the user to see delinquent amounts because most of the 165 members will pay dues, some just need a reminder before being classified delinquent. What i was trying to do is create a dialog box form(invoice members button) that is on the Main Page that will pull up the Members Invoice dialog form (pic 4) that will have the year selected, the amount to charge for dues for that year and the members name to record the amount on the Members Dues History Table (Pic 5) and that way it would show up in the Member Edit/Add info Form (pic 2) and on the report for (pic 3).

I am open to any ideas, i am just trying to record an amount to each member that is due to the club so everyone shows up on the dues report and this way it wont have to be done through the manual entry going forward. If there is an easier way or something you can think of that might be easier please suggest it. I am open to anything.


Main Page


Member Edit/Add Info


Member Dues Report


Members Invoice Dialog Form


Members Dues Table

[ItsMe]

I am open to any ideas, i am just trying to record an amount to each member that is due to the club so everyone shows up on the dues report and this way it wont have to be done through the manual entry going forward. If there is an easier way or something you can think of that might be easier please suggest it. I am open to anything.

Well my idea is not to type data into a table that is part of a calculation. This is why you have reports and forms. In other words, you have the information already inputted. Member A owes X per month and is paid current up to date such and such. you will input data when he makes a payment, cancels membership, or needs to renew membership.

One main reason of having a data base is so you can query the data within and do calculations. It is hard to explain and that is why I asked the previous questions. As much for your benefit as mine.

So what does not make sense to me is the title of the thread and the first post. They contradict each other when you apply some data base fundamentals. Your form is not designed for updating tables. The data you intend to input into the table should not go into a table. It should be available to you via the form or via the report or both.

Do you feel you can depend on a report or form to determine weather a member is current and in good standing? You may only need to make minor adjustments if this is OK with you.

[kevinscomp]

Yes we can put the information into a form, i didnt even think about that as an option. I was thinking that i could only enter that information into a table since that is where the information is ultimately stored. My apologies for the wrong terms.

I do know that the member dues are only charged once a year and can increase from year to year. Not every member will pay dues as well. There is a drop down on the Dues tab in the member edit/add info form that says if they pays dues or not. The Dues info tab has the sub form Member Dues History which is a stand alone form that we can input the information into. Does that make sense?


Side note, i do have a query that is currently showing that Pay Annual Dues.

[ItsMe]

Typically, you would want your data base to do all of the calculations for you programmatically. For instances, in fiscal year 2010 the dues were $210. Instead of updating each members' balance owed as $210 you would just enter the value once in a field that represents the retail cost of dues. Then any promotion or reciprocal acknowledgement would be input into a table once to represent said discount for fiscal 2010 year. Example, ANY member that holds a membership card for our sister chapter will get 50% off their annual dues.

This information and special business rules would be stored in its own table. Then you would have fields in the Main membership table that would indicate if a unique member ID would get any or all of these discounts.

Can you consider deleting private data off of your data base so you can share it? If any of this is making sense then it may be beneficial to implement a working relational database.