best coding practice or methodology for tracking payouts byweek number

[jlathem]

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

 

[dhookom]

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

 

[jlathem]

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?

 

[dhookom]

I think that's exactly what I meant by

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

Duane
Hook'D on Access
MS Access MVP

 

[jlathem]

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.

 

[dhookom]

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

 

[jlathem]

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

 

[dhookom]

While in the table design view, you can view the indexes and create an index that combines the three fields that you want to keep unique.

Duane
Hook'D on Access
MS Access MVP

 

[jlathem]

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

 

[jlathem]

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

 

[dhookom]

I think you can capture this error in the form errors.

Duane
Hook'D on Access
MS Access MVP

 

[jlathem]

Thanks, I will see what I can figure out.

Jms