I want to record quarterly data without having to type the quarter and CY for each record (well over 300). Google suggested putting the Qtr and CY in the Header, then put them in the detail section with the default value set as =[txtMasterValueQtr] and =[TxtmasterValueCY]. This is not working. The last attempt added the Calendar Year but not the Quarter. After these to fields I record the employees name and Employee Code (which is the key field). I also add 17 other number fields. Some of those fields add together to give total fields for data entry verification. What would you recommend to get the Qtr and CY added to each record? I need this information for Qtrly report sorting.
Put the Quarter and Calendar Year controls in the Form Header, but don't try to use them as Default Values for bound fields. A Default Value is evaluated when the new record is created, and a reference to a header control can be unreliable in that context.
The method I would use is to set the fields in the form's BeforeInsert event. That event fires once, when the user starts entering a new record:
Private Sub Form_BeforeInsert(Cancel As Integer)
Qtr = txtMasterValueQtr
CY = txtMasterValueCY
End Sub
Of course, use your actual field/control names. If your bound fields are named Qtr and CY, and your unbound header controls are txtMasterValueQtr and txtMasterValueCY, the code above will copy the values into every new detail record.
Make sure the Quarter and Calendar Year fields are included in the form's Record Source and that the controls in the Detail section are bound to those fields. You can hide those detail controls afterward if you don't need users changing them individually.
Also, Employee Code probably should not be the primary key if an employee will have quarterly records more than once. You will eventually need more than one record for the same employee. I would normally use an AutoNumber ID field as the primary key, then use a unique index on EmployeeCode, Qtr, and CY if you want to prevent entering the same employee twice for the same quarter.
Kim HallOP
@Reply 3 days ago
Thank you!
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.