Hi.
I have an Access 2000 database which tracks finncial brokers and their commissions.
Up to now everything has worked fine but the client has called and wants me to "fix" a limitation on report generation. I think my question will make better sense if I give a brief synopsis of how a broker's commission information is entered now and how it needs to be modified.
Currently, a broker's commission transactions are entered via form/subform and all calculations are working perfectly. In order for the end user to print monthly reports, etc, each transaction entered is date stamped with the current date and a "month" field captures the numerical equivalent of this datestamps month using the "DatePart" function. Report queries then use the "month" field to select the correct records for printing.
The client has stated that on some months she will be entering , for example, "April" transactions during the first week of May due to a high volume of transaction activity. Unfortunately, those transactions entered during the first week of may would be reflected on May's report and not April's since the date stamp be 05/??/2004.
For example,
On February 4th, the end user needs to enter 10 broker transactions. 6 of these are "February" transactions and he/she enters them as normal. Since the combo box has the current month as default, the end user just tabs past this combo box and the "month" field would be populated with the number 2 (corresponds with the current month of February). These transactions would then be included in the February reports. The next 4 transactions are "January" transactions, so the end user would enter the transaction information as normal and change the "Which month do you want to include this transaction in" combo box to "January". This now populates the "month" field with a 1 (corresponding to January) and would then be included in January's reports.
In order to fix this and not have to change a ton of underlying report queries that use a numerical "month" field corresponding to the months of the year (1-12), I created a combo box on the form that lists the months of the year (with the current month set as a default) to allow the end user to select which month that transaction should be included. I created an event in the "After Update" section of the combo box that uses a SELECT statement to look at which month was selected and place it's corresponding numerical equivalent into the table's "month" field. Thus, the datestamp would remain the current date, but the "month" field would have the numeral corresponding to the month selected. The SELECT statement looks as follows:
SELECT cboxmonth
CASE "January"
Me.month = 1
CASE "February"
Me.month = 2
ETC
ETC
ENDCASE
The problem is that the "month" field is not being populated and I'm getting the following error message:
"The field 'tblbrokermonthlydata.month' cannot contain a NULL value because the required property for this field is set to true. Enter a value in this field."
For some reason the value from the case statement isn't being passed to the field.
Does anyone have any suggestions as to what might be the problem?
(Sorry for the wordy explaination)
K.
I have an Access 2000 database which tracks finncial brokers and their commissions.
Up to now everything has worked fine but the client has called and wants me to "fix" a limitation on report generation. I think my question will make better sense if I give a brief synopsis of how a broker's commission information is entered now and how it needs to be modified.
Currently, a broker's commission transactions are entered via form/subform and all calculations are working perfectly. In order for the end user to print monthly reports, etc, each transaction entered is date stamped with the current date and a "month" field captures the numerical equivalent of this datestamps month using the "DatePart" function. Report queries then use the "month" field to select the correct records for printing.
The client has stated that on some months she will be entering , for example, "April" transactions during the first week of May due to a high volume of transaction activity. Unfortunately, those transactions entered during the first week of may would be reflected on May's report and not April's since the date stamp be 05/??/2004.
For example,
On February 4th, the end user needs to enter 10 broker transactions. 6 of these are "February" transactions and he/she enters them as normal. Since the combo box has the current month as default, the end user just tabs past this combo box and the "month" field would be populated with the number 2 (corresponds with the current month of February). These transactions would then be included in the February reports. The next 4 transactions are "January" transactions, so the end user would enter the transaction information as normal and change the "Which month do you want to include this transaction in" combo box to "January". This now populates the "month" field with a 1 (corresponding to January) and would then be included in January's reports.
In order to fix this and not have to change a ton of underlying report queries that use a numerical "month" field corresponding to the months of the year (1-12), I created a combo box on the form that lists the months of the year (with the current month set as a default) to allow the end user to select which month that transaction should be included. I created an event in the "After Update" section of the combo box that uses a SELECT statement to look at which month was selected and place it's corresponding numerical equivalent into the table's "month" field. Thus, the datestamp would remain the current date, but the "month" field would have the numeral corresponding to the month selected. The SELECT statement looks as follows:
SELECT cboxmonth
CASE "January"
Me.month = 1
CASE "February"
Me.month = 2
ETC
ETC
ENDCASE
The problem is that the "month" field is not being populated and I'm getting the following error message:
"The field 'tblbrokermonthlydata.month' cannot contain a NULL value because the required property for this field is set to true. Enter a value in this field."
For some reason the value from the case statement isn't being passed to the field.
Does anyone have any suggestions as to what might be the problem?
(Sorry for the wordy explaination)
K.
