Search Form Help

[carguy37757]

Hey all - Newbie here. I've tried searching for an answer to my question, but haven't had any luck. I'm really an Access novice and have been trying to teach myself as I go (which you'll likely be able to see once you see my work).

What I'm doing is building a database to track all attendance types for a department. I think my forms and such are working fine (with the help of some code pieces I've found online). However, I have a need for a multi-criteria search to retrieve records for updating. Let me explain.

Attached is a copy of my DB I'm working on. I have several tables and forms. The main form used will be frmAddAttendance. This is where all of the information will be entered and filled back to the table tblAttendance.



What I'd like to be able to do is have this form "search on the fly" for an existing record based on the Employee Name entered and the date chosen. If no record is found for that employee and date, the user should be able to continue to add an entry for that date. However, if a record already exists for employee and date chosen, I'd like that record to be prefilled on the form so the existing record can be updated (not a new record created in the table).

I figure some sort of query is in order, but again, I'm less than beginner with this and really am unsure of how to set my criteria to do what I want.

If anyone can assist with this, or point me in the proper direction, I would greatly appreciate it!

Thanks!
Nick S.

[apr pillai]

I think you can solve this issue with a Combobox and a Calendar Control both attached to Textboxes on the Main Form header Section. Do the following:

• Create a Total Query on the Employee Name field from the Employees table, sort the Employee Name in Ascending Order.
• Design a Form with the Employees Table with all required information and save the form with the name frmEmp_Sub.
• Open a New Form in Design View.
• Select the Combobox Control (while the Wizard button is in selected state) and draw a Combobox on the header of the Form, select the Total Query created in step-1 as source for the combobox.
• Name the Combobox control as cboNames.
• Create a second textbox for entering date and name the textbox as attDate.
• Now, drag the frmEmp_Sub Form and drop it in the Detail Section of the Form as a Sub-Form.
• While the frmEmp_Sub is in selected state display it's Property Sheet.
• On the Link Child Field Property type cboNames;attDate.
• On the Link Master Field property type the above fields' corresponding names separated with a semi-colon.
• Save the Form with the name frmMain.
• Open the frmMain in normal view and enter an Employee's name and a date in the header controls.
• If both these information is already available in the Employees table then it will display the details immediatly on the frnEmp_Sub. If not then you can create a new record in the sub-form.

[carguy37757]

Thanks Apr Pillai -

I modified your field names and table lists to fit my need and your suggestion seems to do what I need it to do, however, it seems to only pull the first record from my attendance table regardless of what name I select in my header. Any thoughts on what I might be doing wrong?

I think you can solve this issue with a Combobox and a Calendar Control both attached to Textboxes on the Main Form header Section. Do the following:

• Create a Total Query on the Employee Name field from the Employees table, sort the Employee Name in Ascending Order.
• Design a Form with the Employees Table with all required information and save the form with the name frmEmp_Sub.
• Open a New Form in Design View.
• Select the Combobox Control (while the Wizard button is in selected state) and draw a Combobox on the header of the Form, select the Total Query created in step-1 as source for the combobox.
• Name the Combobox control as cboNames.
• Create a second textbox for entering date and name the textbox as attDate.
• Now, drag the frmEmp_Sub Form and drop it in the Detail Section of the Form as a Sub-Form.
• While the frmEmp_Sub is in selected state display it's Property Sheet.
• On the Link Child Field Property type cboNames;attDate.
• On the Link Master Field property type the above fields' corresponding names separated with a semi-colon.
• Save the Form with the name frmMain.
• Open the frmMain in normal view and enter an Employee's name and a date in the header controls.
• If both these information is already available in the Employees table then it will display the details immediatly on the frnEmp_Sub. If not then you can create a new record in the sub-form.

[apr pillai]

Corrected Database is attached. tblAttendance Table is using the UserID as foreign key of Employees Table. So the cboName also should use this field value (UserID is kept hidden by setting the column width to 0) to link with the sub-Form field Employee.

It is better if you enter the date value first and select the employee name from the combo box. The selected employee's attendance record of the selected date will appear in the sub-form (if record exists). Otherwise only the employee code and date (the link field values) will appear in the attendance form, you can fill in other information in the balnk fields to complete the attendance record for the selected employee.

[carguy37757]

Thank You so much. I was racking my brain trying to figure out what I was doing wrong. I really appreciate your help!