gymnast payment tracker ideas

[SteveMc]

Hi everyone,
I am trying to get to grips with vba with the help of live lessons so I don't have much experience and would be grateful of any pointers you can give me.
I have a worksheet on which the columns are laid out as;
gymnast name, Due date, Next due, overdue date, Amount Due, Amount paid, Outstanding balance

how I see it working is

Due date = the day payment due
Next due date =1 month on from due date
overdue date= 14 days passed due date
Amount Due= monthly payment
Amount paid = this can vary as some parents pay as you go
Outstanding Balance= showing whether in debt or in credit

the part that I cannot seem to understand is that I would the date to auto update on to the next month when the due date has passed. Also to increase the outstanding balance by amount due once due date has passed.

I apologise if I have missed any important details ,but greatly appreciate any time that you can give to explain.

 

[ranman256]

I think the layout should be:
gymnast name, Due date, OverDue Date, Amount Due, Amount paid, Outstanding balance
NextDue date is a given, Its the next month.

I have a Config sheet,it sets the setup values: due date is the 1st, Overdue is 14,
another sheet holds the renters: person, Amt Due, Late Fee

Each month the 'rent' is due, and the macro adds a record for everyone
given the due month:
DueDate = iMonth & "/1/" & year
OverDue = DueDate + 14
AmtDue = That person's rent Amt

 

[SteveMc]

Hi Ranman,
Thanks for your reply I understand what it is your telling me and I can see how it works. However I cannot understand how to write this in code. I think my main problem is that I don't understand when to use Dim or to use variables could you please explain in layman terms to how I should go about starting the setup values and code.

Many Thanks for you help

 

[ranman256]

my answer above can be done in a query, no code needed.
I usu only need code in forms. the DIM tells vb what kind of data will be stored in the variable.
I use variant 98% of the time. A variant can be anything, string, a number , a date.
If you Dim something as Integer, then try to assign a string , you will get an error.
Not so with variants. (v for variant)

I will set minor variables to integer or date if Im going to preform math.

sub Mycode()
dim i as integer
dim vName 

for i = 1 to 5
  vName = "bob"
   msgbox vName & i
next
end sub

 

[SteveMc]

Hi Ranman,
Thanks for your help, its been a real eye opener working on this project(there was me thinking that my knowledge of excel was ok. It seems not)
I have been online teaching myself how to run queries, however I cannot seem to work out how you set the values within the query for due dates, overdue dates and payments. Are the values set elsewhere?
I have created a user form for payments information . I have 9 different types of "memberships" for each one I have a different payment amount. I want to set the amount to the memberships as the membership will not change but the amounts could at some point in time.
Again thanks for you patience.