Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

best coding practice or methodology for tracking payouts byweek number

Status
Not open for further replies.

jlathem

Technical User
Jul 25, 2010
27
I am very new to Access Forms and VBA and looking for best coding practice or methodology for what I need?

I have a new project where I need to track payout requests by Payee ID and week number being requested. Each Payee is entitled to only one payment per week. But can request back payments as far back as week of Oct 23, 2010.

Once I figure out how to get the week numbers for current year how do I track payouts for future year’s week numbers? Doesn’t the week numbers just start over?

Thanks in advance for any assistance someone can give me.

Jms
 
You can calculate the week of the year using
DatePart("ww",[YourDateField])
There are some optional arguments to identify the day the week begins on and the first week of the year.

Setting a unique index on employee, year, and week should prevent duplication of requests.

Duane
Hook'D on Access
MS Access MVP
 
Duane,

If I set a unique index on Payee, Year, and Week_Number wouldn't that keep me from entering a duplicate record with the Payee_ID or Year, or Week_Number?
 
Thanks for helping me out with this.

I am very new to VBA and designing forms so I may not have explained myself very well. I think this issue may be a methodology problem.

I am storing all the payment requests from all the payees in one table.

There are 100s of payees and each of them needs to be able to request payments for each week of the year after Oct. 23, 2010 until the payment program ends. So if I make each of the fields mentioned above unique each payee couldn’t request payments for two different weeks in the table.


 
Maybe you should share your table structure. If the week is part of the unique index then why couldn't "each payee couldn't request payments for two different weeks in the table"


Duane
Hook'D on Access
MS Access MVP
 
Like I said I am new to this kind of developing.

I will include the table structure below.

I thought that if you make any table field unique index that would mean that there could be no duplicate values in that specific field. If I understand what you are saying is that unique indexing will span across multiple fields.

Example: If I make Payee_ID, Week_Number, and Year all unique index, then after entering Payee_ID 999999999 payment request of $100.00 for week 42 of year 2010, then , the table will still accept a record for Payee_ID 999999999 payment request of $150.00 for week 43 of year 2010?

Please bear with me as I ask what must seem like simple questions for a seasoned coder like you guys.

I tried to upload the DB but our network security will not allow me to do it. So here is the long version.

Field Name / Data Type / Field Size
IPP_ID / AutoNumber / Long Integer (Primary Key, indexed no duplicates)
Payee_ID / Text / 11 (Indexed - Duplicates OK)
Week_Number / Number / Long Integer (Indexed - Duplicates OK)
Year / Number / Long Integer (Indexed - Duplicates OK)

If you need more information just let me know.


Jms

 
Ah, I never knew that was possible! I am reading about that process now.

Thank you so much for taking the time to help me.

Jms
 
Duane,

I got the unique index for multiple records working but when an attempt to enter a dupe record I get a MsgBox that doesn’t really tell the user what they did wrong.

Is there a way to create a specific message when the index catches a dupe record?

Jms
 
Thanks, I will see what I can figure out.

Jms
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top